Hi,

This is my first post so go easy on me. I am trying to take spreadsheet data from two different sheets. These sheets are generated by a batch process dumping the data out of a sql server DB. I got the range to be dynamic and resize each time there is a new dump. This is how I did it for Range PMA03:

=OFFSET(PMA!$B$2,MATCH("PMA03",PMA!$C:$C,0)-2,0,COUNTIF(PMA!$C:$C,"PMA03"),1)

All of this is entered into the "Refers to:" box of the Name>Define interface.
The unusual thing here is that the column is made up of multiple ranges (PMA01 - PMA09). Each cell in column B is a string and the range size is based on the ajoining cell column C which contains the key linking it to another range on another sheet (CRP or sheet2). This part is working good.

The hard part is that I have to take the data string from 'sheet2'!A2 and put it into 'sheet1'!B14. Then take the data (cross referenced via the adjacent key fields from each sheet (2 & 3)) from 'sheet3'!C2 and paste it into 'sheet1'!C14. Then copy 'sheet3'!E2 and paste only the formating into 'sheet1'E14. This formating then needs to be copied right to all the rest of the cells in the row. Then a new row needs inserted.

I used a macro and I can get the first line of data to work using the following code. I need help with looping through the rest of the ranges and populating sheet1. From what I can gather I will need to have nested For loops that go through the range on sheet3 then iterate to the next row of the range on sheet 2. Then go through the next range on sheet3, etc...

In the end the sheet should look like:
Data1 DataA1 Format Format...
Data1 DataB1 Format Format...
Data2 DataA2 Format Format...
Data2 DataB2 Format Format...
Data2 DataC2 Format Format...
.
.
.


The Code so far:
Sub Macro3()
'
' Macro3 Macro
'
' Copies a CRP and pastes it into the template
' Then does the same for a PMA
' Then does the same for the format of first data cell
' Still needs to be encased in a loop to do all CRPs and PMAs
' PMA to CRP have a many to one relationship
'
' INSERT LOOPS HERE AND NEXT AT END
Application.ScreenUpdating = False
Sheets("UserTemplate").Select
Range("B14").Select
Sheets("CustomerRelatedProcesses").Select
Selection.Copy
Sheets("UserTemplate").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C14").Select
Sheets("PerformanceMeasureAreas").Select
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("UserTemplate").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("E14").Select
Sheets("PerformanceMeasureAreas").Select
Range("E2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("UserTemplate").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("E14:S14").Select
Application.CutCopyMode = False
Selection.FillRight
ActiveWindow.ScrollColumn = 1
Range("B15").Select
Selection.EntireRow.Insert
Range("D14").Select
Application.ScreenUpdating = True
End Sub

Sorry about the length, I like to be thorough.