Copy tab from one spreadsheet to multiple tabs in another spreadsheetwhile retaining the targets spr

[Dave]

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

 

[Gord Dibben]

See in-line comments and macro at end.

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

 

[Gord Dibben]

NOTE

Macro assumes that workbook Blank_ACD is currently open.


Gord

 

[Dave]

NOTE

Macro assumes that workbook Blank_ACD is currently open.

Gord














- 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!!

 

[Gord Dibben]

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

 

[Dave]

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






- 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.

 

[Clif McIrvin]

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.

Gord






- 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.

---------

Is this a workbook you are creating for the first time, or are (we)
creating it as we go along? As Gord pointed out, you cannot have
duplicate sheet names ... but Excel is real nice about creating unique
names for you when you copy a sheet -- so you could have a series of
Monthly_Totals sheets scattered through the workbook named
Monthly_Totals, Monthly_Totals (2), Monthly_Totals (3) and so forth. If
you are naming them yourself, I'd suggest a series like this:
Monthly_Totals_Jan, Monthly_Totals_Feb, etc.

Now, instead of hiding sheets using a hard-coded "magic number" array
(what happens when a sheet is renamed for some reason?) find this
portion of Gord's original code:

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

and change it to:

For Each ws In ActiveWorkbook.Worksheets
If Left(ws.Name,14) <> "Monthly_Totals" Then
ws.Activate
Set rng2 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1,
0)
rng1.Copy Destination:=rng2
End If
Next ws

I still used a "magic number" ... but only one instead of an array.

 

[Clif McIrvin]

Watch out for line wrap!

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.

---------

Is this a workbook you are creating for the first time, or are (we)
creating it as we go along? As Gord pointed out, you cannot have
duplicate sheet names ... but Excel is real nice about creating unique
names for you when you copy a sheet -- so you could have a series of
Monthly_Totals sheets scattered through the workbook named
Monthly_Totals, Monthly_Totals (2), Monthly_Totals (3) and so forth.
If you are naming them yourself, I'd suggest a series like this:
Monthly_Totals_Jan, Monthly_Totals_Feb, etc.

Now, instead of hiding sheets using a hard-coded "magic number" array
(what happens when a sheet is renamed for some reason?) find this
portion of Gord's original code:

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

and change it to:

For Each ws In ActiveWorkbook.Worksheets
If Left(ws.Name,14) <> "Monthly_Totals" Then
ws.Activate
Set rng2 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1,
0)
rng1.Copy Destination:=rng2
End If
Next ws

I still used a "magic number" ... but only one instead of an array.

 

[Clif McIrvin]

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.

---------

Is this a workbook you are creating for the first time, or are (we)
creating it as we go along? As Gord pointed out, you cannot have
duplicate sheet names ... but Excel is real nice about creating unique
names for you when you copy a sheet -- so you could have a series of
Monthly_Totals sheets scattered through the workbook named
Monthly_Totals, Monthly_Totals (2), Monthly_Totals (3) and so forth.
If you are naming them yourself, I'd suggest a series like this:
Monthly_Totals_Jan, Monthly_Totals_Feb, etc.

Now, instead of hiding sheets using a hard-coded "magic number" array
(what happens when a sheet is renamed for some reason?) find this
portion of Gord's original code:

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

and change it to:

For Each ws In ActiveWorkbook.Worksheets
If Left(ws.Name,14) <> "Monthly_Totals" Then
ws.Activate
Set rng2 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1,
0)
rng1.Copy Destination:=rng2
End If
Next ws

I still used a "magic number" ... but only one instead of an array.

Another remark: the ws.Activate is not necessary for the macro to run.
Removing it will speed things up a bit, and will not change the visible
worksheet while the macro is running. If you like seeing the screens
flash past while the macro is doing its thing you can leave it there.

 

[Gord Dibben]

Thanks Cliff

I like this better than hiding/unhiding a large array of hard-named sheets.

As long as Dave keeps to a naming convention for the sheets to be excluded from
appending.

The destination workbook and sheets already exists so not created at run time.


Gord

 

[Clif McIrvin]

Thanks Cliff

I like this better than hiding/unhiding a large array of hard-named
sheets.

As long as Dave keeps to a naming convention for the sheets to be
excluded from
appending.

The destination workbook and sheets already exists so not created at
run time.

You're welcome.

Another test I have used is looking for a particular value on a sheet
.... something along the lines of a column title that never changes, but
is different on the monthly total sheets than on the weekly detail
sheets. Change

If Left(ws.Name,14) <> "Monthly_Totals" Then

to something along the lines of

If ws.Range("A1") <> "Monthly_Totals" Then


Then, as long as the sheet format never changes it doesn't matter what
the sheet name is.

Clif

 

[Dave]

You're welcome.

Another test I have used is looking for a particular value on a sheet
... something along the lines of a column title that never changes, but
is different on the monthly total sheets than on the weekly detail
sheets. Change

If Left(ws.Name,14) <> "Monthly_Totals" Then

to something along the lines of

If ws.Range("A1") <> "Monthly_Totals" Then

Then, as long as the sheet format never changes it doesn't matter what
the sheet name is.

Clif









--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket - Hide quoted text -

- Show quoted text -

Thanks Gord & Cliff,
I will definitely name the monthly sheets with unique names. You can
tell that I am setting up a new excel spreadsheet. I had the weeks in
but realized that I needed the months to add up the weekly totals. I
was thinking of adding a prefix to all my sheets that I wan to exclude
e.g. EXC_ then the code could be

If Left(ws.Name,4) <> "EXC_" Then

This would work wont it?

I have a couple of questions I wanted to ask you guys about the first
macro, when I ran the 1st macro it did copy the source spreadsheet to
the target but some of the source formatting was lost like column size
and shading etc. Why is that? Second question is about macros, if I
have any macros in the source would they also copy over? Reason I ask
is that it will be easier to create macros on the source once and than
copy them over than to create macros in so many target tabs.

Again I appreciate your help immensely

Dave

 

[Clif McIrvin]

(--> Comments Inline ...)

<...>

Thanks Gord & Cliff,
I will definitely name the monthly sheets with unique names. You can
tell that I am setting up a new excel spreadsheet. I had the weeks in
but realized that I needed the months to add up the weekly totals. I
was thinking of adding a prefix to all my sheets that I wan to exclude
e.g. EXC_ then the code could be

If Left(ws.Name,4) <> "EXC_" Then

This would work wont it?

--> Yes

I have a couple of questions I wanted to ask you guys about the first
macro, when I ran the 1st macro it did copy the source spreadsheet to
the target but some of the source formatting was lost like column size
and shading etc. Why is that?

--> Column width belongs to the column, not the cells. Not sure about
shading ... I'd expect it to copy across with everything else about the
cells. If the shading is a result of conditional formatting, I think
you're getting into another whole level of complexity (not something
I've ever tried to do.)

For the widths, there are at least two options. Look at the VBE help for
the Range.AutoFit Method and the ColumnWidths Property and see if that
makes sense. The macro could be extended to resize individual column
widths if the source columns are wider than the destination.

The other choice would be to simply do a resize after the copy.

---

Second question is about macros, if I
have any macros in the source would they also copy over? Reason I ask
is that it will be easier to create macros on the source once and than
copy them over than to create macros in so many target tabs.

--> No. Why do you want macros on every sheet? Perhaps workbook macros
would be a better choice; or put the macros in an add-in; or put them in
your personal macro workbook. We need a better understanding of your
needs to suggest which would be better.

Again I appreciate your help immensely

Dave

--> You're welcome. Most of us have learned a lot from the community,
and are happy to pass the knowledge along.

 

[Gord Dibben]

Formatting is never copied over with the copy destination method.

See if this revised macro does what you want. Adds formatting and columnwidth
to the copy.

I don't know what you're saying about a macro in or on every sheet.

You can't have macros "on" sheets, only behind sheets.

For this operation, you need only the one macro in a module of the source
workbook Blank_ACD.xls

Run the macro from that workbook..

See Ron de Bruin's site for where to put macros and other code.

http://www.rondebruin.nl/code.htm

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
If Left(ws.Name, 4) <> "EXC_" Then
Set rng2 = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Set rng2 = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
rng1.Copy
With rng2
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=8 ' 8 is columnwidths
End With
End If
Next ws
With ActiveWorkbook
.Save
.Close
End With

End Sub


Gord

 

[Clif McIrvin]

Formatting is never copied over with the copy destination method.

See if this revised macro does what you want. Adds formatting and
columnwidth
to the copy.

Learned something new today <grin>. Thinking about it, I've always used
..Paste or .PasteSpecial, not copy destination. Also didn't realize
..PasteSpecial could paste column widths.

 

[Gord Dibben]

We have all learned from this particular exercise.

Newsgroups are great!!!


Gord