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,D 33").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

--
Message posted from http://www.ExcelForum.com

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,
--
George Nicholson

Remove 'Junk' from return address.


"nrage21 " wrote in message
...
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,D 33").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 from http://www.ExcelForum.com/

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,D2 9,D31,D33").Value _
= Sheet2.Range("A1").Value

would do the first sheet.

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

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

--
Jim Rech
Excel MVP

"nrage21 " wrote in message
...
| 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,D 33").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 from http://www.ExcelForum.com/
|

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

--
Message posted from http://www.ExcelForum.com

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

So the test would be:

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

now how do you include the other sheets in the code?

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
"nrage21 " wrote in message ...
|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 from http://www.ExcelForum.com/
|

Thanks so much Jim! :)

Larry
VBA Amateu

--
Message posted from http://www.ExcelForum.com