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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,286
Office Version
  1. 365
  2. 2007
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
21,286
Office Version
  1. 365
  2. 2007
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
21,286
Office Version
  1. 365
  2. 2007
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
21,286
Office Version
  1. 365
  2. 2007
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..
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,604
Messages
5,838,332
Members
430,538
Latest member
PedroOliveira

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top