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.

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.

--
(please remember to click yes if replies you receive are helpful to you)


"Craig" wrote:

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.

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" wrote in message
...
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.

--
(please remember to click yes if replies you receive are helpful to you)


"Craig" wrote:

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.

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").Formul a = "=$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.



--
(please remember to click yes if replies you receive are helpful to you)


"Craig" wrote:

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" wrote in message
...
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.

--
(please remember to click yes if replies you receive are helpful to you)


"Craig" wrote:

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.

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


"SeanC UK" wrote in message
...
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").Formul a = "=$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.



--
(please remember to click yes if replies you receive are helpful to you)


"Craig" wrote:

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" wrote in message
...
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.

--
(please remember to click yes if replies you receive are helpful to
you)


"Craig" wrote:

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.