vba copy paste values and formats

[jtodd]

Hi
What i need to do is copy and past data from one sheet to another .
The issue is that i want to copy the values and formats but not the formulas. I have the folowing code

Sub CopyWeekly()
Sheets("WeeklyDetail").Unprotect Password:="xxxx"
Range("a1:g40").Copy

Sheets("WeeklyDetail").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Sheets("WeeklyDetail").Protect Password:="xxxxx"

End Sub
This works fine but how do I copy formats as well . I have tried
Sub CopyWeekly()
Sheets("WeeklyDetail").Unprotect Password:="xxxxxx"
Range("a1:g40").Copy

Sheets("WeeklyDetail").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormats
Sheets("WeeklyDetail").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Sheets("WeeklyDetail").Protect Password:="xxxxx"


End Sub

But just get error message

Can anybody help

 

[jmacleary]

Have you tried recording a macro and seeing what comes out?

 

[My Aswer Is This]

edit

 

[My Aswer Is This]

I think it's because part of your Paste range is inside the copy range.

 

[Fluff]

Try

Sub CopyWeekly()
   Sheets("WeeklyDetail").Unprotect Password:="xxxxxx"
   Sheets("[COLOR=#ff0000]New[/COLOR]").Range("a1:g40").Copy
   With Sheets("WeeklyDetail").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
      .PasteSpecial xlPasteValues
      .PasteSpecial xlPasteFormats
   End With
   
   Sheets("WeeklyDetail").Protect Password:="xxxxx"

End Sub

Where the value in red is the sheet name you are copying from.
If this doesn't work, what error do you get & what line is highlighted?