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

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

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

VBA and multiple sheets
 

Great suggestions all.

Thanks for the input.

--Alan