Referencing cell in another workbook

[DJhuffman]

First, I want to thank all the awesome people who have helped me so far. I've managed to streamline my tasks significantly with the VBA help that the people on this forum have provided. Thank you so much!

Now, my current bit of tinkering. I use a single file that has my macros programmed into it ("Macro storage file"). I open it, then I open a separate workbook that I run the macros in and update my data (we'll call it "520 Data"). I'd like to use a cell in Macro Storage File (B4) to generate a value in a 520 Data Worksheet (Test).

So far, this is what I cobbled together:

Sub Variable()


Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet


Set wkb = Excel.Workbooks("Macro storage file.xlsx")
Set wks = wkb.Worksheets("MAIN")


CM = wks.Range("B4")


Worksheets("Test").Range("B3").Value = CM


End Sub

However, attempting to run this generates a "Subscript out of range" error for the Set wkb line. I'm not certain if I'm going about this the wrong way or not, so any feedback would be appreciated.

 

[Fluff]

How about

Sub DJhuffman()
   Dim Wbk As Workbook
   Dim wks As Worksheet
   
   Set Wbk = Workbooks("520 Data.xlsx")
   Set wks = ThisWorkbook.Worksheets("MAIN")
   
   cM = wks.Range("B4")
   
   Wbk.Worksheets("Test").Range("B3").Value = cM
End Sub

 

[DJhuffman]

Ok. That seems to be getting somewhere, but then the code choked on the last line. Still, I was able to use what you gave me to build the following:

Sub DJhuffman()
   Dim wks As Worksheet

   Set wks = ThisWorkbook.Worksheets("MAIN")

   cM = wks.Range("B4")


ActiveWorkbook.Worksheets("Test").Range("B3").Value = cM


End Sub

I didn't know about the ThisWorkbook command, which lets me ignore the Wbk command altogether.
This seems to do the task I was hoping to accomplish. I'll need to test it a little more, but the initial results are very promising. Thank you very much!

 

[MARK858]

but then the code choked on the last line.

Are you sure that 520 Data.xlsx is the ActiveWorkbook when the code runs?

[COLOR="#FF0000"]ActiveWorkbook.[/COLOR]Worksheets("Test").Range("B3").Value = cM