Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and multiple sheets
I apologize for the newbie question, but I'm new to VBA/Excel and need some
advice. Let me give a simple description of what I would like to do. The workbook would consist of 3 sheets. The first sheet would be the "data entry" sheet, with Sheets 2 and 3 being generated by some VBA code. This is done to minimize any data entry/user error. As an example, let's say the user enters in three pieces of information on the first sheet. He/she enters the name of a fruit or vegetable, then the category that it belongs in "fruit" or "vegetable", and finally the price. Example: apple fruit .50 potato vegetable .24 banana fruit .55 corn vegetable .28 The user would (ideally) click a button on the page, or run the VBA code which would iterate through all of the entries in sheet one. If it's a fruit, it would copy the entire row to Sheet 2. If it was a vegetable, it would copy the entire row to Sheet 3. After the code was run, Sheet 2 would look like: apple fruit .50 banana fruit .55 And Sheet 3 would be: potato vegetable .24 corn vegetable .28 The downside is that Sheets 2 and 3 have to be recalculated every time, but since there won't be a lot of data, it's OK. Any assistance or ideas would be appreciated. Thanks in advance, Alan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and multiple sheets
Assuming the column heading row is on Row 3, and the columns used for the
entry are A through C. Let's also further assume that the first row of available data on worksheets 2 and 3 would be row 7 Dim WS1 as Worksheet, WS2 as Worksheet, WS3 as Worksheet DIM FDR as Long, LDR as Long, I as Long, strType as String Dim WR2 as Long, WR3 as Long 'This is to be used to keep track of what row is available for pasting on worksheets 2 and 3. Set WS1 = Thisworkbook.Worksheets("Sheet1") Set WS2 = Thisworkbook.Worksheets("Sheet2") Set WS3 = Thisworkbook.Worksheets("Sheet3") FDR = 4 LDR = WS1.Range("A65536").End(xlUp).Row WR2 = WS2.Range("A65536").End(xlUp).Row WR3 = WS3.Range("A65536").End(xlUp).Row If WR2<7 Then WR2 = 7 End If If WR3<7 Then WR3 = 7 End If For I = FDR to LDR Step 1 strType = WS1.Range("B" & I).Text Select Case UCase(strType) Case "FRUIT" WS1.Range("A" & I & ":C" & I).Copy WS2.Paste(WS2.Range("A" & WR2)) WR2 = WR2 + 1 CutCopyMode = 0 Case "VEGETABLE" WS1.Range("A" & I & ":C" & I).Copy WS3.Paste(WS3.Range("A" & WR2)) WR3 = WR3 + 1 CutCopyMode = 0 End Select Next I -- Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Alan L. Wagoner" wrote in message ... I apologize for the newbie question, but I'm new to VBA/Excel and need some advice. Let me give a simple description of what I would like to do. The workbook would consist of 3 sheets. The first sheet would be the "data entry" sheet, with Sheets 2 and 3 being generated by some VBA code. This is done to minimize any data entry/user error. As an example, let's say the user enters in three pieces of information on the first sheet. He/she enters the name of a fruit or vegetable, then the category that it belongs in "fruit" or "vegetable", and finally the price. Example: apple fruit .50 potato vegetable .24 banana fruit .55 corn vegetable .28 The user would (ideally) click a button on the page, or run the VBA code which would iterate through all of the entries in sheet one. If it's a fruit, it would copy the entire row to Sheet 2. If it was a vegetable, it would copy the entire row to Sheet 3. After the code was run, Sheet 2 would look like: apple fruit .50 banana fruit .55 And Sheet 3 would be: potato vegetable .24 corn vegetable .28 The downside is that Sheets 2 and 3 have to be recalculated every time, but since there won't be a lot of data, it's OK. Any assistance or ideas would be appreciated. Thanks in advance, Alan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and multiple sheets
There is a typo in this code:
For I = FDR to LDR Step 1 strType = WS1.Range("B" & I).Text Select Case UCase(strType) Case "FRUIT" WS1.Range("A" & I & ":C" & I).Copy WS2.Paste(WS2.Range("A" & WR2)) WR2 = WR2 + 1 CutCopyMode = 0 Case "VEGETABLE" WS1.Range("A" & I & ":C" & I).Copy WS3.Paste(WS3.Range("A" & WR2)) ' <==WR2 should be WR3 WR3 = WR3 + 1 CutCopyMode = 0 End Select Next I -- Regards, Tom Ogilvy Ronald Dodge wrote in message ... Assuming the column heading row is on Row 3, and the columns used for the entry are A through C. Let's also further assume that the first row of available data on worksheets 2 and 3 would be row 7 Dim WS1 as Worksheet, WS2 as Worksheet, WS3 as Worksheet DIM FDR as Long, LDR as Long, I as Long, strType as String Dim WR2 as Long, WR3 as Long 'This is to be used to keep track of what row is available for pasting on worksheets 2 and 3. Set WS1 = Thisworkbook.Worksheets("Sheet1") Set WS2 = Thisworkbook.Worksheets("Sheet2") Set WS3 = Thisworkbook.Worksheets("Sheet3") FDR = 4 LDR = WS1.Range("A65536").End(xlUp).Row WR2 = WS2.Range("A65536").End(xlUp).Row WR3 = WS3.Range("A65536").End(xlUp).Row If WR2<7 Then WR2 = 7 End If If WR3<7 Then WR3 = 7 End If For I = FDR to LDR Step 1 strType = WS1.Range("B" & I).Text Select Case UCase(strType) Case "FRUIT" WS1.Range("A" & I & ":C" & I).Copy WS2.Paste(WS2.Range("A" & WR2)) WR2 = WR2 + 1 CutCopyMode = 0 Case "VEGETABLE" WS1.Range("A" & I & ":C" & I).Copy WS3.Paste(WS3.Range("A" & WR2)) WR3 = WR3 + 1 CutCopyMode = 0 End Select Next I -- Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Alan L. Wagoner" wrote in message ... I apologize for the newbie question, but I'm new to VBA/Excel and need some advice. Let me give a simple description of what I would like to do. The workbook would consist of 3 sheets. The first sheet would be the "data entry" sheet, with Sheets 2 and 3 being generated by some VBA code. This is done to minimize any data entry/user error. As an example, let's say the user enters in three pieces of information on the first sheet. He/she enters the name of a fruit or vegetable, then the category that it belongs in "fruit" or "vegetable", and finally the price. Example: apple fruit .50 potato vegetable .24 banana fruit .55 corn vegetable .28 The user would (ideally) click a button on the page, or run the VBA code which would iterate through all of the entries in sheet one. If it's a fruit, it would copy the entire row to Sheet 2. If it was a vegetable, it would copy the entire row to Sheet 3. After the code was run, Sheet 2 would look like: apple fruit .50 banana fruit .55 And Sheet 3 would be: potato vegetable .24 corn vegetable .28 The downside is that Sheets 2 and 3 have to be recalculated every time, but since there won't be a lot of data, it's OK. Any assistance or ideas would be appreciated. Thanks in advance, Alan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and multiple sheets
Great suggestions all.
Thanks for the input. --Alan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to update data from multiple sheets to one specific sheets | Excel Discussion (Misc queries) | |||
export multiple sheets to multiple excel files | Excel Discussion (Misc queries) | |||
SUMPRODUCT, SUMIF, COUNTIF for multiple sheets for multiple criter | Excel Worksheet Functions | |||
Automated multiple text files into multiple sheets in one workbook | Excel Discussion (Misc queries) | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions |