Reference to a cell in a previous sheet

D

dolphinv4

Hi,

I created a macro and in the macro, I'd like to enter a
code whereby the cell, A1, will be equal to a cell, C3,
in a previous sheet. What is the code I have to put in
the Visual Basic Editor?

Thanks!
Val
 
M

mudraker

To enter a formula into a1 use

Sheets("sheet2").Range("a1").Value = "=Sheet1!c3"



or to enter the value of c3 into a1 use this one line of code

Sheets("sheet2").Range("a1").Value = Sheets("sheet1").Range("c3").Valu
 
G

Guest

Hi,

but I'd like to reference to a previous sheet because the
sheets increases every new month. So what do I change
the "Sheet1!" to?

Val
 
M

mudraker

How do you derive the sheet name and what format do you use for it?

How is the new sheet added? Manually or by macro? If by macro post you
sheet add code

Is The new sheet added after the last sheet or before the first shee
or in some other order
 
G

Guest

Hi,

the new sheet is added by recording a macro. and i made
some changes to the codes by telling it to copy from the
ActiveSheet and insert the new sheet. So the sheet name
is actually given by MSexcel itself.

The new sheet is coded to be inserted after the
ActiveSheet.

And I'd like the cell in the new sheet (C1) to be equals
to a particular cell (A1) in the ActiveSheet.

Thanks,
Val
 
D

David

And I'd like the cell in the new sheet (C1) to be equals
to a particular cell (A1) in the ActiveSheet.
Click to expand...

The new sheet becomes the active sheet when added. You can then grab a
value from the previous sheet with ActiveSheet.Previous

like: Range("C1") = ActiveSheet.Previous.Range("A1")
 
Top