Pasting a range into a sheet

[Zain]

I'm writing a macro that will create a large number of worksheets. I have one master worksheet with some data on it and I want to copy a range from the master to each of the new sheets. I'm thinking the following:

Dim myRange As Range

myRange = Sheets("Master").Range("A5:A100")

For k = 1 to 100
Set myWS = Worksheets.Add
myWSName = Sheets("Pivot").Cells(4, Col).Value
myWS.Name = myWSName
myWS.Range("A5:A100") = myRange
Next k

Wil this work? Is there a faster way to do it? Thanks.

 

[RoryA]

I think your code needs to be this (changes in bold):

Dim myRange As Range
 
Set myRange = Sheets("Master").Range("A5:A100")
 
For k = 1 to 100
Set myWS = Worksheets.Add
myWSName = Sheets("Pivot").Cells(4, k).Value
myWS.Name = myWSName
myWS.Range("A5:A100") = myRange
Next k

though if you are just copying values across, I would use:

Dim varData
varData = Sheets("Master").Range("A5:A100").Value
application.screenupdating = false
For k = 1 to 100
Set myWS = Worksheets.Add
myWSName = Sheets("Pivot").Cells(4, k).Value
myWS.Name = myWSName
myWS.Range("A5:A100").Value = varData
Next k
application.screenupdating = true

 

[GlennUK]

Please describe exactly what you want. In English, with data examples if possible?

Is this a pivottable by any chance? Have you considered the Show Pages pivot command?