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$370,+'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?

''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$370,+'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?



.

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" wrote:

''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$370,+'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?



.

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" wrote:

''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$370,+'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?



.


.

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" wrote:

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" wrote:

''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$370,+'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?



.


.