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