Copy Named Range from one sheet to another with formatting

[Sam via OfficeKB.com]

Hi All,

I would like to copy a named range from one worksheet to another within the
same workbook; the range contains constants, formulas and specific formatting
(number formats and column widths).

I'm trying to copy range("Sales_Table") from worksheet Sales Freq to range
("A4") of worksheet Sales.

I 've tried using the code below but get Microsoft VB error message, Run-time
error 1004:
PasteSpecial method of Range class failed.

Sheets("Sales").Activate
Range("Sales_Table").Clear

Sheets("Sales Freq").Activate
Range("Sales_Table").Select
Selection.Resize(Selection.Rows.count, _
Selection.Columns.count + 3).Select
Selection.Copy

Sheets("Sales").Activate
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=
_
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Help very much appreciated.

Thanks
Sam

 

[Dave Peterson]

It could be lots of things.

What line causes the error--you have lots of .pastespecial lines.

Do you have any worksheet/workbook events that are running when you change
sheets or change selection? Maybe running that macro is killing the clipboard.

Maybe you could drop the .select's:

Option Explicit
Sub testme()

Dim RngToCopy As Range
Dim DestCell As Range

Worksheets("Sales").Range("Sales_Table").Clear

With Worksheets("Sales Freq").Range("Sales_Table")
Set RngToCopy = .Resize(.Rows.Count, .Columns.Count + 3)
End With

Set DestCell = Worksheets("Sales").Range("A4")

RngToCopy.Copy

With DestCell
.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

Application.CutCopyMode = False

End Sub


If that doesn't work, are you running the macro using the same version of excel
that created the macro?

 

[Sam via OfficeKB.com]

Hi Dave,

Thank you very much for reply and assistance.

It could be lots of things.

What line causes the error--you have lots of .pastespecial lines.

The first PasteSpecial line. However, I've tried each of them individually
and I get the same error message from each.

Do you have any worksheet/workbook events that are running when you change
sheets or change selection? Maybe running that macro is killing the clipboard.
No

Maybe you could drop the .select's:

When I posted your code below into a new module, most of it was highlighted
red text.

These lines were in normal black text:

Worksheets("Sales").Range("Sales_Table").Clear

Application.CutCopyMode = False

Yes

Further help appreciated.

Cheers,
Sam

Option Explicit
Sub testme()

Dim RngToCopy As Range
Dim DestCell As Range

With Worksheets("Sales Freq").Range("Sales_Table")
Set RngToCopy = .Resize(.Rows.Count, .Columns.Count + 3)
End With

Set DestCell = Worksheets("Sales").Range("A4")

With DestCell
.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

 

[Dave Peterson]

The code I posted compiled ok for me.

Maybe it's time to post the version that you used.

 

[Sam via OfficeKB.com]

Hi Dave,

The code I posted compiled ok for me.

Maybe it's time to post the version that you used.

I used the code from your first post:

Option Explicit
Sub testme()

Dim RngToCopy As Range
Dim DestCell As Range

Worksheets("Sales").Range("Sales_Table").Clear

With Worksheets("Sales Freq").Range("Sales_Table")
Set RngToCopy = .Resize(.Rows.Count, .Columns.Count + 3)
End With

Set DestCell = Worksheets("Sales").Range("A4")

RngToCopy.Copy

With DestCell
.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

Application.CutCopyMode = False

End Sub

Cheers,
Sam

 

[Dave Peterson]

I can't duplicate any syntax error.

Maybe you're picking something up when you copy from the web interface
(officekb.com???).

 

[Sam via OfficeKB.com]

Hi Dave,

I'll go through the code again, line by line.

Cheers
Sam

 

[Sam via OfficeKB.com]

Hi Dave,

I was picking something up when copying the code; looks like some blank
spaces or characters. However, I'm still having problems. The 1st
PasteSpecial works but on the 2nd I get error message:
Run-time error '1004'
PasteSpecial method of Range class failed.

With DestCell
..PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
..PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
..PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

If anything springs to mind please advise.

Cheers,
Sam

 

[Dave Peterson]

It worked fine for me.

Try adding this line before each .pastespecial line:

msgbox "Cutcopymode is: " & application.cutcopymode

If you see 0, then the clipboard has been erased for some reason.

You could do multiple .copy and .pastespecial's.

With DestCell
RngToCopy.Copy
.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

RngToCopy.Copy
.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

RngToCopy.Copy
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

But if you see 1 or 2 (copy or cut is in effect), then I don't have a guess.

 

[Sam via OfficeKB.com]

Hi Dave,

Thank you ever so much for all your help, very much appreciated.

As suggested, I added msgbox "Cutcopymode is: " & application.cutcopymode
before each .pastespecial line.

The 1st msgbox returned 1 - successful pastespecial.
The 2nd msgbox returned 0 - no idea why the clipboard has been erased?

I'll probably go with your option of multiple copy and pastespecial's but it
would be interesting to know why the clipboard is being erased on the second
pastespecial attempt. Is their an option setting that needs to be reset? If
anyone can shed some light on the above, please advise.

Cheers,
Sam

It worked fine for me.

Try adding this line before each .pastespecial line:

msgbox "Cutcopymode is: " & application.cutcopymode

If you see 0, then the clipboard has been erased for some reason.

You could do multiple .copy and .pastespecial's.

With DestCell
RngToCopy.Copy
.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

 

[Dave Peterson]

It's not a setting that needs to be toggled.

Do you have a worksheet_Change event that fires after you do the pasting? Lots
of macros will clear the clipboard.

If yes, then you could turn off events before pasting:

Application.enableevents = false
'code to do the pastespecial's
application.enableevents = true

But I don't have a real guess why you're losing the clipboard. I'd just add the
..copy statements.

 

[Sam via OfficeKB.com]

Hi Dave,

When you first asked,

Do you have any worksheet/workbook events that are running when you change
sheets or change selection? Maybe running that macro is killing the clipboard.

I said, no; as far as I could see there was nothing running or enabled when I
changed sheets. However, there is a print event in "ThisWorkbook" but it
doesn't directly effect the macro and there are no print requests within this
macro, nor did I select anything to be printed whilst the macro was running?
I'm puzzled? But the main thing is Application.enableevents = false has
allowed the multiple pastespecial items to be successfully pasted.

I do use this Private Sub Workbook_BeforePrint(Cancel As Boolean) in
ThisWorkbook.

I've just added your suggestion below to the macro and the paste process
works fine now.

Application.enableevents = false
'code to do the pastespecial's
application.enableevents = true

Thank you very much for all your time, help and patience.

Cheers,
Sam

It's not a setting that needs to be toggled.

Do you have a worksheet_Change event that fires after you do the pasting? Lots
of macros will clear the clipboard.

I do use this Private Sub Workbook_BeforePrint(Cancel As Boolean) in
ThisWorkbook. I didn't think it had any effect on the macro but it obviously
does!

If yes, then you could turn off events before pasting:

Application.enableevents = false
'code to do the pastespecial's
application.enableevents = true

But I don't have a real guess why you're losing the clipboard. I'd just add the
.copy statements.

I did, thanks.

 

[Dave Peterson]

The _Beforeprint event isn't the cause of your trouble.

But if the application.enableevents stuff fixed it, then you really do have
other events that are firing.

It could be a worksheet event, a workbook event or even an application event.
But it is one of these. And those application events could be created by
another workbook/addin--it doesn't have to be related to the workbook that
you're currently using.

 

[Dave Peterson]

ps. It could even be a COM addin that doesn't show up under Tools|Addins.

Any chance you're running Google Desktop. It sticks its fingers into lots of
places--including when you change an excel (or MSWord) document.

 

[Sam via OfficeKB.com]

Hi Dave,

Thanks for follow-up. Please see below.

The _Beforeprint event isn't the cause of your trouble.

But if the application.enableevents stuff fixed it, then you really do have
other events that are firing.

It could be a worksheet event, a workbook event or even an application event.
But it is one of these. And those application events could be created by
another workbook/addin--it doesn't have to be related to the workbook that
you're currently using.

Think I've found the culprit: a worksheet event on the sheet that was using
PasteSpecial.

Cheers,
Sam

 

[Sam via OfficeKB.com]

Hi Dave,

ps. It could even be a COM addin that doesn't show up under Tools|Addins.

Any chance you're running Google Desktop. It sticks its fingers into lots of
places--including when you change an excel (or MSWord) document.
No

Please see Post below.

Cheers,
Sam

 

[Dave Peterson]

That makes lots more sense--and now you have a way to avoid those events in the
future!

 

[Sam via OfficeKB.com]

That makes lots more sense--and now you have a way to avoid those events in the
future!

Most definitely.
Cheers!