Copy rows between sheets-conditional

[William Elerding]

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!

 

[David Coleman]

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

 

[Dave Peterson]

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]

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!

 

[William Elerding]

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]

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>.