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 B2.

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)


Worksheets("Today_BC").Range("B1:P40000").Formula = scr.Worksheets("12SEP2018").Range("A1:O40000").Formula

Application.ScreenUpdating = True

End Sub

Now the ("12Sep2018") keeps changing, I am manually doing right now but I want to assign from cell in worksheet. How can I do it?

  • Replace scr.Worksheets("12SEP2018")... with scr.Worksheets(Sheets("Status").Range("B2").Value)... – Akina Sep 17 '18 at 10:02
  • It stopped at Set scr = Workbooks.Open(FromPath1) only... used as -----** Worksheets("Today_BC").Range("B1:P40000").Formula = scr.Worksheets(Sheets("Status").Range("B2").Value).Range("A1:O40000").Formula** – Jaldeep Vaishnav Sep 17 '18 at 10:12
  • Thnx Akina, Its working now , it was error from my side on reference cell.. thans a lot on prompt reply also. I had tried the same but not knowing to put ".value " as i am not good at VBA. – Jaldeep Vaishnav Sep 17 '18 at 10:35
  • @Akina if you post your solution as an answer then op would be able to accept it as the answer – ChipsLetten Sep 20 '18 at 7:22

I want to assign the work sheet name given in another cell B2

If so you must replace the string literal of the worksheet name "12SEP2018" with the value of a cell where the worksheet name you need is stored. i.e.

Worksheets("Today_BC").Range("B1:P40000").Formula = _

must be replaced with

Worksheets("Today_BC").Range("B1:P40000").Formula = _

Or store the destination worksheet name in a variable:

StoreTo = Sheets("Status").Range("B2").Value
Worksheets("Today_BC").Range("B1:P40000").Formula = _

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.