About Us

scott

thanks your great....

"Dave Peterson" wrote:

Try changing this line:

Set RngToCopy = .Resize(.Rows.Count - 1, 1).Offset(1, 0)

to

Set RngToCopy = .offset(1,-7).Resize(.Rows.Count - 1, 6)

Since it's filtering by CQ, the .offset(1,-7) takes you back to column CJ, but
down a row from the header.

The .resize(.rows.count-1,6) says to make the range 6 columns wide (CJ:CO), but
not include any extra row at the bottom.

Scott wrote:

This does filter up to the last cell but I would like to copy CJ16:CO16 down
as it currently copies CQ down. I would still like to have it look to CQ for
the last row and then copy CJ16:CO16 down based on the last CQ data. Then
copy it to the "B" sheet as it does to A3:F3 down. I know it is a little
confusing sorry, don't know much about this (if any).

Thank You

"Dave Peterson" wrote:

Oops. There's a bug in that code if there's only headers visible.

Use this instead:

Option Explicit
Sub testme03()

Dim FromWks As Worksheet
Dim ToWks As Worksheet
Dim RngToFilter As Range
Dim RngToCopy As Range

Set FromWks = Worksheets("A")
Set ToWks = Worksheets("B")

With FromWks
'remove any existing filter
.AutoFilterMode = False

Set RngToFilter = .Range("CQ15", .Cells(.Rows.Count, "CQ").End(xlUp))

RngToFilter.AutoFilter field:=1, Criteria1:="<"

If .AutoFilter.Range.Columns(1).Cells.SpecialCells(xl CellTypeVisible) _
.Cells.Count = 1 Then
'only the headers are visible, nothing to copy???
Else
With RngToFilter
'skip the header and resize to avoid an
'extra row at the bottom
Set RngToCopy = .Resize(.Rows.Count - 1, 1).Offset(1, 0)
End With

'in newer versions of excel (xl97 and higher), the default is to
'copy the visible cells
RngToCopy.Copy
ToWks.Range("a3").PasteSpecial Paste:=xlPasteValues

End If
End With

End Sub

Notice the .copy stuff has been moved into the else portion--where I know
there's data to copy.

Dave Peterson wrote:

<<snipped

--

Dave Peterson

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
to copy pivot table as new sheet with same format	pol	Excel Discussion (Misc queries)	3	May 5th 10 08:42 PM
copy same cell from different ascending sheet into a table?	Roog	Excel Worksheet Functions	4	February 8th 07 04:36 PM
Copy Records from Access Database table(more than 5 lakh records in this table) to Excel Sheet	divya	Excel Programming	1	October 26th 06 12:12 PM
I need to copy table from one sheet to anothe	Maya	Excel Worksheet Functions	2	March 3rd 05 06:22 PM
Copy Table to New Sheet	HamishM[_6_]	Excel Programming	0	February 2nd 04 10:51 PM

Menu

About Us