VBA and multiple sheets

[Alan L. Wagoner]

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

 

[Ronald Dodge]

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

 

[Tom Ogilvy]

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

 

[Alan L. Wagoner]

Great suggestions all.

Thanks for the input.

--Alan