Macro Debug Help

[twmills]

Hello,

I'm trying to figure out what's causing this macro to fail. I didn't write the code, but I'm trying to figure out how to fix it. Also, I'm not entirely sure what it's trying to do, so I can't provide an example of what the end result should look like. If anyone can please let me know what it's trying to accomplish, as well as why it's failing...that would be greatly appreciated.

The macro I'm trying to run is in the Example Data spreadsheet, and it's called Proformas. There's actually a macro button called "Proformas" on the Data_1 tab. I'm guessing it's attempting to pull in data from the Example Proformas spreadsheet, but it's unable to for some reason. Both spreadsheets - I believe - need to be opened for the code to work.

Here's the code it's running, and the bold portion is where it's failing:


Sub Proformas()
If Verify_Month Then Exit Sub
Dim xWB As Workbook
Dim yWB As Workbook
Set xWB = ActiveWorkbook
For Each WB In Application.Workbooks
If WB.Name Like "*Proformas*" Then
Set yWB = WB
End If
Next
If xMonth_in_Quarter = 1 Then xC = "E"
If xMonth_in_Quarter = 2 Then xC = "G"
If xMonth_in_Quarter = 3 Then xC = "I"
x = 2
Do While xWB.Sheets("Data_1").Cells(x, 1) <> ""
If xWB.Sheets("Data_1").Cells(x, 3) <> "" Then
xTab = Sheets("Data_1").Cells(x, 3)
xR_A = xWB.Sheets("Data_1").Cells(x, 6)
xR_C = xWB.Sheets("Data_1").Cells(x, 7)
y = 2
Do While yWB.Sheets(xTab).Cells(y, 2) <> "Class Variable"
y = y + 1
Loop
Sheets("Data Entry - USBFS").Range("J" & xR_A) = yWB.Sheets(xTab).Cells(y + 1, 2)
Sheets("Data Entry - USBFS").Range(xC & xR_A) = yWB.Sheets(xTab).Cells(y + 1, 4)
Sheets("Data Entry - USBFS").Range("J" & xR_C) = yWB.Sheets(xTab).Cells(y + 2, 2)
Sheets("Data Entry - USBFS").Range(xC & xR_C) = yWB.Sheets(xTab).Cells(y + 2, 4)
End If
x = x + 1
Loop
End Sub



Any help would be greatly appreciated!!

Thanks

[georgiboy]

From what I can see from a quick look:

'yWB' is the second workbook that the code is looking at

In the highlighted line of code it is looking for a sheet named the same as the values in column C of the Data_1 sheet inside the 'Example Data' workbook. So for example when you see that error it is because the code is looking for a sheet named 'CVXQ' inside the March spreadsheet but the only sheet in there is 'Sheet1'

Maybe there used to be more sheets in the March proforma spreadsheet - from looking at the code, the code thinks there should be more sheets named: CVXQ, CVXR, CVXC etc...
In those sheets should potentially be the value 'Class Variable' in column B as that is what it is looking fore before it increments 'y'

Hope this helps

[twmills]

From what I can see from a quick look:

'yWB' is the second workbook that the code is looking at

In the highlighted line of code it is looking for a sheet named the same as the values in column C of the Data_1 sheet inside the 'Example Data' workbook. So for example when you see that error it is because the code is looking for a sheet named 'CVXQ' inside the March spreadsheet but the only sheet in there is 'Sheet1'

Maybe there used to be more sheets in the March proforma spreadsheet - from looking at the code, the code thinks there should be more sheets named: CVXQ, CVXR, CVXC etc...
In those sheets should potentially be the value 'Class Variable' in column B as that is what it is looking fore before it increments 'y'

Hope this helps

Yes, that makes sense.

Thanks so much!