Struggling With Reference

[MikeG]

I always seem to struggle with what I think should be simple references.

I am in a workbook called "Test".

I also have open a workbook called "Operations Report" which has a sheet called "SoCal".

I have a macro command in Test that I want to use to copy a range from SoCal. I am trying the following but get an error:

Workbooks("Operations Report").Worksheets("SoCal").Range(Cells(1, 1), Cells(1, 80)).Copy

Can anyone help me on what I am doing wrong?

Also, if in the macro, I set a variable f="SoCal" then how can I use this so that rather than hard-coding Worksheets("SoCal"), I can use something like Worksheets(f)?

Thanks,

MikeG

 

[shg]

Unqualified, Cells refers to the active sheet. So, you could do this

With Workbooks("Operations Report").Worksheets("SoCal")
    Range(.Cells(1, 1), .Cells(1, 80)).Copy
End with

... or more simply:

Workbooks("Operations Report").Worksheets("SoCal").Range("A1:CB1").Copy

 

[MikeG]

Unqualified, Cells refers to the active sheet. So, you could do this

With Workbooks("Operations Report").Worksheets("SoCal")
    Range(.Cells(1, 1), .Cells(1, 80)).Copy
End with

... or more simply:

Workbooks("Operations Report").Worksheets("SoCal").Range("A1:CB1").Copy

Thanks shg! Where did you learn all this!

The first solution works best for me, because I will be using variables to define the range x and y's e.g. x1=2, y1=7 x2=50, y1=7 and then Range(.Cells( x1, y1), .Cells(x2, y2))

In your code, if I let f="Socal" then can I use this variable f in the references instead of the hard-coded "SoCal"?

MikeG

 

[shg]

Thanks shg!

You're welcome!

Where did you learn all this!

Experimenting.

if I let f="Socal" then can I use this variable f in the references instead of the hard-coded "SoCal"?

Experiment!

 

[MikeG]

You're welcome!

Experimenting.

Experiment!

Ha! Thanks. I will - although one reason this board is so good is we can get answers from experts so we don't have to re-invent the wheel!