Forum Discussion
hrh_dash
Aug 06, 2022Iron Contributor
Taking reference of the sheet name (from another workbook) based on the inputbox
How do i reference my inputbox as part of the worksheet name of the destination workbook?
Below is the code..
Sub copyandpasteTCA()
Dim ws As Worksheet
Dim Destwb As Workbook
Dim Destws As Worksheet
Dim wslastRow As Long
Dim DestlastRow As Long
Dim shtno As String
Set ws = ThisWorkbook.Sheets("TCA")
shtno = InputBox("Please indicate Sheet number To paste data into:")
Set Destwb = Workbooks.Open("C:\Users\hrhquek\Desktop\DEBT RECOVERY\testing.xlsx")
Set Destws = Destwb.Sheets(shtno) '<--- how should i code in a way which the sheet reference is taken from the inputbox(name of the sheet)?
wslastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).EntireRow.Row
DestlastRow = Destws.Cells(Destws.Rows.Count, "B").End(xlUp).Offset(1).Row
End Sub
appreciate the help in advance!