Copy Named Range from one sheet to another with formatting

  • Thread starter Sam via OfficeKB.com
  • Start date
S

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
 
D

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?
 
S

Sam via OfficeKB.com

Hi Dave,

Thank you very much for reply and assistance.

Dave said:
It could be lots of things.
Click to expand...
What line causes the error--you have lots of .pastespecial lines.
Click to expand...

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:
Click to expand...

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
Click to expand...

Yes

Further help appreciated.

Cheers,
Sam

Option Explicit
Sub testme()
Click to expand...
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
Click to expand...
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
Click to expand...
 
D

Dave Peterson

The code I posted compiled ok for me.

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

Sam via OfficeKB.com

Hi Dave,

Dave said:
The code I posted compiled ok for me.
Click to expand...
Maybe it's time to post the version that you used.
Click to expand...

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
 
D

Dave Peterson

I can't duplicate any syntax error.

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

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
 
D

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.
 
S

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

Dave said:
It worked fine for me.
Click to expand...
Try adding this line before each .pastespecial line:
Click to expand...
msgbox "Cutcopymode is: " & application.cutcopymode
Click to expand...
If you see 0, then the clipboard has been erased for some reason.
Click to expand...
You could do multiple .copy and .pastespecial's.
Click to expand...
With DestCell
RngToCopy.Copy
.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Click to expand...
 
D

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.
 
S

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.
Click to expand...

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
Click to expand...

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

Cheers,
Sam

Dave said:
It's not a setting that needs to be toggled.
Click to expand...
Do you have a worksheet_Change event that fires after you do the pasting? Lots
of macros will clear the clipboard.
Click to expand...

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:
Click to expand...
Application.enableevents = false
'code to do the pastespecial's
application.enableevents = true
Click to expand...
But I don't have a real guess why you're losing the clipboard. I'd just add the
.copy statements.
Click to expand...

I did, thanks.
 
D

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.
 
D

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.
 
S

Sam via OfficeKB.com

Hi Dave,

Thanks for follow-up. Please see below.

Dave said:
The _Beforeprint event isn't the cause of your trouble.
Click to expand...
But if the application.enableevents stuff fixed it, then you really do have
other events that are firing.
Click to expand...
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.
Click to expand...

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

Cheers,
Sam
 
S

Sam via OfficeKB.com

Hi Dave,

Dave said:
ps. It could even be a COM addin that doesn't show up under Tools|Addins.
Click to expand...
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
Click to expand...

Please see Post below.

Cheers,
Sam
 
D

Dave Peterson

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error 1004 paste method of worksheet class failed 3
conso macro 0
Want to Copy and paste to new sheet range except hidden rows. 4
copy named range & paste values 2
define Range in vba using contents of a cell that is named 1
Run macro from active workbook whose name keeps changing 20
lenghty code 4
Tannspose and cycle 4

Top