Copy Data from Input Sheet to Log Sheet in VBA Using Combobox-CodePudding

I have a laboratory workbook which houses collected laboratory data. The worksheet has an input page which includes the following Input page. The input page has two columns one for Influent values and one for effluent values.

On the Input page the Influent starts with B13 to lRow and Effluent starts with C13 to lRow. The Input page has a combobox called cbSheet so the user can select which facility log sheet to transfer this data to.

I have written a Macro that should copy and transpose Influent data from the Input sheet to the sheet selected in the combox and paste it after the last used row. Next the macro should copy the Effluent data and paste it directly below the influent data on the same sheet. I would like the macro to alternate copying influent transposing and pasting to the sheet selected in the combobox followed by the effluent. So you would have something like this on the log sheet:

Influent "DATA"   
Effluent "DATA"  
Influent "DATA"  
Effluent "DATA"

First off, I am getting an error trying to reference the cbSheet combobox. Variable Not Defined; Also, I only have the influent data selected and am not sure how to include the effluent so they properly alternate as new data is added every week. Could someone please help me set this up? Thank you so much for the help!

    Dim wb As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lRow1 As Long
    Dim lRow2 As Long
    Dim lRow3 As Long
    
    Set wb = ThisWorkbook
    Set ws1 = wb.Sheets(1)
    lRow1 = ws1.Cells(Rows.Count, 2).End(xlUp).Row
    lRow2 = ws1.Cells(Rows.Count, 3).End(xlUp).Row
    
    If ws1.Range("A8").Value <> "" Then
    Set ws2 = wb.Worksheets(cbSheet.Value)
    lRow3 = ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws1
    .Range("A13" & lRow1).Copy
    ws2.Range("A15" & lRow2).PasteSpecial xlPasteValues, Transpose:=True
    End With
    
    End If
    
End Sub

CodePudding user response：

EDIT: missed the Transpose bit...

Try this:

Sub Transfer()
    Dim wb As Workbook, ws2 As Worksheet, wsInput As Object 'not As Worksheet
    Dim cDest As Range, wsName
    
    Set wb = ThisWorkbook
    Set wsInput = wb.Sheets(1)
    
    wsName = wsInput.cbSheet.Value
    If Len(wsName) > 0 Then
        If wsInput.Range("A8").Value <> "" Then
            Set cDest = wb.Worksheets(wsName).Cells(Rows.Count, "A").End(xlUp).Offset(1)
            With wsInput.Range("B13:C" & wsInput.Cells(Rows.Count, "B").End(xlUp).Row)
                cDest.Resize(.Columns.Count, .Rows.Count).Value = _
                                     Application.Transpose(.Value)
            End With
        End If
    Else
        MsgBox "First select a destination worksheet from the drop-down list!", _
               vbExclamation, "No destination selected"
    End If
End Sub

You can't declare wsInput as Worksheet, becuase the "out of the box" Worksheet object model does not include a member named "cbSheet". By declaring it As Object the member gets resolved at run-time instead of compile-time.