Setting Active Cell as Variable

[kitkatjam235]

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

 

[ChrisBM]

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

[B]set State = "A" & i & " CI"

[/B]

 

[adam087]

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...

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]

You can do all that without selecting.

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]

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...

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.

 

[mikerickson]

nm

 

[adam087]

You can iterate through different values of i using a For...Next loop, or similar.


/AJ