Copying sheet tab "x" number of times

[JDaywalt]

I have a sheet tab that is named "MASTER". I am trying to write a macro that
will copy this sheet tab multiple times based upon the value in cell A1 of a
sheet tab called "QTY". For example, if the value on the QTY tab is "4", the
"MASTER" tab will be copied 4 times---always placing the new tab at the "end"
of the sheet tab block. Can someone help with this?

 

[sebastienm]

Hi,

Use the CopyTabN sub from this thread:
<http://www.microsoft.com/office/com...ming&mid=5bc775be-d632-41a3-8306-03c5d6cd9f90>

and call it as:

Sub Test()
CopyTabN Thisworkbook.Worksheets("MASTER") _
, CLng(Thisworkbook.Worksheets("QTY").Range("A1").value)
End Sub

 

[Dale Fye]

Since I'm relatively new to the Excel object model, I usually start out by
creating a macro to do what I want (in this case, copy the Master worksheet
to the end of the workbook). Then I'll add the code I need around that.
Something like:

Public Sub CopySheet

Dim intLoop as integer

intLoop = 1
Do While intLoop <= ActiveWorkbook.Sheets("SheetName").Cells("A1")

'put your copy code in here
'you might want to add some code that changes the names of the
'copy by appending the intLoop value to the word "Copy"

intLoop = intLoop + 1
Loop

End Sub

HTH
Dale

 

[Don Guillett]

Sub addshts()
For i = 1 To InputBox("Enter sheets desired") - 1
Sheets("Master").Copy After:=Sheets("Master")
Next i
End Sub