Excel VBA Copy Range to Location Determined by Cell Value

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Excel VBA Copy Range to Location Determined by Cell Value

Post by MSingh »

Hi,

I need to copy from source:
Sheets("Data").Range("A12:K213")

To Destination by Paste Special Values:
Sheets("Edit Data")
Destination is determined by value in Sheets("Data").Range("AA3") then 1 column to the right.
This value could be from 1 to 200

ie.
Select case Sheets("Data").Range("AA3") .Value

Case 1, is 1, then
paste special in Sheets("Edit Data"). range("a1")

Case 2, is 2 then
paste special in Sheets("Edit Data") by value in sheets("data").range("aa3") but add 1 more column

so that there is 1 column separating the pasted ranges.

Thanks in advance
Mohamed

User avatar
HansV
Administrator
Posts: 74138
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel VBA Copy Range to Location Determined by Cell Value

Post by HansV »

Try this:

Sheets("Data").Range("A12:K213").Copy
Sheets("Edit Data").Cells(1, 2 * Sheets("Data").Range("AA3") - 1).PasteSpecial xlPasteValues
Regards,
Hans

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Re: Excel VBA Copy Range to Location Determined by Cell Value

Post by MSingh »

Thanks Hans