Forum Discussion

hrh_dash's avatar
hrh_dash
Iron Contributor
Aug 06, 2022

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!

2 Replies

    • hrh_dash's avatar
      hrh_dash
      Iron Contributor
      , 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.

Resources