Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I make this simpler?
Hi, I have 2 workbooks. In the first workbook, I have several worksheet sorted by last name. In the second workbook, I have 4 columns: name account#, description, end date. I am trying to autofilter it so that I can copy the respectiv information from workbook2("text") to workbook1("SNAPSHOT - PWC" through the following criteria: contains the name of the sheet and i end date is greater than 20040630 or equals to 0. Here is what I hav so far, and it works. But is there a way to vba code this so that i will go through each worksheet in a loop of some sort so that I don' have to keep rewriting the same code and just changing the names on it If this is confusing please let me know, but I think the coding provided below should be self-explanatory in what I am trying t accomplish. Sub GetAccounts() Windows("Text.xls").Activate Sheets("Sheet3").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=*altemus*" Selection.AutoFilter Field:=4, Criteria1:="20040630" Operator:=xlOr, _ Criteria2:="0" Range("B1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Range("A1").Select Windows("SNAPSHOT-PWC.xls").Activate Sheets("Altemus").Select Range("A34").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone SkipBlanks:= _ False, Transpose:=False Windows("Text.xls").Activate Sheets("Sheet3").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=*anderson*" Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:="20040630" Operator:=xlOr, _ Criteria2:="0" Range("B1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Range("A1").Select Windows("SNAPSHOT-PWC.xls").Activate Sheets("Anderson").Select Range("A34").Select Selection.PasteSpecial Paste:=xlAll Windows("Text.xls").Activate Sheets("Sheet3").Select Selection.AutoFilter Field:=1, Criteria1:="=*bartlik*" Selection.AutoFilter Field:=4, Criteria1:="20040630" Operator:=xlOr, _ Criteria2:="=0" Range("B1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Range("A1").Select Windows("SNAPSHOT-PWC.xls").Activate Sheets("Bartlik").Select Range("A34").Select Selection.PasteSpecial Paste:=xlAll etc, etc. I can keep going with this, but I am sure there is a much simple method which requires less coding, since I have so many differen sheets. Please help!! Thanks in advance -- Sethaholi ----------------------------------------------------------------------- Sethaholic's Profile: http://www.excelforum.com/member.php...fo&userid=2511 View this thread: http://www.excelforum.com/showthread.php?threadid=38686 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I make this simpler?
Sub GetAccounts()
v = Array("altemus","bartlik","hortimus","swansen") for i = lbound(v) to ubound(v) Windows("Text.xls").Activate Sheets("Sheet3").Select Selection.AutoFilter ' alteration here Selection.AutoFilter Field:=1, Criteria1:="=*" & v(i) & "*" Selection.AutoFilter Field:=4, Criteria1:="20040630", Operator:=xlOr, _ Criteria2:="0" Range("B1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Range("A1").Select Windows("SNAPSHOT-PWC.xls").Activate ' alteration made here Sheets(v(i)).Select Range("A34").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Loop End Sub -- Regards, Tom Ogilvy "Sethaholic" wrote in message ... Hi, I have 2 workbooks. In the first workbook, I have several worksheets sorted by last name. In the second workbook, I have 4 columns: name, account#, description, end date. I am trying to autofilter it so that I can copy the respective information from workbook2("text") to workbook1("SNAPSHOT - PWC") through the following criteria: contains the name of the sheet and is end date is greater than 20040630 or equals to 0. Here is what I have so far, and it works. But is there a way to vba code this so that it will go through each worksheet in a loop of some sort so that I don't have to keep rewriting the same code and just changing the names on it? If this is confusing please let me know, but I think the coding I provided below should be self-explanatory in what I am trying to accomplish. Sub GetAccounts() Windows("Text.xls").Activate Sheets("Sheet3").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=*altemus*" Selection.AutoFilter Field:=4, Criteria1:="20040630", Operator:=xlOr, _ Criteria2:="0" Range("B1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Range("A1").Select Windows("SNAPSHOT-PWC.xls").Activate Sheets("Altemus").Select Range("A34").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Windows("Text.xls").Activate Sheets("Sheet3").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=*anderson*", Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:="20040630", Operator:=xlOr, _ Criteria2:="0" Range("B1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Range("A1").Select Windows("SNAPSHOT-PWC.xls").Activate Sheets("Anderson").Select Range("A34").Select Selection.PasteSpecial Paste:=xlAll Windows("Text.xls").Activate Sheets("Sheet3").Select Selection.AutoFilter Field:=1, Criteria1:="=*bartlik*" Selection.AutoFilter Field:=4, Criteria1:="20040630", Operator:=xlOr, _ Criteria2:="=0" Range("B1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Range("A1").Select Windows("SNAPSHOT-PWC.xls").Activate Sheets("Bartlik").Select Range("A34").Select Selection.PasteSpecial Paste:=xlAll etc, etc. I can keep going with this, but I am sure there is a much simpler method which requires less coding, since I have so many different sheets. Please help!! Thanks in advance. -- Sethaholic ------------------------------------------------------------------------ Sethaholic's Profile: http://www.excelforum.com/member.php...o&userid=25113 View this thread: http://www.excelforum.com/showthread...hreadid=386864 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I make this simpler?
Thanks for your response, Tom, but I get an error: Loop without Do. How can I fix this? Thank -- Sethaholi ----------------------------------------------------------------------- Sethaholic's Profile: http://www.excelforum.com/member.php...fo&userid=2511 View this thread: http://www.excelforum.com/showthread.php?threadid=38686 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I make this simpler?
Loop
should be Next My typo. -- Regards, Tom Ogilvy "Sethaholic" wrote in message ... Thanks for your response, Tom, but I get an error: Loop without Do. How can I fix this? Thanks -- Sethaholic ------------------------------------------------------------------------ Sethaholic's Profile: http://www.excelforum.com/member.php...o&userid=25113 View this thread: http://www.excelforum.com/showthread...hreadid=386864 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
is there a simpler way to do this formula? | Excel Worksheet Functions | |||
VBA code simpler? | Excel Discussion (Misc queries) | |||
Simpler Formula ... 2nd lowest value? | Excel Worksheet Functions | |||
Need to make this simpler. | Excel Programming | |||
Lil Simpler | Excel Programming |