PDA

View Full Version : Solved: copying multiple named ranges to a new table



lostin_space
09-01-2006, 06:10 AM
Hi all, i've got a bit of an issue going on with copying data from multiple named ranges across to a 'roll-up' style / plain table style worksheet.

Here's what i'm doing - to collect entered data, I've created a worksheet in a 'data entry form' style format for my users... (each of the 'data entry' fields, btw is a unique named range) I need to copy across the data from each of the named ranges to a 2nd sheet in my workbook, and present it in a simple table / plain text table...

Sample sheet attached...

can anyone point me in the direction of a KB answer for this, or give me an idea of how to do this??

:banghead:

TIA

Russ

lucas
09-01-2006, 11:25 AM
I didn't use your named ranges....here's one way:
Sub FillRollup()
With Sheets("Rollup").Columns(1).Rows(65536).End(xlUp)
.Offset(1, 0) = Sheet1.[C9]
.Offset(1, 1) = Sheet1.[I9]
.Offset(1, 2) = Sheet1.[C12]
.Offset(1, 3) = Sheet1.[I12]
.Offset(1, 4) = Sheet1.[C15]
.Offset(1, 5) = Sheet1.[I15]
.Offset(1, 6) = Sheet1.[D19]
.Offset(1, 7) = Sheet1.[E19]
.Offset(1, 8) = Sheet1.[F19]
.Offset(1, 9) = Sheet1.[J19]
.Offset(1, 10) = Sheet1.[K19]
.Offset(1, 11) = Sheet1.[L19]
.Offset(1, 12) = Sheet1.[C23]
End With
End Sub
But you have complicated the clearcontents process with your merged cells.....


With Sheet1
[C9].ClearContents
End With


will fail because of the merged cells......
exampe attached

lostin_space
09-04-2006, 03:11 AM
many thanks, i'll give it a go..

:thumb :clap: