Add sheet and change cell reference

[Maheshp]

i have sheet1 which is link with other workbooks i want, when i add new sheet the cell address will increase by one
if cell address is A2 then when i add new sheet(copy of sheet1) in same cell it will reflect A3
i will appreciate any help on this, i need vb if possible provide formula also

sheet1 formula's


Cell("B6").value ='C:\Users\Desktop\F.Y 2010-11\[Book1.xls]Main'!$A$2Cell("B7").value='C:\Users\Desktop\F.Y 2010-11\[Book2.xls]Main'!$A$2Cell("B10").value='C:\Users\Desktop\F.Y 2010-11\[Book3.xls]Main'!$B$10Sheet 2Cell("B6").value ='C:\Users\Desktop\F.Y 2010-11\[Book1.xls]Main'!$A$3Cell("B7").value='C:\Users\Desktop\F.Y 2010-11\[Book2.xls]Main'!$A$3Cell("B10").value='C:\Users\Desktop\F.Y 2010-11\[Book3.xls]Main'!$B$11</PRE>

 

[Maheshp]

Hi
i find the solution , someone else find this helpful

Sub test()
Dim i, x, a As Long
Dim path1 As String
path1 = "=+'C:\Users\Desktop\F.Y 2010-11\[Book1.xls]Main'!"
With ActiveSheet
d = DateValue("01-" & .Name)
x = 3
a = x + 1
Cells(6, 3).Formula = path1 & "C" & x
For i = a To 6
ActiveSheet.Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(DateAdd("m", i, d), "mmm-yy")
Cells(6, 3).Formula = path1 & "C" & i
Next i
End With
End Sub

 

[VoG]

Glad you found a solution. For info it should be

Dim i As Long, x As Long, a As Long

 

[Maheshp]

Thank you so much for your reply Sir,
Any reason behind declaring variable as

Dim i As Long, x As Long, a As Long

instead of

Dim i, x, a As Long

in both the cases i,x & a will be as long, what is difference between both statements ?

 

[VoG]

in both the cases i,x & a will be as long, what is difference between both statements ?

No!

Dim i, x, a As Long

i is Variant

x is Variant

a is Long

 

[Maheshp]

Sir VoG

thank you so much for this information, i am in learning phases.