Setting Active Cell as Variable

kitkatjam235

New Member
Joined
Oct 7, 2014
Messages
11
I have a list of states in column A of workbook "Book1" and over 100 tabs of states in a second workbook "Copy of Exhibit 9". I have a macro that pulls data from each State tab (ie "ZZ CI") and copies the data into the corresponding state row in "Book1"; however, it's just the same code repeated 50 times. I'm trying to make the macro select cell A1 in "book1" and find the corresponding tab in "exhibit 9" that has that state with "CI" at the end of it, copy the data, then jump to A2 and select the data... and so on until there are no more states. I am getting stuck trying to get the sheet it selects to be a variable that is equal to the Ai cell it's on. Any ideas? Below is what i have so far, the bold is where I believe I am struggling.


Dim WBN As Workbook 'new workbook
Dim WBE As Workbook 'exhibit workbook
Dim State As String
Dim i As Integer


i = 1
set State = Ai&" CI"


Set WBE = Workbooks("Copy of Exhibit 9.xlsm")
Set WBN = Workbooks("Book1.xlsx")


WBN.Activate
Range("A1").Select
WBE.Sheets(State).Range("G21:H21").Copy
WBN.Activate
Range("C" & i).Select
ActiveSheet.Paste
WBE.Activate
Range("G39:H39").Select
Application.CutCopyMode = False
Selection.Copy
WBN.Activate
Range("E" & i).Select
ActiveSheet.Paste
WBE.Activate

i = i + 1
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

ChrisBM

Board Regular
Joined
Sep 22, 2014
Messages
215


Not 100% sure what you are after but the set State line looks weird. I think you mean:

Code:
[B]set State = "A" & i & " CI"
[/B]
 

adam087

Well-known Member
Joined
Jun 7, 2010
Messages
1,356
I've not looked at the whole code, but State is a string variable so doesn't need a SET statement. And you need to be more explicit in referencing the range. Try...
Code:
State = Sheets("Sheet1").Range("A"&i).Value & "CI"

Does that help?

Welcome to the board btw

Edit: to clarify, you need a SET statement with object variables.

/AJ
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,907
You can do all that without selecting.

Code:
WBN.Range("C" & i).Resize(1, 2).Value = WBE.Sheets(State).Range("G21:H21").Value


WBN.Range("E" & i).Resize(1, 2).Value = WBE.Sheets(State).Range("G39:H39").Value
 

kitkatjam235

New Member
Joined
Oct 7, 2014
Messages
11

ADVERTISEMENT

I've not looked at the whole code, but State is a string variable so doesn't need a SET statement. And you need to be more explicit in referencing the range. Try...
Code:
State = Sheets("Sheet1").Range("A"&i).Value & "CI"

Does that help?

Welcome to the board btw

Edit: to clarify, you need a SET statement with object variables.

/AJ


Thanks! I reworked that a little to get State = Sheets("Sheet1").Cells(1,1).Value & " CI" because I'm trying to pull the cell A1, then A2... but it came back as an error on that line. That is what I'm looking for though, I'm not sure how to rework it.
 

adam087

Well-known Member
Joined
Jun 7, 2010
Messages
1,356
You can iterate through different values of i using a For...Next loop, or similar.


/AJ
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

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
Top