Concatenate Variable Name

[avd88]

Hi,

I have the following code where I'm entering information to a certain sheet depending on the sheet you are in.

    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:

    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.

 

[Fluff]

There's no need to write the code for each sheet, you can do it like

    Dim NxtRw As Long
    Dim aSheet As String
    
    aSheet = ActiveSheet.Name
  
   With Sheets(aSheet & "_")
       NxtRw = .Range("B" & Rows.Count).End(xlUp).Offset(1).Row
       
       .Cells(NxtRw, 2) = Me.LRdLabel.Caption
       .Cells(NxtRw, 3) = Sheets("RDs").Cells(2, 4).Value
       .Cells(NxtRw, 4) = "Lost"
       .Cells(NxtRw, 5) = Me.LPrimaryReasonComboBox.Value
       .Cells(NxtRw, 6) = Me.LSecondaryReasonComboBox.Value
       .Cells(NxtRw, 8) = Me.LYearComboBox.Value
       .Cells(NxtRw, 9) = Me.LPeriodComboBox.Value
       .Cells(NxtRw, 10) = Me.LImpactBox.Value
       .Cells(NxtRw, 11) = Me.LDJOHSComboBox.Value
       .Cells(NxtRw, 12) = Me.LAccessComboBox.Value
       .Cells(NxtRw, 13) = Me.LProgramSolComboBox.Value
       .Cells(NxtRw, 14) = Me.LDeliveryComboBox.Value
       .Cells(NxtRw, 15) = Me.LGroupBox.Value
       .Cells(NxtRw, 16) = Me.LNotesBox.Value
   End With