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.

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.

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.

Thanks

Dave

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

NOTE

Macro assumes that workbook Blank_ACD is currently open.


Gord

On Wed, 05 Jan 2011 12:02:06 -0800, Gord Dibben wrote:

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

On Jan 5, 2:36*pm, Gord Dibben wrote:
NOTE

Macro assumes that workbook Blank_ACD is currently open.

Gord



On Wed, 05 Jan 2011 12:02:06 -0800, Gord Dibben wrote:
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- Hide quoted text -

- Show quoted text -

Gord:

Thanks for the help. The macro works great. In the C:\ACD\Comp_Acd.xls
file I have tabs that are named Monthly_Totals. I want to exclude them
from this appending process . Can this be done?

Thanks again!!

They cannot all be named Monthly_Totals.

How many of these "excluded" sheets would there be?

Where are they located in the workbook?

There are several methods to exclude those sheets by code.

Below is one method which hides those sheets, appends to visible sheets then
unhides the sheets.

One of the brighter coders will come up with something better..........like not
hiding, I'm sure.

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"
Set myarray = Sheets(Array("Month1", "Month2", "Month3", "Month4"))
myarray.Visible = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = True Then
ws.Activate
Set rng2 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
rng1.Copy Destination:=rng2
End If
Next ws
For Each ws In myarray
ws.Visible = True
Next ws
With ActiveWorkbook
.Save
.Close
End With

End Sub


Gord


On Wed, 5 Jan 2011 12:47:57 -0800 (PST), Dave wrote:

Thanks for the help. The macro works great. In the C:\ACD\Comp_Acd.xls
file I have tabs that are named Monthly_Totals. I want to exclude them
from this appending process . Can this be done?

Thanks again!!

On Jan 5, 3:59*pm, Gord Dibben wrote:
They cannot all be named Monthly_Totals.

How many of these "excluded" sheets would there be?

Where are they located in the workbook?

There are several methods to exclude those sheets by code.

Below is one method which hides those sheets, appends to visible sheets then
unhides the sheets.

One of the brighter coders will come up with something better..........like not
hiding, I'm sure.

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"
* * Set myarray = Sheets(Array("Month1", "Month2", "Month3", "Month4"))
* * myarray.Visible = False
* * For Each ws In ActiveWorkbook.Worksheets
* * * * If ws.Visible = True Then
* * * * * * ws.Activate
* * * * * * Set rng2 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
* * * * * * rng1.Copy Destination:=rng2
* * * * End If
* * Next ws
* * For Each ws In myarray
* * * * ws.Visible = True
* * Next ws
With ActiveWorkbook
* * .Save
* * .Close
End With

End Sub

Gord



On Wed, 5 Jan 2011 12:47:57 -0800 (PST), Dave wrote:
Thanks for the help. The macro works great. In the C:\ACD\Comp_Acd.xls
file I have tabs that are named Monthly_Totals. I want to exclude them
from this appending process . Can this be done?

Thanks again!!- Hide quoted text -

- Show quoted text -

Thanks again,

There are 12 monthly sheets they come after about 4 weeks of data e.g
07-Jan-2011 ,
14-Jan-2011, 21-Jan-2011, 28-Jan-2011, Monthly_Totals

I can Name them Jan, Feb, Mar ....etc.