I am wondering if someone may have an easy solution to help me create a
summary worksheet according to a special field identifier...

Here is the nuts and bolts:

I have a main worksheet with parts information:

A B C
1 Gear 1287 Gear for shaft A
2 Screw 22844 Screw for shaft A
3 Locking pin 2294 Locking pin for shaft B
4 Flange assy. 55499 Entire assembly for shaft A

Now what I would like to do is add a column and use something like an "x" in
it for the rows I want copied to a summary-like worksheet:

A B C
D
1 Gear 1287 Gear for shaft A
x
2 Screw 22844 Screw for shaft A
x
3 Locking pin 2294 Locking pin for shaft B
4 Flange assy. 55499 Entire assembly for shaft A
x

For this given range, I would like to build a summary worksheet with "x" as
the special identifier to copy it over. Does anyone have something like this
put back in their toolbox? I would be most appreciative.

Many thanks in advance...

Here is the only thing I have come up with so far, but I would like a better
solution to copy over only the fields with an "x". In other words, I would
like to see if anyone has a better solution...


'' this sub is a mod from dmjritchies deleterow by blank character in column
A
Sub DeleteRowsByChar(CharacterToDelete As String)
Dim Rng As Range, ix As Long
Set Rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For ix = Rng.Count To 1 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) =
CharacterToDelete Then
Rng.Item(ix).EntireRow.Delete
End If
Next

End Sub

Sub Macro1()
Dim main As String, Xs As String, Os As String

main = "Main"
Xs = "Xs"
Os = "Os"

Sheets.Add
ActiveSheet.Name = Xs
Sheets(main).Select
Cells.Select
Selection.Copy
Sheets(Xs).Select
Range("A1").Select
ActiveSheet.Paste
DeleteRowsByChar ("")
Range("A1").Select

Sheets.Add
ActiveSheet.Name = Os
Sheets(main).Select
Cells.Select
Selection.Copy
Sheets(Os).Select
Range("A1").Select
ActiveSheet.Paste
DeleteRowsByChar ("x")
Range("A1").Select

End Sub





Mark Ivey

I would try to keep the data in one worksheet.

You could still use the indicator column, but then use Data|filter|autofilter to
show (or hide) the rows you want.

I would think that this would make the summary much easier to implement and
change if/when there are updates.

But if you wanted, you could do the same thing (indicator column and
data|filter|autofilter) and then copy the visible rows to a new summary
worksheet.

Record a macro when you do it manually and you'll have the code.


Mark Ivey wrote:

I am wondering if someone may have an easy solution to help me create a
summary worksheet according to a special field identifier...

Here is the nuts and bolts:

I have a main worksheet with parts information:

A B C
1 Gear 1287 Gear for shaft A
2 Screw 22844 Screw for shaft A
3 Locking pin 2294 Locking pin for shaft B
4 Flange assy. 55499 Entire assembly for shaft A

Now what I would like to do is add a column and use something like an "x" in
it for the rows I want copied to a summary-like worksheet:

A B C
D
1 Gear 1287 Gear for shaft A
x
2 Screw 22844 Screw for shaft A
x
3 Locking pin 2294 Locking pin for shaft B
4 Flange assy. 55499 Entire assembly for shaft A
x

For this given range, I would like to build a summary worksheet with "x" as
the special identifier to copy it over. Does anyone have something like this
put back in their toolbox? I would be most appreciative.

Many thanks in advance...

Here is the only thing I have come up with so far, but I would like a better
solution to copy over only the fields with an "x". In other words, I would
like to see if anyone has a better solution...

'' this sub is a mod from dmjritchies deleterow by blank character in column
A
Sub DeleteRowsByChar(CharacterToDelete As String)
Dim Rng As Range, ix As Long
Set Rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For ix = Rng.Count To 1 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) =
CharacterToDelete Then
Rng.Item(ix).EntireRow.Delete
End If
Next

End Sub

Sub Macro1()
Dim main As String, Xs As String, Os As String

main = "Main"
Xs = "Xs"
Os = "Os"

Sheets.Add
ActiveSheet.Name = Xs
Sheets(main).Select
Cells.Select
Selection.Copy
Sheets(Xs).Select
Range("A1").Select
ActiveSheet.Paste
DeleteRowsByChar ("")
Range("A1").Select

Sheets.Add
ActiveSheet.Name = Os
Sheets(main).Select
Cells.Select
Selection.Copy
Sheets(Os).Select
Range("A1").Select
ActiveSheet.Paste
DeleteRowsByChar ("x")
Range("A1").Select

End Sub

Mark Ivey

--

Dave Peterson

That is actually a much cleaner approach. Thank you very much for the
advice...


Mark Ivey




"Dave Peterson" wrote in message
...
I would try to keep the data in one worksheet.

You could still use the indicator column, but then use
Data|filter|autofilter to
show (or hide) the rows you want.

I would think that this would make the summary much easier to implement
and
change if/when there are updates.

But if you wanted, you could do the same thing (indicator column and
data|filter|autofilter) and then copy the visible rows to a new summary
worksheet.

Record a macro when you do it manually and you'll have the code.


Mark Ivey wrote:

I am wondering if someone may have an easy solution to help me create a
summary worksheet according to a special field identifier...

Here is the nuts and bolts:

I have a main worksheet with parts information:

A B C
1 Gear 1287 Gear for shaft A
2 Screw 22844 Screw for shaft A
3 Locking pin 2294 Locking pin for shaft B
4 Flange assy. 55499 Entire assembly for shaft A

Now what I would like to do is add a column and use something like an "x"
in
it for the rows I want copied to a summary-like worksheet:

A B C
D
1 Gear 1287 Gear for shaft A
x
2 Screw 22844 Screw for shaft A
x
3 Locking pin 2294 Locking pin for shaft B
4 Flange assy. 55499 Entire assembly for shaft A
x

For this given range, I would like to build a summary worksheet with "x"
as
the special identifier to copy it over. Does anyone have something like
this
put back in their toolbox? I would be most appreciative.

Many thanks in advance...

Here is the only thing I have come up with so far, but I would like a
better
solution to copy over only the fields with an "x". In other words, I
would
like to see if anyone has a better solution...

'' this sub is a mod from dmjritchies deleterow by blank character in
column
A
Sub DeleteRowsByChar(CharacterToDelete As String)
Dim Rng As Range, ix As Long
Set Rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For ix = Rng.Count To 1 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) =
CharacterToDelete Then
Rng.Item(ix).EntireRow.Delete
End If
Next

End Sub

Sub Macro1()
Dim main As String, Xs As String, Os As String

main = "Main"
Xs = "Xs"
Os = "Os"

Sheets.Add
ActiveSheet.Name = Xs
Sheets(main).Select
Cells.Select
Selection.Copy
Sheets(Xs).Select
Range("A1").Select
ActiveSheet.Paste
DeleteRowsByChar ("")
Range("A1").Select

Sheets.Add
ActiveSheet.Name = Os
Sheets(main).Select
Cells.Select
Selection.Copy
Sheets(Os).Select
Range("A1").Select
ActiveSheet.Paste
DeleteRowsByChar ("x")
Range("A1").Select

End Sub

Mark Ivey

--

Dave Peterson