"Clif McIrvin" wrote in message
...
"Dave" wrote in message
...
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.

<snip

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.

---------

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

(clare reads his mail with moe, nomail feeds the bit bucket :-)




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.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)