See in-line comments and macro at end.

On Wed, 5 Jan 2011 09:46:32 -0800 (PST), Dave wrote:

Hi All,

Thanks in advance for your help. I need a macro please:

I am using Excel 2003.

I have a spreadsheet called Blank_ACD. It only has one tab in it and
this tab is called Weekly ACD Report.
File is located in C:\ACD.

I have another spreadsheet that is called Comp_Acd. This spreadsheet
has 52 tabs for each week of the year named after the ending day of
the week (Friday being that day) so the names are like 07-Jan-2011 ,
14-Jan-2011, 21-Jan-2011, 28-Jan-2011 & so on until 30-Dec-2011. File
is also located in C:\ACD.

What I want is a macro that would copy one tab from one spreadsheet to
multiple tabs in another spreadsheet while retaining the targets
spreadsheets tab names

You cannot copy a sheet tab into another sheet tab

You want to copy the contents of Weekly ACD Report and append those contents to
the contents of 52 sheets in Comp_Acd workbook, right?


In my case copy the Weekly ACD Report tab from c:\acd\ Blank_ACD to c:
\acd\Comp_Acd into all the weekly tabs in Comp_Acd while retaining
the existing 52 week tab names in Comp_Acd.

Sheet names won't change if you are appending data as above.


Additionally this macro must also accommodate the name changes for the
next year when the 52 tabs in Comp_Acd will reflect the ending
Fridays for 2012. And so on to the years after that.

No need for that, thefollowing macro will work no matter what the sheet names
are.

Thanks

Dave

Sub append_data()

Dim ws As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Sheets("Weekly ACD Report").UsedRange
Workbooks.Open Filename:= _
" C:\ACD\Comp_Acd.xls"
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Set rng2 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
rng1.Copy Destination:=rng2
Next ws
With ActiveWorkbook
.Save
.Close
End With

End Sub


Gord Dibben MS Excel MVP