| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy & Paste Named Range Contents
How do I copy and paste the 'contents' of a Named Range via VBA? I don't
want to copy and paste the range name. For example, if the Named Range 'Table' is cells A1:F10 of Sheet name 'Data', how do I code in VBA the ability to paste the contents of 'Table' into sheet name 'Summary' starting at cell G10? Thank you. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy & Paste Named Range Contents
Try this statement...
Range("Table").Copy Worksheets("Summary").Range("G10") Rick "BJ" wrote in message ... How do I copy and paste the 'contents' of a Named Range via VBA? I don't want to copy and paste the range name. For example, if the Named Range 'Table' is cells A1:F10 of Sheet name 'Data', how do I code in VBA the ability to paste the contents of 'Table' into sheet name 'Summary' starting at cell G10? Thank you. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy & Paste Named Range Contents
Thanks Rick - worked great - but I forgot to mention that I need to transpose
the data. How do I work in the PasteSpecial code? "Rick Rothstein (MVP - VB)" wrote: Try this statement... Range("Table").Copy Worksheets("Summary").Range("G10") Rick "BJ" wrote in message ... How do I copy and paste the 'contents' of a Named Range via VBA? I don't want to copy and paste the range name. For example, if the Named Range 'Table' is cells A1:F10 of Sheet name 'Data', how do I code in VBA the ability to paste the contents of 'Table' into sheet name 'Summary' starting at cell G10? Thank you. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy & Paste Named Range Contents
That was a big item to forget.<g Try it this way then...
Range("Table").Copy Worksheets("Summary").Range("G10").PasteSpecial Transpose:=True Application.CutCopyMode = False Rick "BJ" wrote in message ... Thanks Rick - worked great - but I forgot to mention that I need to transpose the data. How do I work in the PasteSpecial code? "Rick Rothstein (MVP - VB)" wrote: Try this statement... Range("Table").Copy Worksheets("Summary").Range("G10") Rick "BJ" wrote in message ... How do I copy and paste the 'contents' of a Named Range via VBA? I don't want to copy and paste the range name. For example, if the Named Range 'Table' is cells A1:F10 of Sheet name 'Data', how do I code in VBA the ability to paste the contents of 'Table' into sheet name 'Summary' starting at cell G10? Thank you. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy & Paste Named Range Contents
I know ... I'm a forgetful putz ... my wife would agree ... =)
When I use the code I'm getting a VBA error ... With the word 'Transpose' highlighted the dialog box indicates: Compile Error: Expected: end of statement Is there a space between 'PasteSpecial' and 'Transpose'? (That's when I get the error message). If I remove the space I get the same error but the ':=' is now highlighted instead. My apologies for my stupidity. bj "Rick Rothstein (MVP - VB)" wrote: That was a big item to forget.<g Try it this way then... Range("Table").Copy Worksheets("Summary").Range("G10").PasteSpecial Transpose:=True Application.CutCopyMode = False Rick "BJ" wrote in message ... Thanks Rick - worked great - but I forgot to mention that I need to transpose the data. How do I work in the PasteSpecial code? "Rick Rothstein (MVP - VB)" wrote: Try this statement... Range("Table").Copy Worksheets("Summary").Range("G10") Rick "BJ" wrote in message ... How do I copy and paste the 'contents' of a Named Range via VBA? I don't want to copy and paste the range name. For example, if the Named Range 'Table' is cells A1:F10 of Sheet name 'Data', how do I code in VBA the ability to paste the contents of 'Table' into sheet name 'Summary' starting at cell G10? Thank you. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy & Paste Named Range Contents
Sorry, I should have included the worksheet reference for the Table. Try
this... Worksheets("Data").Range("Table").Copy Worksheets("Summary").Range("G10").PasteSpecial Transpose:=True Application.CutCopyMode = False Rick "BJ" wrote in message ... I know ... I'm a forgetful putz ... my wife would agree ... =) When I use the code I'm getting a VBA error ... With the word 'Transpose' highlighted the dialog box indicates: Compile Error: Expected: end of statement Is there a space between 'PasteSpecial' and 'Transpose'? (That's when I get the error message). If I remove the space I get the same error but the ':=' is now highlighted instead. My apologies for my stupidity. bj "Rick Rothstein (MVP - VB)" wrote: That was a big item to forget.<g Try it this way then... Range("Table").Copy Worksheets("Summary").Range("G10").PasteSpecial Transpose:=True Application.CutCopyMode = False Rick "BJ" wrote in message ... Thanks Rick - worked great - but I forgot to mention that I need to transpose the data. How do I work in the PasteSpecial code? "Rick Rothstein (MVP - VB)" wrote: Try this statement... Range("Table").Copy Worksheets("Summary").Range("G10") Rick "BJ" wrote in message ... How do I copy and paste the 'contents' of a Named Range via VBA? I don't want to copy and paste the range name. For example, if the Named Range 'Table' is cells A1:F10 of Sheet name 'Data', how do I code in VBA the ability to paste the contents of 'Table' into sheet name 'Summary' starting at cell G10? Thank you. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy & Paste Named Range Contents
Hi Rick - many many thanks ...
I've got some empty columns as part of my named range. When the range is copied, the blank columns are removed. Is that a permanent issue or is there a workaround? "Rick Rothstein (MVP - VB)" wrote: Sorry, I should have included the worksheet reference for the Table. Try this... Worksheets("Data").Range("Table").Copy Worksheets("Summary").Range("G10").PasteSpecial Transpose:=True Application.CutCopyMode = False Rick "BJ" wrote in message ... I know ... I'm a forgetful putz ... my wife would agree ... =) When I use the code I'm getting a VBA error ... With the word 'Transpose' highlighted the dialog box indicates: Compile Error: Expected: end of statement Is there a space between 'PasteSpecial' and 'Transpose'? (That's when I get the error message). If I remove the space I get the same error but the ':=' is now highlighted instead. My apologies for my stupidity. bj "Rick Rothstein (MVP - VB)" wrote: That was a big item to forget.<g Try it this way then... Range("Table").Copy Worksheets("Summary").Range("G10").PasteSpecial Transpose:=True Application.CutCopyMode = False Rick "BJ" wrote in message ... Thanks Rick - worked great - but I forgot to mention that I need to transpose the data. How do I work in the PasteSpecial code? "Rick Rothstein (MVP - VB)" wrote: Try this statement... Range("Table").Copy Worksheets("Summary").Range("G10") Rick "BJ" wrote in message ... How do I copy and paste the 'contents' of a Named Range via VBA? I don't want to copy and paste the range name. For example, if the Named Range 'Table' is cells A1:F10 of Sheet name 'Data', how do I code in VBA the ability to paste the contents of 'Table' into sheet name 'Summary' starting at cell G10? Thank you. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy & Paste Named Range Contents
Hmm! I'm not sure what to tell you as that does not happen on my XL2003
system... blank columns become blank rows after the transpose. Do you have any other code running along with what I posted? If so, could you show it to us? Rick "BJ" wrote in message ... Hi Rick - many many thanks ... I've got some empty columns as part of my named range. When the range is copied, the blank columns are removed. Is that a permanent issue or is there a workaround? "Rick Rothstein (MVP - VB)" wrote: Sorry, I should have included the worksheet reference for the Table. Try this... Worksheets("Data").Range("Table").Copy Worksheets("Summary").Range("G10").PasteSpecial Transpose:=True Application.CutCopyMode = False Rick "BJ" wrote in message ... I know ... I'm a forgetful putz ... my wife would agree ... =) When I use the code I'm getting a VBA error ... With the word 'Transpose' highlighted the dialog box indicates: Compile Error: Expected: end of statement Is there a space between 'PasteSpecial' and 'Transpose'? (That's when I get the error message). If I remove the space I get the same error but the ':=' is now highlighted instead. My apologies for my stupidity. bj "Rick Rothstein (MVP - VB)" wrote: That was a big item to forget.<g Try it this way then... Range("Table").Copy Worksheets("Summary").Range("G10").PasteSpecial Transpose:=True Application.CutCopyMode = False Rick "BJ" wrote in message ... Thanks Rick - worked great - but I forgot to mention that I need to transpose the data. How do I work in the PasteSpecial code? "Rick Rothstein (MVP - VB)" wrote: Try this statement... Range("Table").Copy Worksheets("Summary").Range("G10") Rick "BJ" wrote in message ... How do I copy and paste the 'contents' of a Named Range via VBA? I don't want to copy and paste the range name. For example, if the Named Range 'Table' is cells A1:F10 of Sheet name 'Data', how do I code in VBA the ability to paste the contents of 'Table' into sheet name 'Summary' starting at cell G10? Thank you. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy & Paste Named Range Contents
Sometimes even my stupidity astounds me ... I had left the transpose = true
statement in ... maybe it's time to go outside and enjoy the 90 degree MN summer for a while ... =) Many thanks again Rick - your responsiveness on this board is truly a blessing ... "Rick Rothstein (MVP - VB)" wrote: Hmm! I'm not sure what to tell you as that does not happen on my XL2003 system... blank columns become blank rows after the transpose. Do you have any other code running along with what I posted? If so, could you show it to us? Rick "BJ" wrote in message ... Hi Rick - many many thanks ... I've got some empty columns as part of my named range. When the range is copied, the blank columns are removed. Is that a permanent issue or is there a workaround? "Rick Rothstein (MVP - VB)" wrote: Sorry, I should have included the worksheet reference for the Table. Try this... Worksheets("Data").Range("Table").Copy Worksheets("Summary").Range("G10").PasteSpecial Transpose:=True Application.CutCopyMode = False Rick "BJ" wrote in message ... I know ... I'm a forgetful putz ... my wife would agree ... =) When I use the code I'm getting a VBA error ... With the word 'Transpose' highlighted the dialog box indicates: Compile Error: Expected: end of statement Is there a space between 'PasteSpecial' and 'Transpose'? (That's when I get the error message). If I remove the space I get the same error but the ':=' is now highlighted instead. My apologies for my stupidity. bj "Rick Rothstein (MVP - VB)" wrote: That was a big item to forget.<g Try it this way then... Range("Table").Copy Worksheets("Summary").Range("G10").PasteSpecial Transpose:=True Application.CutCopyMode = False Rick "BJ" wrote in message ... Thanks Rick - worked great - but I forgot to mention that I need to transpose the data. How do I work in the PasteSpecial code? "Rick Rothstein (MVP - VB)" wrote: Try this statement... Range("Table").Copy Worksheets("Summary").Range("G10") Rick "BJ" wrote in message ... How do I copy and paste the 'contents' of a Named Range via VBA? I don't want to copy and paste the range name. For example, if the Named Range 'Table' is cells A1:F10 of Sheet name 'Data', how do I code in VBA the ability to paste the contents of 'Table' into sheet name 'Summary' starting at cell G10? Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy contents of a named range to a section on working area | Excel Discussion (Misc queries) | |||
| After Copy and Paste, Add to an Existing Named Range | Excel Programming | |||
| Copy and Paste 2 columns from a named range? | Excel Programming | |||
| Copy named range contents to activecell position | Excel Programming | |||
Linear Mode
