workbook name referenced in a worksheet cell

[ben_sorensen]

The code that I have written works that all the workbooks are open (there are three) two of the files are forecasted demand and then there is a master that imports the data and digests it for managers. Everyday their are going to be new forecasts that need to be loaded.

I have written code to remove the variation of having people copy and paste data into the model and instead have written a macro that looks at the other file and pastes the values in the other sheet, because these files are going to change every day the file name changes by date everyday so what I am looking to do is write an equation that in order for the code to get the file name of the workbook it needs it references a cell in the master file, which is where the code will be activated.

Here is the part of code that I have written that keeps giving me an error:

Workbooks(ActiveSheet.Range("C12").Value).Sheets("Forecast Plan").Range("A1:DZ250").Copy

Does anybody have an idea of why I keep getting the subscript out of range error on this?

When I type in the name, as opposed to referencing the cell, it works just fine.

Any help would be appreciated!

Best

 

[MARK858]

What happens with the below if you change "master" to your master sheets name?

Workbooks(Trim(Sheets("master").Range("C12").Value)).Sheets("Forecast Plan").Range("A1:DZ250").Copy

 

[ben_sorensen]

What happens with the below if you change "master" to your master sheets name?

Workbooks(Trim(Sheets("master").Range("C12").Value)).Sheets("Forecast Plan").Range("A1:DZ250").Copy

It looks like this:

Workbooks(Trim(Sheets("General Manager Report").Range("C12").Value)).Sheets("Forecast Plan").Range("A1:DZ250").Copy

It is still popping me the code of 'subscript out of range'

Thanks for the reply!

 

[MARK858]

I created a workbook called ABCD EFGH created a sheet called Forecast Plan.
I then created a new workbook and named a sheet General Manager Report and in C12 put ABCD EFGH.
I then ran the code below and it filled Range("A1:DZ250") of sheet Forecast Plan on workbook ABCD EFGH with XXX.

As this is the case I can only say that you must either have a spelling error compared to the name of the workbook (or the workbook isn't open) or a strange non-printing character in C12 to get that error. What do you have in C12 exactly?

Sub ddd()
Workbooks(Trim(Sheets("General Manager Report").Range("C12").Value)).Sheets("Forecast Plan").Range("A1:DZ250").Value = "XXX"
End Sub

 

[ben_sorensen]

You were right! Totally missed that there was a space in front of everything in the cell, that is a totally easy thing that I should know to look for.

Either way the formula works perfect now! Thanks!

 

[ben_sorensen]

Now my big issue is the reports downloading in a different instance in excel...

Do you know how to make it so all file default to the same instance or the macro search ALL open files for the file name?

Thanks for the help1

Best