Copy & Paste Named Range Contents

[BJ]

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.

 

[Rick Rothstein \(MVP - VB\)]

Try this statement...

Range("Table").Copy Worksheets("Summary").Range("G10")

Rick

 

[BJ]

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\)]

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]

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\)]

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]

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\)]

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]

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