Hello everyone
my problem is this:
with a combobox I select an item which is the name of the sheet in a closed workbook.
I would like the name of the sheet to be performed to be assigned by the selection.
I have tried various solutions but as a result it is always:
"Subscript out of range"
As it does not identify the sheet name.
my problem is this:
with a combobox I select an item which is the name of the sheet in a closed workbook.
I would like the name of the sheet to be performed to be assigned by the selection.
I have tried various solutions but as a result it is always:
"Subscript out of range"
As it does not identify the sheet name.
VBA Code:
Dim oDataBook As Workbook
Private Sub CommandButton1_Click()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim wsName As String
Dim lCopyLastRow As Long
Dim lDest LastRow As Long
wsName = Me.Combobox1.Value
Set wsCopy = Workbooks("gigi_test.xlsm").Worksheets("tempData")
If wsName <> "" Then
Set wsDest = Workbooks(ThisWorkbook.Path & "/" & "orders.xlsm").Worksheets(wsName) ' <= HERE
End If
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
wsCopy.Range("A2:D" & lCopyLastRow).Copy wsDest.Range("A" & lDestLsstRow)
End Sub
Private Sub UserForm_Initialize()
Dim i As Long
Dim rng As Range
Application.ScreenUpdating = False
Set oDataBook = Workbooks.Open(ThisWorkbook.Path & "/" "orders.xlsm")
With oDataBook.Sheets("Sheet1")
Set rng = .Range(.Range("A1"). .Range("A" & Rows.Count).End(xlUp))
End With
ComboBox1.Clear
ComboBox1.List = rng.Value
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_Terminate()
oDataBook.Close
Set oDataBook = Nothing
End Sub