Macro Help

[Craig]

I am a novice at macros and need a little help.

In Tab 1 I have a value in cell H35.

What I want to do is have the Macro go to Cell h38 in Tab 2 and have that
cell put a formula to reference cell H35 on Tab 1. Then in Cell I38:AD38 in
Tab 2 I want it to reference cell h38 of Tab 2.

I tried to record a Macro to do this and it keeps giving me errors. I have
tried switching between relative reference and not and I still have an
error. So obviously I am doing something wrongs. Anyone have a code that
will enable me to do this.

Thanks in advance.

 

[SeanC UK]

Hi Craig,

Try something like this:

Worksheets("Sheet2").Range("H38").Formula = "=Sheet1!$H$35"
Worksheets("Sheet2").Range(Cells(38, 9), Cells(38, 30)).Formula = "=$H$38"

Change the Sheet2 and Sheet1 to your tab names. I have simply referenced the
cells directly as your question requests. Obviously you can now amend the
formula to represent any worksheet function you need.

Hope this helps,

Sean.

 

[Craig]

I copied your code exactly and it gives me an error for the second line of
code:

Worksheets("Sensitivity").Range(Cells(38, 9), Cells(38, 30)).Formula =
"=$H$38"

Thie first line worked correctly, but for some reason it doesn't like this.
Any other ideas.

thanks!

 

[SeanC UK]

OK, I'm not sure why that's not working. Try one of these instead:

replace
Worksheets("Sensitivity").Range(Cells(38, 9), Cells(38, 30)).Formula =
"=$H$38"

with
Worksheets("Sensitivity").Range("I38:AD38").Formula = "=$H$38"

Or try this instead, so as to loop through each cell in your range:

Dim MyRange As Range
Worksheets("Sensitivity").Range("H38").Formula = "=Sheet1!$H$35"
For Each MyRange In Worksheets("Sensitivity").Range("I38:AD38")
MyRange.Formula = "=$H$38"
Next

(Change Sheet1 as necessary)

If you're still getting troubles then let me know the Excel version and the
error number/description.

Sean.

 

[Craig]

That worked. Thanks for the help!!!!!!

OK, I'm not sure why that's not working. Try one of these instead:

replace
Worksheets("Sensitivity").Range(Cells(38, 9), Cells(38, 30)).Formula =
"=$H$38"

with
Worksheets("Sensitivity").Range("I38:AD38").Formula = "=$H$38"

Or try this instead, so as to loop through each cell in your range:

Dim MyRange As Range
Worksheets("Sensitivity").Range("H38").Formula = "=Sheet1!$H$35"
For Each MyRange In Worksheets("Sensitivity").Range("I38:AD38")
MyRange.Formula = "=$H$38"
Next

(Change Sheet1 as necessary)

If you're still getting troubles then let me know the Excel version and
the
error number/description.

Sean.