Copy Named Range

[exwarrior]

I need guidance in creating a Macro that will copy named ranges from my "Template Sheet" and paste them a variable number of times within my "SpecTemplate" sheet. I will need to do this for many named ranges within my first sheet, and the number of times I paste the named ranges into my second sheet will vary. I currently have 2 named ranges to work with for this example: Actuator_NoMod , Flame_Arrestor. Thanks a million in advance.

[Bob Phillips]

How do you know how many times to copy.

[exwarrior]

It would have to be a variable. I run a query on some data that tells me how many time each template is needed so it would definately vary. I was thinking of allowing this value to be input with an inputbox?

[Bob Phillips]

Public Sub CopyData()
Dim NumCopies As Long
Dim nme As Name
Dim NextRow As Long
Dim i As Long

With Worksheets("Template Sheet")
NumCopies = InputBox("How many copies?")
If NumCopies <> 0 Then

NextRow = 1
On Error Resume Next
For Each nme In ActiveWorkbook.Names
.Range(nme.Name).Copy Worksheets("SpecTemplate").Range("A" & NextRow)
For i = 1 To NumCopies
.Range(nme.Name).Copy Worksheets("SpecTemplate").Range("A" & NextRow)
NextRow = NextRow + .Range(nme.Name).Rows.Count
Next i
Next nme
End If
End With

End Sub

[exwarrior]

I couldnt figure yours out but I made some progress out last night.


I can get it to copy and paste 5 times in 1 location but it does not move down the sheet while pastinge 5 times, here is my code: here are the values that i give the input boxes: 18, Actuator_NoMod, 5


Sub CopyRangeAlot()
Dim iCount, iAmount, iAmountDone As Integer
irow = ActiveCell.Row
icol = ActiveCell.Column

iCount = Application.InputBox("row count")
myRange = Application.InputBox("enter range name")
iAmount = Application.InputBox("how many times")
iAmountDone = 0

Do While iAmountDone <> iAmount
Range(myRange).Copy
Sheets("Template").Select
ActiveCell.PasteSpecial (xlPasteValues)
iAmountDone = iAmountDone + 1
ActiveCell.Offset = iCount


Loop

'iAmountDone = iAmountDone + 1
ActiveCell.Offset = iCount + 1

End Sub

[jwise]

Try this for your loop:
Do While iAmountDone <> iAmount
Range(myRange).Copy
Sheets("Template").Select
ActiveCell.PasteSpecial (xlPasteValues)
iAmountDone = iAmountDone + 1
ActiveCell.Offset = iCount + 1
Loop



Good luck