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?

Hi,

Use the CopyTabN sub from this thread:
<http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.programming&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

--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"JDaywalt" wrote:

> 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?

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''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


"JDaywalt" wrote:

> 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?

Sub addshts()
For i = 1 To InputBox("Enter sheets desired") - 1
Sheets("Master").Copy After:=Sheets("Master")
Next i
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"JDaywalt" <(E-Mail Removed)> wrote in message
news:BA2E4EA9-91DB-4969-B55E-(E-Mail Removed)...
>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?