VBA Copy paste to different locations based on value

Kauerauf

New Member
Joined
Jan 10, 2018
Messages
6
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
If you copy A11:B20 to B11:C20 then you will overwrite the values of B11:B20, is that correct?
 
Upvote 0
How about
VBA Code:
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.
 
Upvote 0
How about
VBA Code:
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!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top