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

On Thu, 6 Jan 2011 05:46:01 -0800 (PST), Dave wrote:

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