create a macro

[ganga]

I have a sheet 1 with all the info and formula and I want to use macro to
copy that sheet into sheet 2 and where the formula says sheet 1 i want to
change that to sheet 2 and so on for the other sheets.

thank you in advance.

 

[Gary''s Student]

Sub copySheet()
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Cells.Copy s2.Range("A1")
End Sub

 

[Gord Dibben]

If you have formulas on Sheet1 why are they referring to Sheet1?

If you delete any Sheet1 references, the formulas will copy as is with no
sheet reference.

If you have a reason for the Sheet1 reference then............

Delete all sheets except Sheet1 then run this macro.

Copies Sheet1 as many times as you designate in the InputBox.

Each Sheet's formulas will refer to their own sheet.

Sub SheetCopy()
Dim I As Long
On Error GoTo endit
Application.ScreenUpdating = False
shts = InputBox("How many times?")
For I = 2 To shts
ActiveSheet.Copy After:=ActiveSheet
With ActiveSheet
.Name = "Sheet" & I
End With
Next I
Application.ScreenUpdating = True
endit:
End Sub


Gord Dibben MS Excel MVP

 

[ganga]

it's not really working for me.. can you explain me more.. sorry i'm not
really good in macro

thank you

 

[Gord Dibben]

You had a couple of replies.

Which one isn't working for you?

The macro and instructions I posted work fine for me.

But please answer why you have formulas on Sheet1 that reference Sheet1


Gord Dibben MS Excel MVP

 

[ganga]

Sorry if i confused you. I don't have any formula in sheet 1. but in sheet 2
I have a formula that refer to sheet 1. so I need a macro ( by entering 1 key
or function) to copy that sheet into sheet 3 and so on and change the formula
where it says sheet 1 change to sheet 2 and so on..
I hope this time its clear.

for example:
sheet 1 A1 15
sheet 2 A1 sheet1!+5

By entering a Key or functions i need

sheet 3 A1 sheet2!+5

 

[Gord Dibben]

Copy/paste this UDF to a general module in your workbook.

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

Example of usage...................

Say you have 12 sheets, sheet1 through sheet12...........sheet names don't
matter.

In sheet1 you have 15 in A1

Select sheet2 and SHIFT + Click last sheet tab to group the sheets.

In active sheet A1 enter =prevsheet(A1)+5

Ungroup the sheets.



Gord