Copy Named Range from one sheet to another with formatting
 

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200803/1

Copy Named Range from one sheet to another with formatting
 

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" wrote:

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200803/1

--

Dave Peterson

Copy Named Range from one sheet to another with formatting
 

Hi Dave,

Thank you very much for reply and assistance.

Dave Peterson wrote:
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

RngToCopy.Copy

Application.CutCopyMode = False

---------------------------------------------------------------------

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

Yes

Further help appreciated.

Cheers,
Sam


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?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200803/1

Copy Named Range from one sheet to another with formatting
 

The code I posted compiled ok for me.

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

"Sam via OfficeKB.com" wrote:

Hi Dave,

Thank you very much for reply and assistance.

Dave Peterson wrote:
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

RngToCopy.Copy

Application.CutCopyMode = False

---------------------------------------------------------------------

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

Yes

Further help appreciated.

Cheers,
Sam

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?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200803/1

--

Dave Peterson

Copy Named Range from one sheet to another with formatting
 

Hi Dave,

Dave Peterson wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200803/1

Copy Named Range from one sheet to another with formatting
 

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" wrote:

Hi Dave,

Dave Peterson wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200803/1

--

Dave Peterson

Copy Named Range from one sheet to another with formatting
 

Hi Dave,

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

Cheers
Sam

Dave Peterson wrote:
I can't duplicate any syntax error.

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200803/1

Copy Named Range from one sheet to another with formatting
 

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 wrote:
I can't duplicate any syntax error.

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

--
Message posted via http://www.officekb.com

Copy Named Range from one sheet to another with formatting
 

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" wrote:

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 wrote:
I can't duplicate any syntax error.

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

--
Message posted via http://www.officekb.com

--

Dave Peterson

Copy Named Range from one sheet to another with formatting
 

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 Peterson wrote:
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.

--
Message posted via http://www.officekb.com

Copy Named Range from one sheet to another with formatting
 

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" wrote:

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 Peterson wrote:
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.

--
Message posted via http://www.officekb.com

--

Dave Peterson

Copy Named Range from one sheet to another with formatting
 

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

Dave Peterson wrote:
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.

--
Message posted via http://www.officekb.com

Copy Named Range from one sheet to another with formatting
 

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.



"Sam via OfficeKB.com" wrote:

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

Dave Peterson wrote:
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.

--
Message posted via http://www.officekb.com

--

Dave Peterson

Copy Named Range from one sheet to another with formatting
 

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.

Dave Peterson wrote:

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.

"Sam via OfficeKB.com" wrote:

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

Dave Peterson wrote:
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.

--
Message posted via http://www.officekb.com

--

Dave Peterson

--

Dave Peterson

Copy Named Range from one sheet to another with formatting
 

Hi Dave,

Thanks for follow-up. Please see below.

Dave Peterson wrote:
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

--
Message posted via http://www.officekb.com

Copy Named Range from one sheet to another with formatting
 

Hi Dave,

Dave Peterson wrote:
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

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

Please see Post below.

Cheers,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200803/1

Copy Named Range from one sheet to another with formatting
 

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

"Sam via OfficeKB.com" wrote:

Hi Dave,

Thanks for follow-up. Please see below.

Dave Peterson wrote:
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

--
Message posted via http://www.officekb.com

--

Dave Peterson

Copy Named Range from one sheet to another with formatting
 

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

Most definitely.
Cheers!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200803/1