Paste special formatting issues

Macro_

New Member
Joined
Sep 3, 2014
Messages
35
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,279
Office Version
  1. 2013
Platform
  1. Windows
Why not just use
Code:
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_

New Member
Joined
Sep 3, 2014
Messages
35
Thanks! Will try it out now.. Oh I see what would I need to add for it to paste to another sheet?
 

Macro_

New Member
Joined
Sep 3, 2014
Messages
35
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,279
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

The other sheetname,........ for example
Code:
Range("A1:S84").Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteFormats
 

Macro_

New Member
Joined
Sep 3, 2014
Messages
35
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,279
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Macro_

New Member
Joined
Sep 3, 2014
Messages
35
Both the sentence beginning with sheets are getting highlighted.... :/
Code:
Range("A1:S84").Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteFormats
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,279
Office Version
  1. 2013
Platform
  1. Windows
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_

New Member
Joined
Sep 3, 2014
Messages
35
I want to copy it to a new workbook.. I named one sheet2 to see if it would work and it doesn't..
 

Watch MrExcel Video

Forum statistics

Threads
1,109,493
Messages
5,529,187
Members
409,856
Latest member
MAO
Top