Copy worksheet and create 60 sheets "manage formulas"

[Javi]

Hi All.

I have the below code that will allow me to copy the active worksheet and create 60 copies.

The source worksheet that I will be copying from is setup to not allow data entry on it all, all of the values are from a main data entry sheet.

The issue I am having is the below code will copy everything to the sheets as well as the formulas (identical to the source) with no issues. However, I need the formulas to drop down one row maintaining the columns. As a note: the column width, column height, Fonts, print area etc. all needs to remain the same as the source the only thing I would like to change on the worksheet copy is the formulas.

Otherwise it’s going to be a huge manual process after the sheets are created to adjust all of the formulas.



Any help or advice would be greatly appreciated!

Sub CopysheetMultipleTimes()

Dim i As Integer

Dim p As Integer

On Error GoTo out

i = InputBox("How many copies do you what?", "Making Copies")

p = 0

Do

ActiveSheet.Copy After:=Sheets(Sheets.Count)

p = p + 1

Loop Until p = i

Exit Sub

out:

MsgBox "copy was cancelled"

Application.ScreenUpdating = True

End Sub

 

[Javi]

I apologize for being impatient, just looking for some direction whether or not the above question is feasible within VBA. Or should I move on to the manual process?

 

[Javi]

My apologies, I posted this approach separately not knowing I should have posted this within the same thread.

I like to take a different approach at this as I know sometimes it's difficult for me to put in words what I'm trying to achieve.

Hi All

I have a workbook with 20 formulas (See below) all are referencing a different sheet (Sheet1 “MAIN”).
I need to create additional sheets with the same formula in each the same cell location. The one exception is the formula needs to reference the next row from the reference sheet (Sheet1 “MAIN”).

Thank you, any assistance would be greatly appreciated or even a different approach this is my third approach to this process.

The below formulas are located on (Sheet9) this sheet is completed.


(“C8”) =+Main!C10
(“A10”) =+Main!AK10
("K8") =+Main!J10
("C13") =+Main!C10
("C14") =+Main!F10
("C15") =+Main!AF10
("F15") =+Main!I10
("C17") =+Main!D10
("C24") =+Main!C10
("C25") =+Main!AF10
("C26") =+Main!D10
("C28") =+Main!E10
("F25") =+Main!I10
("I25") =+Main!J10
("C40") =+Main!C10
("C41") =+Main!AF10
("C42") =+Main!D10
("C44") =+Main!E10
("F41") =+Main!I10
("I41") =+Main!$J$10

Below is what I need be entered on (Sheet10). I will need to duplicate this process from (Sheet10 – Sheet59). (Sheet59 should eventually reference row 60)

(“C8”) =+Main!C11
(“A10”) =+Main!AK11
("K8") =+Main!J11
("C13") =+Main!C11
("C14") =+Main!F11
("C15") =+Main!AF11
("F15") =+Main!I11
("C17") =+Main!D11
("C24") =+Main!C11
("C25") =+Main!AF11
("C26") =+Main!D11
("C28") =+Main!E11
("F25") =+Main!I11
("I25") =+Main!J11
("C40") =+Main!C11
("C41") =+Main!AF11
("C42") =+Main!D11
("C44") =+Main!E11
("F41") =+Main!I11
("I41") =+Main!$J$11

 

[jolivanes]

You state to create 60 sheets but if you have 9 already and you go to Sheet59, you create less.
Anyway, see the code to change to whatever your heart desires.

This assumes that you have 9 Sheets in your workbook.
One of these sheets is called "Main" while the last sheet is called "Sheet9" (no double quotation marks)

Sub Maybe()
Dim i As Long
For i = 1 To 50    '<----- Change the 50 to whatever is needed
ThisWorkbook.Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count)
        With ActiveSheet
            .Name = "Sheet" & Sheets.Count
            .UsedRange.SpecialCells(xlCellTypeFormulas).Replace What:=Mid(.Name, 6, 10), Replacement:=Mid(.Name, 6, 10) + 1
        End With
Next i
End Sub

It would be smart to try it on a copy of your original workbook first. (As you should always do)

 

[Javi]

Thank you I appreciate your assistance. Do you think you could call out the source sheet in the code and not just the active sheet? ie:Sheet38

 

[jolivanes]

Re: "call out the source sheet"
May I ask why? The code does not mind.

With Sheets(Sheets.Count)

maybe
If you let us know the rationale maybe we can change the code accordingly.

 

[Javi]

It was using a random sheet as the source. I was trying to pointed to a specifics sheet as the source. I wasn't sure where the source she was located in your code.

 

[jolivanes]

What do you consider as "Source" sheet?
All the code from Post #4 does is copy the last (= leftmost sheet) sheet and change the formula.
It does this 50 times. Pretty simple.
If you're requirement is different, you have to explain what you want in a manner that we can build on. We don't know what your workbook looks like unless you tell us.

 

[Javi]

Thank you for responding. Sorry about the delayed reply down on the Gulf Coast dealing with this hurricane.

Since my original post many sheets have been added to the WB so using the left most sheet as the source for copy and became an issue/confusing. That's why I was hoping to be able to specifically identify the source sheet that the code would copy.

 

[Javi]

I was able to get the code to work perfectly (it duplicated the she furthest to the right) THANK YOU! I appreciate your expertise and assistance. So valuable to not have to go in and manually change all of the formulas thanks again.


Below is a code that duplicates sheets. However, you would enter the quantity of sheets in a message box instead of hard coding that requirement.

Could you please assist me in incorporating this message box into your code?

Sub CopysheetMultipleTimes()
    Dim i As Integer
    Dim p As Integer
    On Error GoTo out
    i = InputBox("How many copies do you what?", "Making Copies")
    p = 0
    Do
        ActiveSheet.Copy After:=Sheets(Sheets.Count)
        p = p + 1
    Loop Until p = i
Exit Sub
out:
    MsgBox "copy was cancelled"
    Application.ScreenUpdating = True
End Sub