VBA Copy paste to different locations based on value

[Kauerauf]

Hi guys

I hope anyone can help me with my excel problem.

I want to create a “CopyPaste” VBA that based on a cell value it will paste in different areas. The “copyarea/copyrange” will always be the same (A11:B20). But based on the cell value in cell “A1” the pastearea/pasterange will change.



Lets say that if cell “A1” has the value “Stockholm” I want the VBA to copy the area “A11:B20” to “B11:C20”. If the value in cell “A1” is “London” I want the VBA to copy the area “A11:B20” to B21:C30”. If the value in cell “A1” is “New York” I want the VBA to copy the area “A11:B20” to “B31:C40” and so on.

Can anyone help me with this?

Wbr // Anders Kauerauf

 

[Fluff]

If you copy A11:B20 to B11:C20 then you will overwrite the values of B11:B20, is that correct?

 

[Kauerauf]

@Fluff - you are absolutely right. I meant to paste it in column C & D (and not B & C)

 

[Fluff]

How about

Sub Kauerauf()
   Dim PasteRng As Range
   Select Case LCase(Range("A1").Value)
      Case "stockholm": Set PasteRng = Range("C11")
      Case "london": Set PasteRng = Range("C21")
   End Select
   Range("A11:B20").Copy PasteRng
End Sub

Just add the rest of the Cases, making sure that the names are all lower case.

 

[Kauerauf]

How about

Sub Kauerauf()
   Dim PasteRng As Range
   Select Case LCase(Range("A1").Value)
      Case "stockholm": Set PasteRng = Range("C11")
      Case "london": Set PasteRng = Range("C21")
   End Select
   Range("A11:B20").Copy PasteRng
End Sub

Just add the rest of the Cases, making sure that the names are all lower case.

You are my hero! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.