I have two workbooks: Schedule.xlsx (wkb1) which contains data and Temp.xls (wkb2), which stores temporary data. Schedule.xlsx is basically a calender that goes across for each Aircraft Type (AC Type). So it looks like:
AC01 Nov 1 data Nov 2 Data.....Nov 30 data
AC02 Nov 1 data Nov 2 Data.....Nov 30 data
...
AC40 Nov 1 data Nov 2 Data.....Nov 30 data
I have attached the Schedule.xlsx file.
Mind you the "data" above is a 7x9 table for each day. We want those in a table in Access. My VBA code copies that specific window range data from Schedule into temp file. Then, the VBA code transfers the data in temp file into a temp table in access. A query will append what ever is in temp table into a staging table. The problem is, VBA keeps copying-pasting only the first range specified in the first iteration of the loop. Theoretically after the first iteration, it should move to a new range, copy paste that. Then in 3rd iteration, move to a new range, copy paste that, and so on. I understand that this is kinda long winded, but I put lots of descriptive comments in the code below. But any help is appreciated. Here's the code:
Code:
'''Workbook Operations
Set wkb1 = Workbooks.Open(FileName:=InputFolder & "Schedule.xlsx")
Set wkb2 = Workbooks.Open(FileName:=InputFolder & "temp.xls")
wkb1.Activate
Set wksh1 = wkb1.Sheets("Sheet1")
Set wksh2 = wkb2.Sheets("Sheet1")
''Get Last Row and Last Column in Schedule file
LastRow = wkb1.ActiveSheet.UsedRange.Rows.Count
LastColumn = wkb1.ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
''Initialization of variables. x and i are for the For Loop, but the range I want is specified in StartRow, StartCol, EndRow and EndCol. You will see in a second below in the For Loop
StartRow = 32
StartCol = 9
EndRow = 40
EndCol = 17
i = 32
x = 9
'Outer Loop for Row Range. Move vertical until conditions are met
wkb1.Activate
For i = StartRow To LastRow
'Inner Loop for Column Range. Move Horizontal until conditions are met
For x = StartCol To LastColumn
'This is where I'm copying the range
wksh1.Range(Cells(StartRow, StartCol), Cells(EndRow, EndCol)).Copy
'Also copying some data in variables
ACType = ActiveSheet.Cells(StartRow, 1).Value
Pax = ActiveSheet.Cells(StartRow, 2).Value
SchDate = ActiveSheet.Cells(30, StartCol).Value
'Pasting the range into a new workbook.
wkb2.Activate
wksh2.Range(Cells(2, 3), Cells(10, 11)).Select
ActiveSheet.Paste
LastRow2 = wkb2.ActiveSheet.UsedRange.Rows.Count
wkb2.Save
wkb2.Close
'Here, transfer the temp file into a a table called TEMP_UPTAKE_WEEK_STAGING
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "TEMP_UPTAKE_WEEK_STAGING", InputFolder & FileName2, True, "A1:L" & LastRow2 & ""
'Update the data with variables. Not important
DoCmd.RunSQL "UPDATE TEMP_UPTAKE_WEEK_STAGING SET ACTYPE = '" & ACType & "'"
DoCmd.RunSQL "UPDATE TEMP_UPTAKE_WEEK_STAGING SET SCHDATE = '" & SchDate & "'"
DoCmd.RunSQL "UPDATE TEMP_UPTAKE_WEEK_STAGING SET Pax = '" & Pax & "'"
'Append temp table to staging table.
DoCmd.OpenQuery "APPEND_TEMP_UPTAKE_WEEK_TO_STAGING"
Debug.Print "Schedule Date: " & SchDate & " AC Type: " & ACType & " PAX: " & Pax & " StartRow: " & StartRow & " StartCol: " & StartCol & " EndRow: " & EndRow & " EndCol: " & EndCol
'This is the part where it should move to the next range, but it's not!
StartCol = StartCol + 15
EndCol = EndCol + 15
x = StartCol
DoCmd.RunSQL "DELETE FROM TEMP_UPTAKE_WEEK_STAGING"
'Just a quick way to exit the process
If ACType = "AC03" Then
Exit Sub
End If
Next x
'Move to a new set that is 10 rows below.
StartRow = StartRow + 10
i = StartRow
StartCol = 9
EndCol = 17
Next i
My Access database for weird reason does not work if I remove everything and only keep the relevant bits. I can attach the one I have, but the problem is that it is looking for specific folders which you will need to create, so I was trying to avoid that 
I hope the error is simple like opening or closing a workbook. Any help is super appreciated.