| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2002 SP3 Copy Worksheet with another sheet named range now # | Excel Discussion (Misc queries) | |||
| Copy Several named Range in many sheets to a summary sheet | Excel Programming | |||
| named range in conditional formatting | Excel Programming | |||
| Finding a named range based on cell value and copy/paste to same sheet? | Excel Programming | |||
| named range, conditional formatting | Excel Programming | |||
Hybrid Mode
