Copy from worksheet to worksheet with Macro

[Robert L. Altic Jr.]

Hi All,
I don't program macros in Excell often, but would like to automate a
monthly spreadsheet. I would like to copy the headers from the
January worksheet to the current month and format. I can get it to do
the formating, but what syntax is necessary to copy A!-E1 in the
January 22,2003 sheet to one for September, October etc (assuming that
I am in the current month's sheet). Many thanks in advance.. Best,

 

[Don Guillett]

Easiest way is to select the sheets you want by ctrl+sheet key>type in the
headers>select only one sheet>done

 

[Tom Ogilvy]

worksheets("January 22,2003").Range("A1:E1").Copy _
Destination:=ActiveSheet.Range("A1:E1")

Check the name of the January sheet - should there be a space after the
comma?

 

[Robert L. Altic Jr.]

worksheets("January 22,2003").Range("A1:E1").Copy _
Destination:=ActiveSheet.Range("A1:E1")

Check the name of the January sheet - should there be a space after the
comma?

Hi Tom,
Did your suggestion changing the date on the first tab and get a
subscript out of range message. Suggestions?

copy of macro:

Sub test()
'
' test Macro
' Macro recorded 6/14/2003 by Robert L. Altic Jr.
'

'
Worksheets("January 22, 2003").Range("A1:E1").Copy _
Destination:=ActiveSheet.Range("A1:E1")
End Sub

 

[Tom Ogilvy]

You get that error message if "January 22,2003". does not match the name
on the worksheet tab.

ONLY you can check that.

In otherwords, Mike doesn't answer to the name Bob. As an example.

 

[Tom Ogilvy]

There is no reason to have the range after the copy be a single cell - it
must be a single cell or an exact match in terms of size - it is an exact
match in the sample I provided.

 

[Otto Moehrbach]

Thanks Tom. I thought it would paste the entire copy range starting with
each cell of the destination range. I learned something else from you
today. Otto