Taking reference of the sheet name (from another workbook) based on the inputbox

Frequent Contributor

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!

2 Replies


Does the code fail? If so, what is the problem exactly?

, now the code works perfectly. Apologies for the false alarm again. I think unknowingly i could have entered an additional character into the input box. Appreciate the response.