What's wrong w/my code?

[nrage21]

Sub Copy_Paste()
If Range("D7").Value < "" Then
Sheets("Sheet1").Range("A1").Select
Selection.Copy
Sheets("Sheet2").Select
Range("D7,D9,D15,D17,D19,D21,D23,D25,D27,D29,D31,D33").Select
Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "mm/dd/yyyy"
Else
End If
End Sub

I need to run this program from sheet 2 to sheet 10 when as soon a
workbook opens. Ranges should all be the same. Anyone knows a bette
way of doing this??? I keep getting a "1004" error.


Larry
VBA Amateu

 

[George Nicholson]

Just a guess:

If Range("D7").Value < "" Then

What sheet?? A 1004 error would be raised if Excel couldn't figure it out
either.

HTH,

 

[Jim Rech]

You should always mention the exact error meessage. 1004 tells us nothing.

It may be that the clipboard is getting cleared out. You don't have to even
used copy/paste and you can skip all the jumping around selecting sheets and
cells. You do not have to select anything. For example:

Sheet2.Range("D7,D9,D15,D17,D19,D21,D23,D25,D27,D29,D31,D33").Value _
= Sheet2.Range("A1").Value

would do the first sheet.

This is perplexing. What does less that an empty cell mean?

--
Jim Rech
Excel MVP

| Sub Copy_Paste()
| If Range("D7").Value < "" Then
| Sheets("Sheet1").Range("A1").Select
| Selection.Copy
| Sheets("Sheet2").Select
| Range("D7,D9,D15,D17,D19,D21,D23,D25,D27,D29,D31,D33").Select
| Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone,
| SkipBlanks _
| :=False, Transpose:=False
| Application.CutCopyMode = False
| Selection.NumberFormat = "mm/dd/yyyy"
| Else
| End If
| End Sub
|
| I need to run this program from sheet 2 to sheet 10 when as soon as
| workbook opens. Ranges should all be the same. Anyone knows a better
| way of doing this??? I keep getting a "1004" error.
|
|
| Larry
| VBA Amateur
|
|
| ---
| Message posted
|

 

[nrage21]

I forgot what the rest of the 1004 error said... something abou
"Application Function..." cant remember.

I know this VBA Code is shameful... but just learning.

">>If Range("D7").Value < "" Then"

I just wanted the program to start running if cell D7 was empty but i
not empty then to end the program and do nothing.

Thanks for your help... now how do you include the other sheets in th
code?

Larry
VBA Amateu

 

[Jim Rech]

I just wanted the program to start running if cell D7 was empty

So the test would be:

If Range("D7").Value = "" Then

Worksheets("Sheet2").Range("D7,D9,D15,D17,D19,D21,D23,D25,D27,D29,D31,D33").Value _
= Worksheets("Sheet1").Range("A1").Value

Worksheets("Sheet3").Range("D7,D9,D15,D17,D19,D21,D23,D25,D27,D29,D31,D33").Value _
= Worksheets("Sheet1").Range("A1").Value


--
Jim Rech
Excel MVP
|I forgot what the rest of the 1004 error said... something about
| "Application Function..." cant remember.
|
| I know this VBA Code is shameful... but just learning.
|
| ">>If Range("D7").Value < "" Then"
|
| I just wanted the program to start running if cell D7 was empty but if
| not empty then to end the program and do nothing.
|
| Thanks for your help... now how do you include the other sheets in the
| code?
|
| Larry
| VBA Amateur
|
|
| ---
| Message posted
|

 

[nrage21]

Thanks so much Jim!

Larry
VBA Amateu