Hi dflak,

Many thanks for your help. I like the concept but Im having some trouble. I
would really appreciate your help. Its most likely lack of experience and
probably didnt apply your instructions correctly.

Perhaps we could do a quick test. Lets say we have a simple workbook with
the following worksheets: €œMenu€�, €œWorksheet 1€� and €œWorksheet 2€�.

On "Menu", I have data in cells A1, B2 and C3. I named this range as
€œCopyPaste€�. The macro would grab the data from worksheet €œMenu€� and paste it
in €œworksheet 1€�. I want this macro to work discretely on €œworksheet 2€�, and
worksheets 3 and on.

When I run the code, I get a €œcompile error: Sub or function not defined€�.

Here is the code I created:

Option Base 1

Sub CopyPaste()
Dim NumCopies As Long, iCopies As Long
Dim CopyRanges() As String

NumCopies = Range("CopyPaste").Rows.Count
ReDim CopyRanges(NumCopies)

CopyRanges(1) = €œA1: A1€�
CopyRanges(2) = B2: B2 ""
€¦

For iCopies = 1 to NumCopies
CopyRanges(iCopies) = Range("CopyPaste").Cells(iCopies,1)
Sheets("Menu").Select
Range(CopyRanges(iCopies)).Select
Selection.Copy
Sheets("Worksheet 1").Select
Range(CopyRanges(iCopies)).Select
ActiveSheet.Paste

Next
€¦
End Sub


"dflak" wrote:

This is untested but you could set something up like:

Option Base 1

sub SetUpBase ()
Dim NumCopies as Long, iCopies as long
Dim CopyRanges() as String

NumCopies = 16
ReDim CopyRanges(NumCopies)

CopyRanges(1) = €œQ5:S5€�
CopyRanges(2) = O11:P12"
€¦

For iCopies = 1 to NumCopies

Sheets("Setup (Base)").Select
Range(CopyRanges(iCopies)).Select
Selection.Copy
Sheets("Worksheet 1").Select
Range(CopyRanges(iCopies)).Select
ActiveSheet.Paste

Next
€¦
End Sub

There is a reason I deliberately set up the CopyRanges as an undimensioned array intitally. First I didn't count how many copies you actually did. So the 16 may be wrong. You will have to change it to match the number of copies you make.

This makes the code a little more flexible in case you need to add, delete or change what you are copying.

The code can be made "expandable" - that is, instead of describing the ranges in the code, you could list them in a named dynamic range on the spreadsheet, and define them there. So you won't have to change code, you will only have to change the spreadsheet. Then NumCopies becomes NumCopies = Range("YourRangeName").Rows.Count.

To fill the array use:
For iCopies = 1 to NumCopies
CopyRanges(iCopies) = Range("YourRangeName").Cells(iCopies,1)
Next

Good Luck.

---
frmsrcurl: http://msgroups.net/microsoft.public...ple-worksheets
.