Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Several named Range in many sheets to a summary sheet
Dear Experts:
I have defined the dynamic range using "offset" to overcome the number of rows which is variable. From the summary sheet I have defined the name of the range such as "dbase1", "dbase2", etc... of each sheet. My question is how can we copy this name range to be combined to the summary sheet. the structures( number of columns)of all sheets are all the same. I have thought of starting copying dbase1 to A9, then using End and xldown. But could you please explain what macro can we use to handle this task. Many thanks, Frank |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Several named Range in many sheets to a summary sheet
You could try something like this:
Sub Test() Dim Name As Name Dim aWS As Worksheet Set aWS = ActiveSheet For Each Name In ActiveWorkbook.Names lrow = aWS.Cells(aWS.Rows.Count, 1).End(xlUp).Row For Each r In Range(Name) aWS.Cells(lrow, 1) = r lrow = lrow + 1 Next r Next Name End Sub "Frank Situmorang" wrote: Dear Experts: I have defined the dynamic range using "offset" to overcome the number of rows which is variable. From the summary sheet I have defined the name of the range such as "dbase1", "dbase2", etc... of each sheet. My question is how can we copy this name range to be combined to the summary sheet. the structures( number of columns)of all sheets are all the same. I have thought of starting copying dbase1 to A9, then using End and xldown. But could you please explain what macro can we use to handle this task. Many thanks, Frank |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Several named Range in many sheets to a summary sheet
Barb, It seems this will work for me, if you could give me more explanation.
Waht is aWS stands for is that the name of my summary sheet?, the name of my summary sheet to which I will combine the named range is "SummaryWBLA". Name in the brace is the name of my range? Many thanks, Frank "Barb Reinhardt" wrote: You could try something like this: Sub Test() Dim Name As Name Dim aWS As Worksheet Set aWS = ActiveSheet For Each Name In ActiveWorkbook.Names lrow = aWS.Cells(aWS.Rows.Count, 1).End(xlUp).Row For Each r In Range(Name) aWS.Cells(lrow, 1) = r lrow = lrow + 1 Next r Next Name End Sub "Frank Situmorang" wrote: Dear Experts: I have defined the dynamic range using "offset" to overcome the number of rows which is variable. From the summary sheet I have defined the name of the range such as "dbase1", "dbase2", etc... of each sheet. My question is how can we copy this name range to be combined to the summary sheet. the structures( number of columns)of all sheets are all the same. I have thought of starting copying dbase1 to A9, then using End and xldown. But could you please explain what macro can we use to handle this task. Many thanks, Frank |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Several named Range in many sheets to a summary sheet
hi,
try somethink like this... Sub MacCopyRanges() Dim x As Object For Each x In ActiveWorkbook.Names Range(x).Copy Sheets("sheet1").Select Range("a65000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll Next x End Sub you may have to adjust to fit your stuff. Regards FSt1 "Frank Situmorang" wrote: Dear Experts: I have defined the dynamic range using "offset" to overcome the number of rows which is variable. From the summary sheet I have defined the name of the range such as "dbase1", "dbase2", etc... of each sheet. My question is how can we copy this name range to be combined to the summary sheet. the structures( number of columns)of all sheets are all the same. I have thought of starting copying dbase1 to A9, then using End and xldown. But could you please explain what macro can we use to handle this task. Many thanks, Frank |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Several named Range in many sheets to a summary sheet
Hello:
I have tried below, but it stuck in the first row of VBA as follows: Sub AutoShape1_Click() ' ' AutoShape1_Click Macro ' Macro recorded 5/8/2007 by Frank ' ' Sub MacCopyRanges() Dim dbaseA00 As Object Dim dbaseE00 As Object For Each dbaseA00 In ActiveWorkbook.Names Range(dbaseA00).Copy Sheets("Conso").Select Range("a65000").End(xlUp).Offset(9, 0).PasteSpecial xlPasteAll Next dbaseE00 End Sub "FSt1" wrote: hi, try somethink like this... Sub MacCopyRanges() Dim x As Object For Each x In ActiveWorkbook.Names Range(x).Copy Sheets("sheet1").Select Range("a65000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll Next x End Sub you may have to adjust to fit your stuff. Regards FSt1 "Frank Situmorang" wrote: Dear Experts: I have defined the dynamic range using "offset" to overcome the number of rows which is variable. From the summary sheet I have defined the name of the range such as "dbase1", "dbase2", etc... of each sheet. My question is how can we copy this name range to be combined to the summary sheet. the structures( number of columns)of all sheets are all the same. I have thought of starting copying dbase1 to A9, then using End and xldown. But could you please explain what macro can we use to handle this task. Many thanks, Frank |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Several named Range in many sheets to a summary sheet
hi frank,
I use 'x' as the object designator to make it generic. the named range is the object. in my code i used for each x .....next x. in your code you use for each dbaceA00....next DBaseE00. you should have got an error: invalid next control variable reference variables should be generic. change your code back to x and see if that helps. regards FSt1 "Frank Situmorang" wrote: Hello: I have tried below, but it stuck in the first row of VBA as follows: Sub AutoShape1_Click() ' ' AutoShape1_Click Macro ' Macro recorded 5/8/2007 by Frank ' ' Sub MacCopyRanges() Dim dbaseA00 As Object Dim dbaseE00 As Object For Each dbaseA00 In ActiveWorkbook.Names Range(dbaseA00).Copy Sheets("Conso").Select Range("a65000").End(xlUp).Offset(9, 0).PasteSpecial xlPasteAll Next dbaseE00 End Sub "FSt1" wrote: hi, try somethink like this... Sub MacCopyRanges() Dim x As Object For Each x In ActiveWorkbook.Names Range(x).Copy Sheets("sheet1").Select Range("a65000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll Next x End Sub you may have to adjust to fit your stuff. Regards FSt1 "Frank Situmorang" wrote: Dear Experts: I have defined the dynamic range using "offset" to overcome the number of rows which is variable. From the summary sheet I have defined the name of the range such as "dbase1", "dbase2", etc... of each sheet. My question is how can we copy this name range to be combined to the summary sheet. the structures( number of columns)of all sheets are all the same. I have thought of starting copying dbase1 to A9, then using End and xldown. But could you please explain what macro can we use to handle this task. Many thanks, Frank |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Several named Range in many sheets to a summary sheet
also...
i just noticed your offset(9,0).... this will paste your dbs 9 rows apart. I used the for next loop because i thought you had more than 2 dbs. if you only have 2 dbs then we don't need the loop. try and post back if you have problems. regards FSt1 "Frank Situmorang" wrote: Hello: I have tried below, but it stuck in the first row of VBA as follows: Sub AutoShape1_Click() ' ' AutoShape1_Click Macro ' Macro recorded 5/8/2007 by Frank ' ' Sub MacCopyRanges() Dim dbaseA00 As Object Dim dbaseE00 As Object For Each dbaseA00 In ActiveWorkbook.Names Range(dbaseA00).Copy Sheets("Conso").Select Range("a65000").End(xlUp).Offset(9, 0).PasteSpecial xlPasteAll Next dbaseE00 End Sub "FSt1" wrote: hi, try somethink like this... Sub MacCopyRanges() Dim x As Object For Each x In ActiveWorkbook.Names Range(x).Copy Sheets("sheet1").Select Range("a65000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll Next x End Sub you may have to adjust to fit your stuff. Regards FSt1 "Frank Situmorang" wrote: Dear Experts: I have defined the dynamic range using "offset" to overcome the number of rows which is variable. From the summary sheet I have defined the name of the range such as "dbase1", "dbase2", etc... of each sheet. My question is how can we copy this name range to be combined to the summary sheet. the structures( number of columns)of all sheets are all the same. I have thought of starting copying dbase1 to A9, then using End and xldown. But could you please explain what macro can we use to handle this task. Many thanks, Frank |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Several named Range in many sheets to a summary sheet
You are right that I have more than 2 dbases, what I wanted to do is to start
pasting from row A9, is it OK? Frank "FSt1" wrote: also... i just noticed your offset(9,0).... this will paste your dbs 9 rows apart. I used the for next loop because i thought you had more than 2 dbs. if you only have 2 dbs then we don't need the loop. try and post back if you have problems. regards FSt1 "Frank Situmorang" wrote: Hello: I have tried below, but it stuck in the first row of VBA as follows: Sub AutoShape1_Click() ' ' AutoShape1_Click Macro ' Macro recorded 5/8/2007 by Frank ' ' Sub MacCopyRanges() Dim dbaseA00 As Object Dim dbaseE00 As Object For Each dbaseA00 In ActiveWorkbook.Names Range(dbaseA00).Copy Sheets("Conso").Select Range("a65000").End(xlUp).Offset(9, 0).PasteSpecial xlPasteAll Next dbaseE00 End Sub "FSt1" wrote: hi, try somethink like this... Sub MacCopyRanges() Dim x As Object For Each x In ActiveWorkbook.Names Range(x).Copy Sheets("sheet1").Select Range("a65000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll Next x End Sub you may have to adjust to fit your stuff. Regards FSt1 "Frank Situmorang" wrote: Dear Experts: I have defined the dynamic range using "offset" to overcome the number of rows which is variable. From the summary sheet I have defined the name of the range such as "dbase1", "dbase2", etc... of each sheet. My question is how can we copy this name range to be combined to the summary sheet. the structures( number of columns)of all sheets are all the same. I have thought of starting copying dbase1 to A9, then using End and xldown. But could you please explain what macro can we use to handle this task. Many thanks, Frank |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Several named Range in many sheets to a summary sheet
Hello Goodman: I appreciate your help, only one more step to go, all your
suggestion is very helpful this is my Macro and it stuck in Range(x) Copy, maybe when there is no more object, it stuck, what statement shall we add. Thanks to God that made other people in the advanced country like you can help us under developping country Indonesia. Again Sir, please help us Sub AutoShape7_Click() ' ' AutoShape7_Click Macro ' Macro recorded 5/10/2007 by Frank ' ' ActiveWindow.SmallScroll ToRight:=-5 Range("A9:Y1714").Select Selection.ClearContents Dim x As Object For Each x In ActiveWorkbook.Names Range(x).Copy Sheets("SUMMMARYWBLA").Select Range("a65000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll Next x End Sub "FSt1" wrote: also... i just noticed your offset(9,0).... this will paste your dbs 9 rows apart. I used the for next loop because i thought you had more than 2 dbs. if you only have 2 dbs then we don't need the loop. try and post back if you have problems. regards FSt1 "Frank Situmorang" wrote: Hello: I have tried below, but it stuck in the first row of VBA as follows: Sub AutoShape1_Click() ' ' AutoShape1_Click Macro ' Macro recorded 5/8/2007 by Frank ' ' Sub MacCopyRanges() Dim dbaseA00 As Object Dim dbaseE00 As Object For Each dbaseA00 In ActiveWorkbook.Names Range(dbaseA00).Copy Sheets("Conso").Select Range("a65000").End(xlUp).Offset(9, 0).PasteSpecial xlPasteAll Next dbaseE00 End Sub "FSt1" wrote: hi, try somethink like this... Sub MacCopyRanges() Dim x As Object For Each x In ActiveWorkbook.Names Range(x).Copy Sheets("sheet1").Select Range("a65000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll Next x End Sub you may have to adjust to fit your stuff. Regards FSt1 "Frank Situmorang" wrote: Dear Experts: I have defined the dynamic range using "offset" to overcome the number of rows which is variable. From the summary sheet I have defined the name of the range such as "dbase1", "dbase2", etc... of each sheet. My question is how can we copy this name range to be combined to the summary sheet. the structures( number of columns)of all sheets are all the same. I have thought of starting copying dbase1 to A9, then using End and xldown. But could you please explain what macro can we use to handle this task. Many thanks, Frank |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 SP3 Copy Worksheet with another sheet named range now # | Excel Discussion (Misc queries) | |||
Make all values of a 3D named range appear on summary sheet | Excel Discussion (Misc queries) | |||
Sheets named from range, and copy data? | Excel Discussion (Misc queries) | |||
Copy named range from file list to summary workbook | Excel Programming | |||
copy all named ranges in a sheet to seperate sheets | Excel Programming |