Hi,
I have the following code where I'm entering information to a certain sheet depending on the sheet you are in.
There are 2 more if statements for 2 more sheets.
Trying to do this all on a single step instead of having If statements so trying to use the aSheet variable to do something like this:
I'm pretty sure the error is when trying to concatenate the nextrow integer variables.
Anyone know how I can concatenate those variables so I can use the code I'm trying above.
I have the following code where I'm entering information to a certain sheet depending on the sheet you are in.
VBA Code:
Dim IDNnextrow As Integer
Dim BTnextrow As Integer
Dim NPInextrow As Integer
Dim ENDnextrow As Integer
Dim aSheet As String
IDNnextrow = WorksheetFunction.CountA(Sheets("IDN_").Range("B:B"))
BTnextrow = WorksheetFunction.CountA(Sheets("BT_").Range("B:B"))
NPInextrow = WorksheetFunction.CountA(Sheets("NPI_").Range("B:B"))
ENDnextrow = WorksheetFunction.CountA(Sheets("END_").Range("B:B"))
aSheet = ActiveSheet.Name
'COPY FORM VALUES TO SHEET'
If ActiveSheet.Name = "IDN" Then
Sheets("IDN_").Cells(IDNnextrow + 1, 2) = Me.LRdLabel.Caption
Sheets("IDN_").Cells(IDNnextrow + 1, 3) = Sheets("RDs").Cells(2, 4).Value
Sheets("IDN_").Cells(IDNnextrow + 1, 4) = "Lost"
Sheets("IDN_").Cells(IDNnextrow + 1, 5) = Me.LPrimaryReasonComboBox.Value
Sheets("IDN_").Cells(IDNnextrow + 1, 6) = Me.LSecondaryReasonComboBox.Value
Sheets("IDN_").Cells(IDNnextrow + 1, 8) = Me.LYearComboBox.Value
Sheets("IDN_").Cells(IDNnextrow + 1, 9) = Me.LPeriodComboBox.Value
Sheets("IDN_").Cells(IDNnextrow + 1, 10) = Me.LImpactBox.Value
Sheets("IDN_").Cells(IDNnextrow + 1, 11) = Me.LDJOHSComboBox.Value
Sheets("IDN_").Cells(IDNnextrow + 1, 12) = Me.LAccessComboBox.Value
Sheets("IDN_").Cells(IDNnextrow + 1, 13) = Me.LProgramSolComboBox.Value
Sheets("IDN_").Cells(IDNnextrow + 1, 14) = Me.LDeliveryComboBox.Value
Sheets("IDN_").Cells(IDNnextrow + 1, 15) = Me.LGroupBox.Value
Sheets("IDN_").Cells(IDNnextrow + 1, 16) = Me.LNotesBox.Value
ElseIf ActiveSheet.Name = "BT" Then
Sheets("BT_").Cells(BTnextrow + 1, 2) = Me.LRdLabel.Caption
Sheets("BT_").Cells(BTnextrow + 1, 3) = Sheets("RDs").Cells(2, 4).Value
Sheets("BT_").Cells(BTnextrow + 1, 4) = "Lost"
Sheets("BT_").Cells(BTnextrow + 1, 5) = Me.LPrimaryReasonComboBox.Value
Sheets("BT_").Cells(BTnextrow + 1, 6) = Me.LSecondaryReasonComboBox.Value
Sheets("BT_").Cells(BTnextrow + 1, 8) = Me.LYearComboBox.Value
Sheets("BT_").Cells(BTnextrow + 1, 9) = Me.LPeriodComboBox.Value
Sheets("BT_").Cells(BTnextrow + 1, 10) = Me.LImpactBox.Value
Sheets("BT_").Cells(BTnextrow + 1, 11) = Me.LDJOHSComboBox.Value
Sheets("BT_").Cells(BTnextrow + 1, 12) = Me.LAccessComboBox.Value
Sheets("BT_").Cells(BTnextrow + 1, 13) = Me.LProgramSolComboBox.Value
Sheets("BT_").Cells(BTnextrow + 1, 14) = Me.LDeliveryComboBox.Value
Sheets("BT_").Cells(BTnextrow + 1, 15) = Me.LGroupBox.Value
Sheets("BT_").Cells(BTnextrow + 1, 16) = Me.LNotesBox.Value
There are 2 more if statements for 2 more sheets.
Trying to do this all on a single step instead of having If statements so trying to use the aSheet variable to do something like this:
Code:
Sheets(aSheet & "_").Cells(aSheet & "nextrow" + 1, 2) = Me.LRdLabel.Caption
I'm pretty sure the error is when trying to concatenate the nextrow integer variables.
Anyone know how I can concatenate those variables so I can use the code I'm trying above.