twmills
04-19-2022, 07:05 AM
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
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