Copy rows between sheets-conditional
 

I would like to copy each row of sheet "Raw Data", colunms A-N, that has a
"T" in column 'R' onto a template on another sheet. This other sheet is
titled "Input Table", and I would copy into these same columns.

The number of rows in "Raw Data" could be up to 25,000 rows, and "Input
Table" maxed at 10,000.

Does anyone have recommendations for a reasonably efficient way to do this?
The "Input Table" drives a number of pivot tables and graphs. Ideally, I'd
be able to delete the "Raw Data" content after it copies over the appropriate
rows.

Thank you in advance for any help!

Copy rows between sheets-conditional
 

Hi William

There will be more efficient ways to do this but this certainly works and
takes 1 min, 43 seconds for 28000 source rows and 10,000 "to copy" rows.

Regards

David

=========

Sub test()
Dim srcrow As Integer
Dim dstrow As Integer
Application.ScreenUpdating = False

srcrow = 1
dstrow = 1
While (Sheets("raw data").Range("A" & srcrow).Value < "")
If (Sheets("raw data").Range("R" & srcrow).Value = "T") Then
Range("A" & srcrow & ":N" & srcrow).Copy
Sheets("input table").Select
Range("A" & dstrow).Select
ActiveSheet.Paste
Sheets("raw data").Select
dstrow = dstrow + 1
End If
srcrow = srcrow + 1
Wend

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


"William Elerding" wrote in
message ...
I would like to copy each row of sheet "Raw Data", colunms A-N, that has a
"T" in column 'R' onto a template on another sheet. This other sheet is
titled "Input Table", and I would copy into these same columns.

The number of rows in "Raw Data" could be up to 25,000 rows, and "Input
Table" maxed at 10,000.

Does anyone have recommendations for a reasonably efficient way to do
this?
The "Input Table" drives a number of pivot tables and graphs. Ideally,
I'd
be able to delete the "Raw Data" content after it copies over the
appropriate
rows.

Thank you in advance for any help!

Copy rows between sheets-conditional
 

Maybe you could apply Data|Filter|autofilter to column R to show just the values
T.

Then copy those visible rows to the other sheet, then delete the visible rows,
and remove the filter.

If you need a macro, you could record one when you do it manually.

William Elerding wrote:

I would like to copy each row of sheet "Raw Data", colunms A-N, that has a
"T" in column 'R' onto a template on another sheet. This other sheet is
titled "Input Table", and I would copy into these same columns.

The number of rows in "Raw Data" could be up to 25,000 rows, and "Input
Table" maxed at 10,000.

Does anyone have recommendations for a reasonably efficient way to do this?
The "Input Table" drives a number of pivot tables and graphs. Ideally, I'd
be able to delete the "Raw Data" content after it copies over the appropriate
rows.

Thank you in advance for any help!

--

Dave Peterson

Copy rows between sheets-conditional
 

Thanks, David. I have copied it over to the worksheet, but it took 15
minutes, and didn't work. I'll try playing with it some more. I really
appreciate the help!

"David Coleman" wrote:

Hi William

There will be more efficient ways to do this but this certainly works and
takes 1 min, 43 seconds for 28000 source rows and 10,000 "to copy" rows.

Regards

David

=========

Sub test()
Dim srcrow As Integer
Dim dstrow As Integer
Application.ScreenUpdating = False

srcrow = 1
dstrow = 1
While (Sheets("raw data").Range("A" & srcrow).Value < "")
If (Sheets("raw data").Range("R" & srcrow).Value = "T") Then
Range("A" & srcrow & ":N" & srcrow).Copy
Sheets("input table").Select
Range("A" & dstrow).Select
ActiveSheet.Paste
Sheets("raw data").Select
dstrow = dstrow + 1
End If
srcrow = srcrow + 1
Wend

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


"William Elerding" wrote in
message ...
I would like to copy each row of sheet "Raw Data", colunms A-N, that has a
"T" in column 'R' onto a template on another sheet. This other sheet is
titled "Input Table", and I would copy into these same columns.

The number of rows in "Raw Data" could be up to 25,000 rows, and "Input
Table" maxed at 10,000.

Does anyone have recommendations for a reasonably efficient way to do
this?
The "Input Table" drives a number of pivot tables and graphs. Ideally,
I'd
be able to delete the "Raw Data" content after it copies over the
appropriate
rows.

Thank you in advance for any help!

Copy rows between sheets-conditional
 

Good morning, Dave. If I was the only one using this worksheet, that would
be the best course. Unfortunately, I have 12-14 managers that need to use
this worksheet. I'm trying to set-up a process that minimizes their time and
knowledge of Excel. Any thoughts?

BTW, I had protected column 'R' of the "Raw File" worksheet, as this
calculates off the imported data. I hope this doesn't cause a problem. Tx.

I'm not sure if I thanked you for some advice you gave me last year. A
response you gave me saved many, many hours of work. I was in a mental
block, and your advice turned the light on. Thanks!




"Dave Peterson" wrote:

Maybe you could apply Data|Filter|autofilter to column R to show just the values
T.

Then copy those visible rows to the other sheet, then delete the visible rows,
and remove the filter.

If you need a macro, you could record one when you do it manually.

William Elerding wrote:

I would like to copy each row of sheet "Raw Data", colunms A-N, that has a
"T" in column 'R' onto a template on another sheet. This other sheet is
titled "Input Table", and I would copy into these same columns.

The number of rows in "Raw Data" could be up to 25,000 rows, and "Input
Table" maxed at 10,000.

Does anyone have recommendations for a reasonably efficient way to do this?
The "Input Table" drives a number of pivot tables and graphs. Ideally, I'd
be able to delete the "Raw Data" content after it copies over the appropriate
rows.

Thank you in advance for any help!

--

Dave Peterson

Copy rows between sheets-conditional
 

My first thought is to keep the data in one worksheet and teach the managers how
to use the arrows on data|filter|autofilter.

By keeping the data in one location, life will get much easier. You won't end
up with one manager updating the wrong sheet and losing those changes (or even
worse--asking you to update the correct location with his changes. "Just check
cell by cell. It can't take more than a couple of hours for you to do it."
Yech!)

===
I'm not sure what your graphs do, but there's an option under:
tools|Options|chart tab
to plot visible cells only

And for pivottable stuff, I've sometimes used an indicator column (like your
column R) as a page field. Then I can choose to show only the data associated
with the T's.

===
But if you really, really need this on a separate sheet, I still think you could
record a macro when you do it once manually.

If you have to have the managers do it, then you could plop a button from the
Forms toolbar onto that worksheet--give it a nice caption ("Click her to extract
the T values") and assign your recorded macro to that button.

And her's hoping that this works as well as last year's suggestion <vbg.

William Elerding wrote:

Good morning, Dave. If I was the only one using this worksheet, that would
be the best course. Unfortunately, I have 12-14 managers that need to use
this worksheet. I'm trying to set-up a process that minimizes their time and
knowledge of Excel. Any thoughts?

BTW, I had protected column 'R' of the "Raw File" worksheet, as this
calculates off the imported data. I hope this doesn't cause a problem. Tx.

I'm not sure if I thanked you for some advice you gave me last year. A
response you gave me saved many, many hours of work. I was in a mental
block, and your advice turned the light on. Thanks!

"Dave Peterson" wrote:

Maybe you could apply Data|Filter|autofilter to column R to show just the values
T.

Then copy those visible rows to the other sheet, then delete the visible rows,
and remove the filter.

If you need a macro, you could record one when you do it manually.

William Elerding wrote:

I would like to copy each row of sheet "Raw Data", colunms A-N, that has a
"T" in column 'R' onto a template on another sheet. This other sheet is
titled "Input Table", and I would copy into these same columns.

The number of rows in "Raw Data" could be up to 25,000 rows, and "Input
Table" maxed at 10,000.

Does anyone have recommendations for a reasonably efficient way to do this?
The "Input Table" drives a number of pivot tables and graphs. Ideally, I'd
be able to delete the "Raw Data" content after it copies over the appropriate
rows.

Thank you in advance for any help!

--

Dave Peterson


--

Dave Peterson