OK Money, I used your concept and it works! This is my code, and it works,
but now I need to close the empty spaces. For example, I have approximately
10 possible ranges to copy and paste into an area on my spreadsheet that can
only accommodate 5 cells. Not all of the 10 will contain text -- say 3 out
of 10 will actually hold text, or 6 out of 10, etc., but for those that do
hold text, I want to see the values in the 5 cells or less. Is there a way
to accomplish this?
Sheets("Morning Report Database").Select
Range("Y2").Select
If ActiveCell.Value <> "" Then
Selection.Copy
Sheets("CHEATSHEET").Select
Range("B6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
ElseIf ActiveCell = "" Then
ActiveCell.Offset(RowOffset).Activate
End If
Sheets("Morning Report Database").Select
Range("Y3").Select
If ActiveCell.Value <> "" Then
Selection.Copy
Sheets("CHEATSHEET").Select
Range("B7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
ElseIf ActiveCell = "" Then
ActiveCell.Offset(RowOffset).Activate
End If
Money said:
Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
''check value on sheet
Sheets("E40").Select
Range("e37").Select
start:
If ActiveCell.Value <> "" Then
ActiveCell.Copy
''paste value where u want it
Sheets("CHEAT SHEET").Select
Range("B6").Select
ActiveCell.PasteSpecial
elseif activecell="" Then
activecell.offset(rowoffset:=1).activate
goto start
end if
End Sub
-----Original Message-----
Well, I'm really new to VB, but I think I understand that I must DIM my
worksheet as something? I named the macro Ctrl+s --- this is how it is
supposed to run? Can it run automatically?
I want the copy to appear in a worksheet called "CHEAT SHEET" in cell B6. I
copied this code into my code window -- is it correct -- verbatim?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
''check value on sheet
Sheets("E40").Select
Range("e37").Select
If ActiveCell.Value <> "" Then
ActiveCell.Copy
''paste value where u want it
Sheets("CHEAT SHEET").Select
Range("B6").Select
ActiveCell.PasteSpecial
elseif activecell="E38"
Money said:
''check value on sheet
sheets("E40").select
range("e37").select
if activecell.value<>"" then
activecell.copy
''paste value where u want it
sheets("?????").select
range("??").select
activecell.pastespecial
elseif activecell=""
''you can put this in a loop if req.
activecell.offset(rowoffset:=1).activate
....
end if
Hope this helps.
-----Original Message-----
How do I write a VB macro or procedure that will get the
contents of cell E37
from workbook named E40 if E37 is not blank?
This is the formula that I have in the target cell:
=IF('C:\Documents and Settings\[E40.xls]E40'!
$E$37>0,+'C:\Documents and
Settings\[E40.xls]E40'!$E$37,0)
That formula works, but I think I need a case select type
of code to move
onto cell $E$38, if cell E37 is blank, then to
$E$39 if E38 is blank. If E39 is not blank, then plus
that cell in the
target cell.
Sound complicated? It is -- I can't seem to understand
how to make this
happen. Can someone help me?
.
.
