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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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 !!!
 
Upvote 0
Thanks! Will try it out now.. Oh I see what would I need to add for it to paste to another sheet?
 
Upvote 0
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?
 
Upvote 0
The other sheetname,........ for example
Code:
Range("A1:S84").Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteFormats
 
Upvote 0
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
 
Upvote 0
Code:
Range("A1:S84").Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteFormats
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
I want to copy it to a new workbook.. I named one sheet2 to see if it would work and it doesn't..
 
Upvote 0

Forum statistics

Threads
1,216,577
Messages
6,131,513
Members
449,654
Latest member
andz

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
Back
Top