VBA to copy & paste named range to another sheet based upon cell value

[m_ner]

Hello,

I am a bit of a VBA novice, and have ran into a recent issue trying to copy and paste a named range from one worksheet into a specific cell on another worksheet, based upon a value.

I've attached a sample data set so you can see what I'm talking about, but the sheet works as follows.

On Worksheet "Sample":
1. A user selects a "city" from a drop-down - Cell B1
2. A vlookup is written in D1 that looks up a potential $ value for that city selected in B1
3. A range named region_potential (H4:H44) is calculated by multiplying the values in G4:G44 * the potential $ value in D1

On Worksheet "Cities":
1. There is a list of all the potential cities that can be selected from the drop-down in cell B1 on the "Sample" Worksheet. They are listed horizontally across columns, D2:M2 on the "Cities" worksheet

What I would like to do:
1. Write a macro that copies the data in the range region_potential from the "Sample" worksheet, and paste it into the "Cities" worksheet under the column header for the city selected in the "city" drop-down in cell B1 on the "Sample" worksheet.

So if "Toronto" is chosen, the values in the range: region_potential (H4:H44) on the "Sample" worksheet will be pasted under the "Toronto" header on the "Cities" worksheet. The values pasted should override any previous values in there, as I will be using this table for future hlookup & index/match formula's.

This way for any city selected I will be able to simply run the macro to paste that cities values under that respective city in the "Cities" worksheet

Any help is greatly appreciated, and as I mentioned previously a sample worksheet is attached.

Thanks!

 

[Hui]

Copy this code into the Sample Worksheet Module in VBA

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim City As String

If Intersect(ActiveCell, Range("B1")) Is Nothing Then Exit Sub
Worksheets("Sample").Calculate
Range("Region_Potential").Copy
City = ActiveCell.Text

Worksheets("Cities").Select

With Worksheets("Cities").Range("D1:m1")
  Set c = .Find(City, LookIn:=xlValues)
End With

c.Offset(1, 0).PasteSpecial Paste:=xlPasteValues, _
  Operation:=xlNone, _
  SkipBlanks:=False, _
  Transpose:=False

Application.CutCopyMode = False
Selection.Style = "Comma"
End Sub

or see attached file:

The file must be saved as a *.xlsm or *.xlsb file type

 

[m_ner]

Hui,

Thank you so much this looks great! I just gave it a test and it works awesome, and is very easy to follow in the VBA.

My one question is if I didn't want to the sheet to automatically change to the "Cities" worksheet when the data is pasted into the appropriate column, instead I want the the following cell to be selected:

Worksheets("Sample").Range("B1").Select

If I put it in at the end of the Private Sub it is ignored. How would I go about and take care of that?

Thanks!

 

[Hui]

Use

Worksheets("Sample").select
Range("B1").Select

 

[m_ner]

Excellent, that worked perfectly!

Thanks again

 

[KTalridge]

Hello - This is late question to this thread, so i hope Excel Nija is still on the site. I have copied the code exactly and then made some name changes to fit my needs. However, when i select my analgous city, the code does not execute.

I have a feeling that i need to do something with properties or data upate, but i cannot figure it out.

The operation:
1. Copy range CurPrdCls on input to the appropriate colmn on the ClsAct sheet.

The vba does not execute.

Help?