copy formulas from sheet to sheet and tie back to previous

Re: copy formulas from sheet to sheet and tie back to previo

You could use code like:

Sub ChangeLinkedFormulas()

Dim OldSheetName As String, NewSheetName As String

If ActiveSheet.Index = 1 Then Exit Sub
OldSheetName = Sheets(1).Name
NewSheetName = Sheets(ActiveSheet.Index - 1).Name

Cells.Replace OldSheetName, NewSheetName & NewSheetNo, xlPart, xlByRows, True

End Sub

By running this code on each sheet it will replace all instances of "Sheet1" (or whatever the first sheet is named) with the name of the previous sheet. You'll have to be careful though - it will replace all instances of "Sheet1" even though you may not want it to. Also, it uses the index of the sheet to to determine what to change the formula to. I believe this index is order that the sheets were added to the workbok, NOT the order you currently have them in.

Re: copy formulas from sheet to sheet and tie back to previo

Have tried to get the code to work, it doesnt give me any errors, but it doesnt copy the previous sheet over the current sheet.

Re: copy formulas from sheet to sheet and tie back to previo

Sorry, I didn't realise you wanted to copy it too. Try this instead. I was wrong about the index property, it actually does refer to the order that the sheets are in on the tab bar. So, running this code should copy the second sheet on the taskbar (I'm assuming this is the first of these multiple sheets with the offending formulas, ie =Sheet1..etc) to the active sheet. It then changes all of the instances of the name of sheet1 to the name of the sheet to the left of the activesheet.
It would be best to get your sheets in the order you want before running.

Sub ChangeLinkedFormulas2()

Dim OldSheetName As String, NewSheetName As String

If ActiveSheet.Index = 1 Or ActiveSheet.Index = 2 Then _
MsgBox "Cannot perform on sheet " & ActiveSheet.Name: Exit Sub
OldSheetName = Sheets(1).Name
NewSheetName = Sheets(ActiveSheet.Index - 1).Name

Sheets(2).Cells.Copy ActiveSheet.Cells

Cells.Replace OldSheetName, NewSheetName & NewSheetNo, xlPart, xlByRows, True

End Sub

Re: copy formulas from sheet to sheet and tie back to previo

Thanks for the help, works great now!!

Re: copy formulas from sheet to sheet and tie back to previo

No Problem.

Re: copy formulas from sheet to sheet and tie back to previo

Just realised that the "& NewSheetNo" in the last code line shouldn't be there. It doesn't matter, it makes no difference to the code, but it may be confusing. It was left over from when I tried a different approach.

Re: copy formulas from sheet to sheet and tie back to previo

Just have one more question. Is there a way to make it copy the previous sheet instead of just the 2nd sheet in the tab order? I might end up adding a few things to new sheets, but dont wont it to always revert back to the 2nd sheet format, only the previous sheet. Thanks.

Re: copy formulas from sheet to sheet and tie back to previo

This should do it

Sub ChangeLinkedFormulas3()

Dim OldSheetName As String, NewSheetName As String

If ActiveSheet.Index = 1 Or ActiveSheet.Index = 2 Then _
MsgBox "Cannot perform on sheet " & ActiveSheet.Name: Exit Sub
OldSheetName = Sheets(ActiveSheet.Index - 2).Name
NewSheetName = Sheets(ActiveSheet.Index - 1).Name

Sheets(ActiveSheet.Index - 1).Cells.Copy ActiveSheet.Cells

Cells.Replace OldSheetName, NewSheetName, xlPart, xlByRows, True

End Sub

Re: copy formulas from sheet to sheet and tie back to previo

Works great Craig, thanks for the help