Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using named ranges in macro
Hi All
I'm a novice trying to adapt code that I found on http://www.rondebruin.nl/copy2.htm (which is really good), but I'm having a little trouble and I have Two questions: 1. I need to merge data within all worksheets EXCEPT: Sheet("Overview") Hidden sheets Destination Sheet I tried to add to the existing code that excludes the destination sheet & hidden sheets, but my change didn't work? For Each sh In ActiveWorkbook.Worksheets If sh.Name = "Overview" And sh.Name < DestSh.Name And sh.Visible = True Then 'do nothing 2. I want to include a named range in my code instead of actual cell ranges i.e. Range("A18:BJ39") = "GRPResults" named range This named range is the same across all 6 worksheets that need to be merged I'm thinking that it's better to use a named range because it will automatically expand if the user inserts rows - whereas a set cell range will not? Please can you show me how the following code should look using the named ranges versus an actual range (occurs twice in the code before): Sub CopyGRPSections() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim shLast As Long Dim CopyRng As Range Dim StartRow As Long Dim lastrow As Variant lastrow = Range("A40") Application.ScreenUpdating = False Application.EnableEvents = False Sheets("GRP Data Collection").Select Cells.Select Selection.Clear Range("A1").Select Set DestSh = ActiveWorkbook.Worksheets("GRP Data Collection") StartRow = 1 ''''I need to loop through all worksheets but exclude those hidden, the Overview & Destination sheet For Each sh In ActiveWorkbook.Worksheets If sh.Name = "Overview" And sh.Name < DestSh.Name And sh.Visible = True Then '''''do nothing ''''''' How do I change the range to Go to "GRPResults" named range in all worksheets that meet the above criteria? If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then Range("A18:BJ39").Select Selection.Copy With DestSh.Range("A1") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If Last = DestSh.Range("A" & Rows.Count).End(xlUp).Row shLast = sh.Range("A" & Rows.Count).End(xlUp).Row ''''''' How do I change the range to Go to "GRPResults" named range in all worksheets that meet the above criteria? If shLast 0 And shLast = StartRow Then Set CopyRng = sh.Range("A18:BJ39") If Last + CopyRng.Rows.Count DestSh.Rows.Count Then MsgBox "There are not enough rows in the Destsh" GoTo ExitTheSub End If CopyRng.Copy With DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If Next ExitTheSub: Application.Goto DestSh.Cells(1) Application.ScreenUpdating = True Application.EnableEvents = True End With End Sub -- Thank for your help BeSmart |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using named ranges in macro
Try this code VBA Code: -------------------- Sub CopyGRPSections() Dim sh As Worksheet Dim DestSh As Worksheet Dim LastRowDest As Long Dim NewRowDest As Long Dim LastRowSource As Long Dim DestLoc As Range Application.ScreenUpdating = False Application.EnableEvents = False Sheets("GRP Data Collection").Cells.Clear Set DestSh = ActiveWorkbook.Worksheets("GRP Data Collection") For Each sh In ActiveWorkbook.Worksheets If sh.Name < "Overview" And sh.Name < DestSh.Name And sh.Visible = True Then If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then LastRowDest = 1 Set DestLoc = DestSh.Range("A1") Else LastRowDest = DestSh.Range("A" & Rows.Count).End(xlUp).Row NewRowDest = LastRowDest + 1 Set DestLoc = DestSh.Range("A" & NewRowDest) End If LastRowSource = sh.Range("A" & Rows.Count).End(xlUp).Row If LastRowSource + LastRowDest DestSh.Rows.Count Then MsgBox "There are not enough rows in the Destsh" Exit For End If sh.Range("GRPResults").Copy With DestLoc .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats End With End If Next Application.Goto DestSh.Cells(1) Application.ScreenUpdating = True Application.EnableEvents = True End Sub -------------------- -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=183175 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using named ranges in macro
Thanks Joel
The code looks good and I can understand everything that it's doing. But I got a run-time error '1004' when I ran it the first time? Msg: "Method 'Range" of Object '_Worksheet' failed" This happened at the following point in the code: sh.range('GRPResults').Copy The named range is definitely set on two of the worksheets that the code scans and both worksheets have the same range defined under that name i.e (A18:BJ39). Any ideas on how to fix this? -- Thank for your help BeSmart |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using named ranges in macro
I never tried to have the same named range on my than one sheet. Excel only allows one Named Range with the same name. You must have the named range set to one of the sheets. So you need to remove the sheet reference of the Named Range. Using the Address Property will do that like in the code below. The frist two lines of the "TO" is common and can be moved to the beginning of the macro. From sh.Range("GRPResults").Copy To Set MyRange = Range("GRPResults") MyRangeAddr = MyRange.Address(external:=False) sh.Range(MyRangeAddr).Copy VBA Code: -------------------- Sub CopyGRPSections() Dim sh As Worksheet Dim DestSh As Worksheet Dim LastRowDest As Long Dim NewRowDest As Long Dim LastRowSource As Long Dim DestLoc As Range Application.ScreenUpdating = False Application.EnableEvents = False Sheets("GRP Data Collection").Cells.Clear Set DestSh = ActiveWorkbook.Worksheets("GRP Data Collection") Set MyRange = Range("GRPResults") MyRangeAddr = MyRange.Address(external:=False) For Each sh In ActiveWorkbook.Worksheets If sh.Name < "Overview" And sh.Name < DestSh.Name And sh.Visible = True Then If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then LastRowDest = 1 Set DestLoc = DestSh.Range("A1") Else LastRowDest = DestSh.Range("A" & Rows.Count).End(xlUp).Row NewRowDest = LastRowDest + 1 Set DestLoc = DestSh.Range("A" & NewRowDest) End If LastRowSource = sh.Range("A" & Rows.Count).End(xlUp).Row If LastRowSource + LastRowDest DestSh.Rows.Count Then MsgBox "There are not enough rows in the Destsh" Exit For End If sh.Range(MyRangeAddr).Copy With DestLoc .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats End With End If Next Application.Goto DestSh.Cells(1) Application.ScreenUpdating = True Application.EnableEvents = True End Sub -------------------- -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=183175 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using named ranges in macro
That's not true.
You can have a name that is local that is the same. Sheet1!Name1 could refer to A1 on Sheet1 'sheet 99'!Name1 could refer to c3:d5,x9:z10,u5 on Sheet 99 When you define the name (manually), you can include the sheetname to make it local (or a sheet level) name. If you don't include the sheet name in the name, then the name will be global (or workbook level). In code, you can create local/sheet level names several ways. Here's a couple: with worksheets("Sheet 99") .range("a1:C9").name = "'" & .name & "'!Name1" End with Or with worksheets("Sheet 99") .names.add Name:="Name1", RefersTo:="=$a$1" end with ======= Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager NameManager.Zip from http://www.oaltd.co.uk/mvp Allows you to convert from local to global and global to local very easily. joel wrote: I never tried to have the same named range on my than one sheet. Excel only allows one Named Range with the same name. You must have the named range set to one of the sheets. So you need to remove the sheet reference of the Named Range. Using the Address Property will do that like in the code below. The frist two lines of the "TO" is common and can be moved to the beginning of the macro. From sh.Range("GRPResults").Copy To Set MyRange = Range("GRPResults") MyRangeAddr = MyRange.Address(external:=False) sh.Range(MyRangeAddr).Copy VBA Code: -------------------- Sub CopyGRPSections() Dim sh As Worksheet Dim DestSh As Worksheet Dim LastRowDest As Long Dim NewRowDest As Long Dim LastRowSource As Long Dim DestLoc As Range Application.ScreenUpdating = False Application.EnableEvents = False Sheets("GRP Data Collection").Cells.Clear Set DestSh = ActiveWorkbook.Worksheets("GRP Data Collection") Set MyRange = Range("GRPResults") MyRangeAddr = MyRange.Address(external:=False) For Each sh In ActiveWorkbook.Worksheets If sh.Name < "Overview" And sh.Name < DestSh.Name And sh.Visible = True Then If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then LastRowDest = 1 Set DestLoc = DestSh.Range("A1") Else LastRowDest = DestSh.Range("A" & Rows.Count).End(xlUp).Row NewRowDest = LastRowDest + 1 Set DestLoc = DestSh.Range("A" & NewRowDest) End If LastRowSource = sh.Range("A" & Rows.Count).End(xlUp).Row If LastRowSource + LastRowDest DestSh.Rows.Count Then MsgBox "There are not enough rows in the Destsh" Exit For End If sh.Range(MyRangeAddr).Copy With DestLoc .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats End With End If Next Application.Goto DestSh.Cells(1) Application.ScreenUpdating = True Application.EnableEvents = True End Sub -------------------- -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=183175 Microsoft Office Help -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using named ranges in macro
First, it has double quotes around that name:
sh.Range("GRPResults").Copy Second, you don't have a name on that sheet--or the name doesn't refer to a range. You could add a few checks to make sure you know when there's problems--or if to ignore that sheet. dim TestRng as range .... Set testrng = nothing on error resume next set testrng = sh.Range("GRPResults") on error goto 0 if testrng is nothing then 'it wasn't correct msgbox sh.name & " has a problem with grpresults" else 'do the real work here testrng.Copy 'might as well use that variable! .... That means that you don't have a name BeSmart wrote: Thanks Joel The code looks good and I can understand everything that it's doing. But I got a run-time error '1004' when I ran it the first time? Msg: "Method 'Range" of Object '_Worksheet' failed" This happened at the following point in the code: sh.range('GRPResults').Copy The named range is definitely set on two of the worksheets that the code scans and both worksheets have the same range defined under that name i.e (A18:BJ39). Any ideas on how to fix this? -- Thank for your help BeSmart -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using named ranges in macro
Thanks Joel
I got another error on your revised code too: Run-time error '1004' "Method 'Range' of Object '_Global' failed" However - I've know why it happened... I was on the worksheet "GRP Data Collection" when I ran the macro, and this worksheet does not have the named range "GRPResults". When I selected a worksheet that does have the named range, and ran the macro, it ran perfectly!!! THANK YOU THANK YOU THANK YOU... Unfortunately, I can't trust the users to select a particular worksheet before running the macro... To avoid the debug in the future, should I add the named range to "all" sheets (which won't affect the ones that are excluding when the macro runs) or is there a better way to safeguard against the debug happening? I can't add "Sheets("Plan 1").Select" to the code, because I don't know what names the users will use on the worksheets. FYI the same name range on more than one sheet. Users will make a copy of a master worksheet within the workbook, rename the copy and then use it. The master worksheet has the named range, so when the user makes a copy - the named range copies over too - thereby creating two sheets with the same named range. Excel seems to only show the named range for the worksheet selected, plus it references the sheet name when you look at the list under insert/name/define (in Excel 2003). Thank again for your help with this! BeSmart |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using named ranges in macro
Thanks Dave
You explained that alot better than me... Your "testing" code worked great - once I worked out 'where' to put it within the code... And thanks for pointing out the " " (a named range does not need them) I'm also going to setup the named range across all worksheets to avoid the error happening on existing sheets. The error message will be good once Users start playing with it and adding sheets that they shouldn't be adding... -- Thank for your teachings & help BeSmart "Dave Peterson" wrote: That's not true. You can have a name that is local that is the same. Sheet1!Name1 could refer to A1 on Sheet1 'sheet 99'!Name1 could refer to c3:d5,x9:z10,u5 on Sheet 99 When you define the name (manually), you can include the sheetname to make it local (or a sheet level) name. If you don't include the sheet name in the name, then the name will be global (or workbook level). In code, you can create local/sheet level names several ways. Here's a couple: with worksheets("Sheet 99") .range("a1:C9").name = "'" & .name & "'!Name1" End with Or with worksheets("Sheet 99") .names.add Name:="Name1", RefersTo:="=$a$1" end with ======= Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager NameManager.Zip from http://www.oaltd.co.uk/mvp Allows you to convert from local to global and global to local very easily. joel wrote: I never tried to have the same named range on my than one sheet. Excel only allows one Named Range with the same name. You must have the named range set to one of the sheets. So you need to remove the sheet reference of the Named Range. Using the Address Property will do that like in the code below. The frist two lines of the "TO" is common and can be moved to the beginning of the macro. From sh.Range("GRPResults").Copy To Set MyRange = Range("GRPResults") MyRangeAddr = MyRange.Address(external:=False) sh.Range(MyRangeAddr).Copy VBA Code: -------------------- Sub CopyGRPSections() Dim sh As Worksheet Dim DestSh As Worksheet Dim LastRowDest As Long Dim NewRowDest As Long Dim LastRowSource As Long Dim DestLoc As Range Application.ScreenUpdating = False Application.EnableEvents = False Sheets("GRP Data Collection").Cells.Clear Set DestSh = ActiveWorkbook.Worksheets("GRP Data Collection") Set MyRange = Range("GRPResults") MyRangeAddr = MyRange.Address(external:=False) For Each sh In ActiveWorkbook.Worksheets If sh.Name < "Overview" And sh.Name < DestSh.Name And sh.Visible = True Then If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then LastRowDest = 1 Set DestLoc = DestSh.Range("A1") Else LastRowDest = DestSh.Range("A" & Rows.Count).End(xlUp).Row NewRowDest = LastRowDest + 1 Set DestLoc = DestSh.Range("A" & NewRowDest) End If LastRowSource = sh.Range("A" & Rows.Count).End(xlUp).Row If LastRowSource + LastRowDest DestSh.Rows.Count Then MsgBox "There are not enough rows in the Destsh" Exit For End If sh.Range(MyRangeAddr).Copy With DestLoc .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats End With End If Next Application.Goto DestSh.Cells(1) Application.ScreenUpdating = True Application.EnableEvents = True End Sub -------------------- -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=183175 Microsoft Office Help -- Dave Peterson . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using named ranges in macro
I don't know what this means:
And thanks for pointing out the " " (a named range does not need them) But it sounds like you got the code running. BeSmart wrote: Thanks Dave You explained that alot better than me... Your "testing" code worked great - once I worked out 'where' to put it within the code... And thanks for pointing out the " " (a named range does not need them) I'm also going to setup the named range across all worksheets to avoid the error happening on existing sheets. The error message will be good once Users start playing with it and adding sheets that they shouldn't be adding... -- Thank for your teachings & help BeSmart "Dave Peterson" wrote: That's not true. You can have a name that is local that is the same. Sheet1!Name1 could refer to A1 on Sheet1 'sheet 99'!Name1 could refer to c3:d5,x9:z10,u5 on Sheet 99 When you define the name (manually), you can include the sheetname to make it local (or a sheet level) name. If you don't include the sheet name in the name, then the name will be global (or workbook level). In code, you can create local/sheet level names several ways. Here's a couple: with worksheets("Sheet 99") .range("a1:C9").name = "'" & .name & "'!Name1" End with Or with worksheets("Sheet 99") .names.add Name:="Name1", RefersTo:="=$a$1" end with ======= Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager NameManager.Zip from http://www.oaltd.co.uk/mvp Allows you to convert from local to global and global to local very easily. joel wrote: I never tried to have the same named range on my than one sheet. Excel only allows one Named Range with the same name. You must have the named range set to one of the sheets. So you need to remove the sheet reference of the Named Range. Using the Address Property will do that like in the code below. The frist two lines of the "TO" is common and can be moved to the beginning of the macro. From sh.Range("GRPResults").Copy To Set MyRange = Range("GRPResults") MyRangeAddr = MyRange.Address(external:=False) sh.Range(MyRangeAddr).Copy VBA Code: -------------------- Sub CopyGRPSections() Dim sh As Worksheet Dim DestSh As Worksheet Dim LastRowDest As Long Dim NewRowDest As Long Dim LastRowSource As Long Dim DestLoc As Range Application.ScreenUpdating = False Application.EnableEvents = False Sheets("GRP Data Collection").Cells.Clear Set DestSh = ActiveWorkbook.Worksheets("GRP Data Collection") Set MyRange = Range("GRPResults") MyRangeAddr = MyRange.Address(external:=False) For Each sh In ActiveWorkbook.Worksheets If sh.Name < "Overview" And sh.Name < DestSh.Name And sh.Visible = True Then If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then LastRowDest = 1 Set DestLoc = DestSh.Range("A1") Else LastRowDest = DestSh.Range("A" & Rows.Count).End(xlUp).Row NewRowDest = LastRowDest + 1 Set DestLoc = DestSh.Range("A" & NewRowDest) End If LastRowSource = sh.Range("A" & Rows.Count).End(xlUp).Row If LastRowSource + LastRowDest DestSh.Rows.Count Then MsgBox "There are not enough rows in the Destsh" Exit For End If sh.Range(MyRangeAddr).Copy With DestLoc .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats End With End If Next Application.Goto DestSh.Cells(1) Application.ScreenUpdating = True Application.EnableEvents = True End Sub -------------------- -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=183175 Microsoft Office Help -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What am I doing wrong with Ranges named within Macro... | Excel Programming | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
Referring to named ranges in a macro | Excel Programming | |||
Macro eliminating named ranges | Excel Programming | |||
Named Ranges - Macro Problems | Excel Programming |