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

user_15457651 0
Viewed 73

I have a workbook with laboratory data.

An input page has two columns one for Influent values and one for Effluent values.
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 combobox 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.

There should be something like this on the log sheet:

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

I get an error trying to reference the cbSheet combobox.

Variable Not Defined

Also, I only have the influent data selected.
How do I include the effluent so they alternate as new data is added every week?

    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
vba excel
1 Answers

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, rngDest, v
    
    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)
                v = Application.Transpose(.Value)
                With cDest.Resize(.Columns.Count, .Rows.Count)
                    .Value = v
                    .Interior.Color = vbYellow
                    .Borders.LineStyle = xlContinuous
                    .Borders.Weight = xlThin
                End With
            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.

user_4788840
Related