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

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

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

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

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

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

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

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

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

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