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

 

[jolivanes]

Like so?

Sub Maybe()
Dim a As String, i As Long
a = ActiveSheet.Name
Application.ScreenUpdating = False
For i = 1 To InputBox("How many copies do you what?", "Making Copies")  
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
Sheets(a).Select
Application.ScreenUpdating = True
End Sub

 

[Javi]

Thanks again!!! Worked perfectly...Like a charm!!!

 

[jolivanes]

Thank you for letting us know.
Good luck and stay safe.
All the best with the problems at the coast.

 

[Javi]

I would like to try and understand and learn more about your code could you please break down this code for me and let me know what it is doing the next time I'll give it a go.

.UsedRange.SpecialCells(xlCellTypeFormulas).Replace What:=Mid(.Name, 6, 10), Replacement:=Mid(.Name, 6, 10) + 1

 

[jolivanes]

Re:

UsedRange.SpecialCells(xlCellTypeFormulas).Replace What:=Mid(.Name, 6, 10), Replacement:=Mid(.Name, 6, 10) + 1

UsedRange should speak for itself. It is a lazy way of declaring the range that is being used but it can have pitfalls.
If, like unfortunately so many people do, have formulas from row 1 to row 5000 for 20 columns and you actually only
need to work on 15 rows x 8 columns, UsedRange will work on 5000 rows x 20 columns.
So you are way better with a proper range that is used.

SpecialCells again speaks for itself. Whatever needs to be done, it'll do it on the SpecialCells only.
In this case the cells in question are the cells with a formula.
I assume it is easy to understand that that can safe a lot of time.
If you would have a UsedRange like mentioned above, looping through every cell will be 100,000 cells.
If, in the same UsedRange, you would have 10 cells with a formula, it would only loop through the 10 cells.

The Replace is again that it replaces what you tell it to with another value that you give.
In our case, the value to be replaced is from character 6 on in the SheetName for 10 characters.
The 10 can be left out because a blank statement there will use the remainder of the characters.
If you would have a SheetName like "Sheet3452" (without double quotation marks), the result would be 3452
The replacement gets the same value but it adds 1 to it.
You have to be carefull with that also because sheet names are a string and not a number.
A better approach would have been

.UsedRange.SpecialCells(xlCellTypeFormulas).Replace What:=CDbl(Mid(.Name, 6, 10)), Replacement:=CDbl(Mid(.Name, 6, 10)) + 1

where the "CDbl" converts a convertable numerical string to a number value. When calculating, you would need numerical values, not strings.
CStr does the opposite but not often needed.

The best thing to do in my opinion is to experiment.
If you open a new workbook which would have all empty sheets and run this macro

Sub AAAAA()
    Sheets("Sheet1").Range("A1:O5000").Formula = "=IF(RC[19]="""","""",10)"
End Sub

You will see just so called empty cells. They are not empty but they don't show anything.
Now run this code.

Sub AAAAB()
    Sheets("Sheet1").UsedRange.Select
End Sub

You'll see that it selects a large range.

If you go to the next sheet (Sheet2) and run this code you'll see what looks like empty cells in C5 - C10.
However, they have formulas that calculate to nothing ("")

Sub AAAAC()
    With Sheets("Sheet2").Range("A1:O5000")
        .Formula = "=ROW()"
        .Value = .Value
        .Range("C5:C10").Formula = "=IF(RC[19]="""","""",10)"
    End With
End Sub

Now run this code and notice the difference with the code from AAAAB

Sub AAAAD()
    Sheets("Sheet2").UsedRange.SpecialCells(xlCellTypeFormulas).Select
End Sub

Longwinded but I hope it explains what you wanted to know.
Good luck

 

[Javi]

Finally back at the computer have been dealing with hurricane Sally and loss of power.

Thank you for sharing your knowledge. I will for sure next time give it a go.