Paste special formatting issues

[Macro_]

Hi all,
I have a macro which copies data from one sheet (including tables, figures etc) and paste specials - values and formatting only - to another worksheet. The only issue is that cells which are highlighted in the first workbook are not getting copied to the exactly relevant cells in the new workbook but to one cell below.. I've tried two different codes with the same result. The codes I have used are listed below. Any ideas? Thanks any advance for any help..
ActiveCell.Offset(1, 0).Range("A1:S84").Copy
ActiveCell.Offset(1, 0).Range("A1:S84").PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).Range("A1:S84").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False

AND

ActiveCell.Offset(1, 0).Range("A1:S84").Copy
ActiveCell.Offset(1, 0).Range("A1:S84").PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).Range("A1:S84").PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False

 

[Michael M]

Why not just use

Range("A1:S84").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
Range("A1").PasteSpecial Paste:=xlPasteFormats

Keep in mind though, that the code you posted simply copies over itself....it doesn't paste to another sheet !!!

 

[Macro_]

Thanks! Will try it out now.. Oh I see what would I need to add for it to paste to another sheet?

 

[Macro_]

And I'm still facing the same issue.. highlighted cells one cell below what they should be.. is maybe formatting on the original sheet are source for the problem?

 

[Michael M]

The other sheetname,........ for example

Range("A1:S84").Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteFormats

 

[Macro_]

Sorry I am new to macro but the bolded line is where my first error is showing up... ActiveCell.Offset(1, 0).Range("A1:S84").Copy
ActiveCell.Offset(1, 0)Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0)Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False

 

[Michael M]

Range("A1:S84").Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteFormats

 

[Macro_]

Both the sentence beginning with sheets are getting highlighted.... :/

Range("A1:S84").Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteFormats

 

[Michael M]

Do you have a sheet called "Sheet2" ??
What is the name of the sheet you are trying to copy to ?
I'd suggest recording the macro and then looking at the recorded macro structure

 

[Macro_]

I want to copy it to a new workbook.. I named one sheet2 to see if it would work and it doesn't..