I am using following VBA Code to copy data from a closed excel sheet. the name of sheet changes as per the date. so I assign the path and file name from the excel cell
B1. Now I am stuck at one place only. I want to assign the work sheet name given in another cell
Sub GetDataClosedBook() Dim scr As Workbook Dim FromPath1 As String 'location of the file and data to copy' Application.ScreenUpdating = False FromPath1 = Sheets("Status").Range("B1") Set scr = Workbooks.Open(FromPath1) ThisWorkbook.Activate Worksheets("Today_BC").Range("B1:P40000").Formula = scr.Worksheets("12SEP2018").Range("A1:O40000").Formula Workbooks(2).Close Application.ScreenUpdating = True ActiveWorkbook.Save End Sub
("12Sep2018") keeps changing, I am manually doing right now but I want to assign from cell in worksheet. How can I do it?