CODEWITHSUNDEEP
excelvba
Mushiesmu
Mushiesmu

Reputation: 1

Userform to fiill multiple sheets with "with"

I am having trouble with my code. I need to update multiple sheets with one userform. However, my code keeps giving me error. Any help would be appreciated. 

Private Sub AddItem_Click()
    Dim r As Long
    Dim r1 As Long
    Dim Sheet1 As Worksheet
    Dim Sheet4 As Worksheet

    With Worksheets("Sheet1", "Sheet4")
        r = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        r1 = .Range("A" & .Rows.Count).End(xlUp).Row + 1
       .Range("A" & r) = Me.txtFN
       .Range("A" & r1) = Me.txtFN
       .Range("B" & r) = Me.txtLN

Upvotes: 0

Views: 124

Answers (2)

Zac
Zac

Reputation: 1944

I haven't tested this but try something like this

Option Explicit

Sub AddItem_Click()
    Dim oWS As Worksheet
    Dim iLRow As Long, iC As Long, iErr As Long
    Dim aSheets As Variant: aSheets = Array("Sheet1", "Sheet4")

    For iC = LBound(aSheets) To UBound(aSheets)
        Err.Clear
        iErr = 0
        On Error Resume Next
        Set oWS = ThisWorkbook.Worksheets(aSheets(iC))
        iErr = Err.Number
        On Error GoTo 0

        If iErr = 0 Then
            With oWS
                iLRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
                .Range("A" & iLRow) = "Test"
                .Range("B" & iLRow) = "Test2"
            End With
        End If
    Next
End Sub

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

The Worksheets property takes a single parameter, not a list of sheet names; you need to wrap the list of sheet names with an Array(...) call to feed it with more than a single sheet name.

With Worksheets(Array("Sheet1", "Sheet4"))

But doing that will only make the Worksheets call succeed - it still won't return the object you're expecting to work with.

A With block holds a reference to an object. In this case, the object class is Excel.Sheets, which is a collection of sheets.

A collection of worksheets doesn't have a Range property; you can't treat it as you would a single Worksheet.

Assuming you mean to apply that logic to all Worksheets objects in the collection, you need to loop through the worksheets in that collection.

Dim ws As Worksheet
For Each ws In Worksheets(Array("Sheet1", "Sheet4"))
    With ws
        r = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        ...
    End With
Next

Upvotes: 1

Related Questions