How to incorporate 2 sets of changing named ranges

Hi All
I'm a novice that needs help please..... sorry - I've tried but I'm stuck....
(one workbook, heaps of worksheets)

I've created a macro that runs a set of actions for multiple named ranges by 
calling each (see code below)
It finds / copies / pastes data from different worksheets & named ranges 
into an overview.

The macro adds a 1 or 2 or 3 to "collectionMT" and repeats the actions 
against each named ranges:
CollectMT1, CollectMT2, CollectMT3, CollectMT4 etc through to CollectMT9

I don't know how to do the same (in the same macro) for the destination 
named ranges?
Data is inserted into the second set of named ranges on the overview:
overviewMT1, overviewMT2, overviewMT3, overviewMT4 etc through to overviewMT9

They are numbered the same as the 1st set and they live together e.g.
collectionMT1 and overviewMT1 have to run in the same macro,
then collectionMT2 and overviewMT2 have to run together etc

I've marked the two times where the sub refer to the overviewMT1 (etc) named 
ranges and needs to rotate through the numbers.
Thanks for your help in advance!

Sub MTcollection()

For i = 1 To 9
Call Test("collectionMT" & i)

Next i

End Sub

Sub Test(collectionMT As String)

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim LastRowDest As Long
Dim NewRowDest As Long
Dim LastRowSource As Long
Dim DestLoc As Range
Dim MTRng As Range
Dim myrange As Range
Dim myRange1 As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row

Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("Collection").Visible = True
Sheets("Collection").Cells.Clear
Set DestSh = ActiveWorkbook.Worksheets("Collection")
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "Overview Template" And sh.Name <> "GRP Wkly Collection" And 
sh.Name <> "GRP Qtrly Collection" And sh.Name <> DestSh.Name And sh.Visible = 
True Then
Set MTRng = Nothing
On Error Resume Next
Set MTRng = sh.Range(collectionMT)
'' The above named range already changes to collectionMT2, collectionMT3, 
collectionMT4 etc
On Error GoTo 0
If MTRng Is Nothing Then
Else
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
MTRng.Copy
With DestLoc
 .PasteSpecial xlPasteValues
 .PasteSpecial xlPasteFormats
End With
Application.CutCopyMode = False
End If
End If
Next

Sheets("Overview Template").Select
    Application.Goto Reference:="OverviewMT1"
'''''''''''overviewMT1 will change to overviewMT2, overviewMT3, overviewMT4 
etc

    Selection.ClearContents
Sheets("Collection").Select
Range("A1").Select
Range("A1:BL" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Selection.Sort Key1:=Range("G1"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("C:C").Select
With Selection
..HorizontalAlignment = xlLeft
End With
Range("A1:BL" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Selection.Copy

Range(overviewMT1).Resize(1, 1).Offset(1, 0).Insert shift:=xlDown
'''''''''''''''overviewMT1 will change to overviewMT2, overviewMT3, 
overviewMT4 etc

Range("A44").Select
Sheets("Collection").Visible = False
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

-- 
BeSmart
0
Utf
3/8/2010 12:33:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
479 Views

Similar Articles

[PageSpeed] 15

Call Test("collectionMT" & i, "overviewMT" & i)

Mishell


"BeSmart" <BeSmart@discussions.microsoft.com> a �crit dans le message de 
news: 09F358B3-E9B1-4007-BFC2-1D514B0A51B8@microsoft.com...
> Hi All
> I'm a novice that needs help please..... sorry - I've tried but I'm 
> stuck....
> (one workbook, heaps of worksheets)
>
> I've created a macro that runs a set of actions for multiple named ranges 
> by
> calling each (see code below)
> It finds / copies / pastes data from different worksheets & named ranges
> into an overview.
>
> The macro adds a 1 or 2 or 3 to "collectionMT" and repeats the actions
> against each named ranges:
> CollectMT1, CollectMT2, CollectMT3, CollectMT4 etc through to CollectMT9
>
> I don't know how to do the same (in the same macro) for the destination
> named ranges?
> Data is inserted into the second set of named ranges on the overview:
> overviewMT1, overviewMT2, overviewMT3, overviewMT4 etc through to 
> overviewMT9
>
> They are numbered the same as the 1st set and they live together e.g.
> collectionMT1 and overviewMT1 have to run in the same macro,
> then collectionMT2 and overviewMT2 have to run together etc
>
> I've marked the two times where the sub refer to the overviewMT1 (etc) 
> named
> ranges and needs to rotate through the numbers.
> Thanks for your help in advance!
>
> Sub MTcollection()
>
> For i = 1 To 9
> Call Test("collectionMT" & i)
>
> Next i
>
> End Sub
>
> Sub Test(collectionMT As String)
>
> Dim sh As Worksheet
> Dim DestSh As Worksheet
> Dim LastRowDest As Long
> Dim NewRowDest As Long
> Dim LastRowSource As Long
> Dim DestLoc As Range
> Dim MTRng As Range
> Dim myrange As Range
> Dim myRange1 As Range
> lastrow = Cells(Rows.Count, "A").End(xlUp).Row
>
> Application.ScreenUpdating = False
> Application.EnableEvents = False
> Sheets("Collection").Visible = True
> Sheets("Collection").Cells.Clear
> Set DestSh = ActiveWorkbook.Worksheets("Collection")
> For Each sh In ActiveWorkbook.Worksheets
> If sh.Name <> "Overview Template" And sh.Name <> "GRP Wkly Collection" And
> sh.Name <> "GRP Qtrly Collection" And sh.Name <> DestSh.Name And 
> sh.Visible =
> True Then
> Set MTRng = Nothing
> On Error Resume Next
> Set MTRng = sh.Range(collectionMT)
> '' The above named range already changes to collectionMT2, collectionMT3,
> collectionMT4 etc
> On Error GoTo 0
> If MTRng Is Nothing Then
> Else
> 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
> MTRng.Copy
> With DestLoc
> .PasteSpecial xlPasteValues
> .PasteSpecial xlPasteFormats
> End With
> Application.CutCopyMode = False
> End If
> End If
> Next
>
> Sheets("Overview Template").Select
>    Application.Goto Reference:="OverviewMT1"
> '''''''''''overviewMT1 will change to overviewMT2, overviewMT3, 
> overviewMT4
> etc
>
>    Selection.ClearContents
> Sheets("Collection").Select
> Range("A1").Select
> Range("A1:BL" & Cells(Rows.Count, "A").End(xlUp).Row).Select
> Selection.Sort Key1:=Range("G1"), Order1:=xlDescending, Header:=xlGuess, _
>        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> Range("C:C").Select
> With Selection
> .HorizontalAlignment = xlLeft
> End With
> Range("A1:BL" & Cells(Rows.Count, "A").End(xlUp).Row).Select
> Selection.Copy
>
> Range(overviewMT1).Resize(1, 1).Offset(1, 0).Insert shift:=xlDown
> '''''''''''''''overviewMT1 will change to overviewMT2, overviewMT3,
> overviewMT4 etc
>
> Range("A44").Select
> Sheets("Collection").Visible = False
> Application.ScreenUpdating = True
> Application.EnableEvents = True
> End Sub
>
> -- 
> BeSmart 


0
Mishell
3/8/2010 2:12:21 PM
Thanks Mishell
That worked perfectly - so simply, but I didn't know you could do that.
-- 
BeSmart


0
Utf
3/9/2010 1:23:02 AM
Reply:

Similar Artilces:

Converting to PDF #2
I lose my hyperlinks to email addresses when I convert from Publisher 2002 to a .PDF in Adobe Acrobat 6.0 Professional. Do I need to upgrade to Publisher 2003 or can this be fixed? While in a state of ecstasy after repairing his laptop, Ed sees a message from Banks <Banks@discussions.microsoft.com>. On it is written: > I lose my hyperlinks to email addresses when I convert from Publisher > 2002 to a .PDF in Adobe Acrobat 6.0 Professional. Publisher sends printable information to the printer driver. As you cannot click a hyperlink on a printed piece of paper, Publisher doesn&#...

Problems with public folders #2
I am having two problems which may or may not be related. 1) In System Manager when I try to view or work with the public folder tree, I get the following error "The token supplied to the function is invalid", ID no:80090308. I cannot see any public folders or manage them at all after that. 2) Whenever anyone clicks on send/receive in outlook 2003, they get the following error "Task 'Microsoft Exchange Server' reported error (0x8004010F):'The operation failed. An object could not be found.'" Does anyone have an idea of what the problem is for either...

How do you change the rows to columns and columns to rows.
Ok, I am stumped on how to do this? I have a spreadsheed that has Dates in the A column and Data in the row. I want to make is so, the data is in the A column and Dates ru accross in the 1 column. How can I do this? Sound easy, but I am stumped??? :eek -- cc4digita ----------------------------------------------------------------------- cc4digital's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2656 View this thread: http://www.excelforum.com/showthread.php?threadid=39843 Highlight your data and copy it into the clipboard. Move to an open spot in the sprea...

How do I add vertical lines to separate columns in Outlook 2003 #2
In Outlook 2000, all columns were separated by a gray vertical line. I can't seem to set this up in Outlook 2003. Any help would be appreciated ARe you using Word as the message editor? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, mschwartz asked: | In Outlook 2000, all columns were separated by a gray | vertical line. I can't seem to set this up in O...

OnChange event
Hi all, I want to let the end users change the color of the top and bottom dark blue (or navy) borders of the Account form using a drop down that lists the colors (red, yellow and blue) .So the OnChange event of the picklist should trigger the change of the top and bottom dark blue colors to which ever color is selected. However I dont know if this can be accomplished. I even dont know if a stylesheet is used for that purpose, which one refers to these areas of the form. Any help will be deeply appreciated. Thanks in advance! ...

Swedish characters in the Display name #2
I have a problem with Swedish characters in the senders' names on outgoing e-mail. Previously we have formatted the display names like: Last name, First name This is causing problems for some external recipients, since their e-mail program reads this as two addresses (Last name as one name and First name as another) I tried changing the display name for one user to "First name Last name" instead, but then the Swedish characters are not shown when he/she sends an external e-mail. Example: "Åke Jönsson" becomes "Ake Jonsson" or "Åsa H...

Print 2 receipts when selling item with rebate?
Is there any way to program rms to print multiple receipts on particullar items, for example I sell an automatic pool cleaner that has two differant mail in rebates and we always supply the customer with duplicate receipts Victor Out of the box, no. The easiest way is to train cashiers to use the Reprint function when they need a second copy. The other option is to configure the receipts to print 2 copies every time, but that's probably a waste of paper. I'm pretty sure this could be accomplished as a customization using the HTML status bar and QSBridge, but you would need a ...

Select Names Dialog Box
Could someone help with the following? I am using Access 2003 with outlook 2003 and all I need to do is open outlook and for it to display the Select Names Dialog Box ( it would be nice to open outlook if it was closed) no more than that. Hence I am now after the code to go in the click event of the button. Any examples of VBA code that will work within Access 2003 would be appreciated The Select Names dialog box is programmable starting from Outlook 2007. You can't display it in Outlook 2003. http://msdn.microsoft.com/en-us/library/bb176400.aspx --JP On Feb 5, 3:52=...

2007 Schema Prep #2
I extended my AD last night for 2007 and have a few questions to verify it was successful: I have a root domain (domain.local) that houses my schema master and a child domain (domain.net) that has Exchange 2003. I ran setup /prepareAD then ran setup /preparedomain: domain.net and both said they completed successfully. However, I was expecting to see the new OU (Microsoft Exchange Security Groups) in my child domain. It is present in my root domain. Is that all correct? In my child domain I now have a new global security group call Exchange Install Domain Servers. Last thing, the D...

Formula query #2
I am trying to identify a formula which will add and then display th number of times a number appears in a range. For example, the range looks like this .......A...........B............C 1...1110......1110.......1114 2...1110..... 1111.......1111 3...1111......1112.......1110 4...1111......1113.......1111 The numbers which appear in the applicable range e.g. 1110, could b any number between 1110 and 9999 and as such I cannot simply predefin which number to look for. I am hoping the output could like as follows: .......A........B 1....1110....4 2....1111....5 3....1112....1 4.....

if formula #2
i have this formula in a cell D3 =IF(A1="F",B1&B2) how can i make it multiple for example i want to add this IF(a2="G",B1&B3) in D3 also. thanks Either - =IF(AND(A1="F",A2="G"),B1&B3,) =IF(OR(A1="F",A2="G"),B1&B3,) Regards. Bill Ridgeway Computer Solutions "jaypee" <jaypee2cool@yahoo.com> wrote in message news:O3NhGKIhGHA.1272@TK2MSFTNGP03.phx.gbl... >i have this formula in a cell D3 =IF(A1="F",B1&B2) > how can i make it multiple for example i want to add this IF(a2="G&q...

Unable to change password
Hi, We have some users in domain A, who now have exchange accounts in domain B. They will log on to domain A as usual, then when they open outlook it prompts them for a logon to domain B. Works fine. Problem is that we gave them all a standard PW for domain B so we could check their mail was working. We now want them to change their passwords for domain B, so we selected 'user must change PW at next logon' in the AD. Sure enough they are prompted to change password when they open outlook, but the password change is never accepted. Can anyone help? Andy Cox ...

Security Name
Hello - I have another problem that I could use help on. I have several accounts with different brokerage houses that holds the same security but with slightly different names. When I go to Update those accounts, I get popups from Money asking if the name for the security is the same as another security with a similar name. I say OK. But when it gets to the next account I get the same popup to prompt me to put it back to the old name, which gets annoying. To solve this problem, I could use 2 different names for the same security but then my summaries would be based on 2 different ...

After Re-Install
HELP!!! After a crash and a re-install of Office we are now getting user name and password errors in Outlook 2002. We can access the same accounts via a www interface with the same account information. PLEASE help. I've downloaded all the service packs for XP and Office and I've tried un-installing and re-installing including deleting all of the profile information I can find and deleting Outlook's registry, but to no avail. I'm obviously missing something. Any help would be fabulous. Please e-mail Monica@ViaSollertia.co.nz Thanks so much!!! Monica ...

Percent Change
I'm using the formula (A2-A1)/A1 to determine the % change between two cells. I have an instance where I need to subtract the value of two cells from the current cell before determining the % change. I've come up with several different ways to arrive at the actual changed value (e.g. A3-SUM(A1:A2) or SUM(A3-A2-A1) ) which return the correct value but when I try to arrive at the % change from the original amount by adding "/A1" to the formula I get an error. Am I way off? Tom, Could you post the end formula exactly as you typed it? It should work, based on what you'...

Reminder is not work in default folder #2
I used as follow coding to send email to outlook client(outlook 2000): ''''''''''''''''''''''''''''''=AD'''''''''''''''' Dim ll As New CDO.Message Dim bb As New CDO.Configuration bb.Fields(cdoSMTPServer) =3D "nc80012" bb.Fields(cdoSendUsingMethod) =3D cdoSendUsingPort bb.Fields.Update Set ll.Configuration =3D bb ll.Fields.Append "urn:schemas:mailheader:x-mess=ADage-flag", adBSTR _ ...

POP3 & IMAP4 services #2
Hi there, I got problems on starting POP3 & IMAP4 services. Recently they cannot be start after my exchange server restarts. At the beginning, POP3 cannot be start automatically, later it's IMAP4. After I can start them manually after the server has been completely started, I check event viewer, I found the server did try to start IMAP4, but got error "An error occurred while starting the Microsoft Exchange IMAP4 Service: server instance number 1 failed to start with error 0x80040a01." At the same time, an event also there, "Microsoft Exchange IMAP4 Service (Build...

sort data without changing pattern
I have a worksheet where every other row has a 'pattern' in it to help make it easier to read across the spreadsheet. The problem I'm running into is as I add new rows at the bottom of the spreadsheet and then resort them into their correct place, the patterns go with the sort and now the patterns have to be redone again the get them back to every other row. Any suggestions? This will happen if your patterns are manually applied. It would be better to remove that manual shading and to apply background colours using conditional formatting, the condition being if th...

VB: using a string to set a range object?
I'm a bit new to the excel "range" object type. I was suprised to see that while I can do: dim chunk as Range chunk = .Range(A5:B6) I apparently cannot do: dim chunk as Range dim stuff as string string = "A5:B6" chunk= .Range(string) How can I concatenate up a string describing a range, and then use it to define a range object's target cells? - Ross. Oops, I meant chunk = .Range("A5:B6") in the first example - I forgot the quotes. R. "RGK" <nothanks@nospam.go> wrote in message news:RqydnSWzbu_OEZbeRVn-2A@...

2 accounts being sent under one address
I currently have two accounts in my Outlook 2000. xxx@provider.net & PPP@provider.net. The PPP@provider.net is the primary account. When sending an email from the xxx@provider.net, it still shows as the PPP@provider.net address, so in turn I get any replies to the xxx@provider.net back to the PPP@provider.net. Is there any way to make the reply address or the address of sender shown to the xxx@provider.net account? You said you had 2 accounts. You then listed 2 email addresses. Those are not the same thing. Clarify your post. -- Russ Valentine [MVP-Outlook] "jeepstr9" ...

Compare 2 Worksheets Create a 3rd depending on results
I have a unique problem that none of the other posts or shareware seems to solve. I have 2 worksheets (orig.xls and new.xls) that are 15 columns wide (to the "O"). The data is just numbers but the second column either has the words: "new" or "cancel" which is important as you will see. I need to compare the two worksheets and create a third worksheet (update.xls) depending on the three possible results: 1) If a row is removed in the new.xls file = copy the row from the orig.xls file and make the 2nd column "Cancel" 2) If a row is added to the new.xls...

Printing Externally #2
That works great...except it word brings up a message box stating tha Word is currently printing, if you exit, you will cancel you print.....and if i click no, so i dont close word, it still doesnt wan to print...any suggestions???Thank -- tess45 ----------------------------------------------------------------------- tess457's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1393 View this thread: http://www.excelforum.com/showthread.php?threadid=26662 There's a setting in MSWord that allows you to do background printing. I've turned this off--so Word do...

money 03 same funds in 2 accounts
I have 1 tax defered account and 1 non-tax defered account. They both have the same mutual fund in them. Money won't let me use the same symbol twice for 2 accounts. Any thoughts ? See http://umpmfaq.info/faqdb.php?q=59. "Fruehauf2000" <fruehauf2000@aol.com> wrote in message news:20041201213351.11419.00001421@mb-m29.aol.com... >I have 1 tax defered account and 1 non-tax defered account. They both have >the > same mutual fund in them. Money won't let me use the same symbol twice for > 2 > accounts. Any thoughts ? ...

OT: Trend Micro WFBS SP2 beta starting 2/15/10
Anyone interested in helping to improve the WFBS product should join the beta...and all future ones. https://www.trendbeta.com/index.php?get=356&content=554 Gregg Hill -- Gregg's pet peeves: First of all, what does a peeve look like, and why would anyone want one as a pet? Peeve #1: Apostrophes: when in doubt, leave them out! You will be correct more often than not. Its = Belonging to it. For example, "Look at the sky. Its color is blue." It's = It is. For example, "It's hot today." It's = It has. For example, "It's b...

Outlook 2003
We're on Exchange 2003 and have started rolling out Outlook 2003 in place of Outlook 2000. In Outlook 2003 by default all folders come up grouped by day and with solid lines between the messages. Everyone hates these "features" and has made a point of telling me so. Yes, each individual folder can be changed back through the user interface but that's a non-solution for 100 users that have an average of 25 folders each plus the 50 or so public folders as well. Is there any way to change this default behavior in Outlook 2003 so that all folders come up without the solid lines...