Copy filtered data from column to a fixed location

[camylarde]

Hello,

I've been battling with following problem:

How to copy filtered data from a column into a different sheet using excel vba macro?

The in depth problem is this:
Column D is fed with a formula, the formula is then copypasted as values.

Range("A1").Select
    ActiveSheetRowCount = RowCount  'function to count rows using search approach ((found on a different website)) )
    Range("d3").Select
    ActiveCell.FormulaR1C1 = "=IF(ISNA(MATCH(RC[-3],'Ship to'!R2C1:R" & CStr(SheetShipToRows) & "C1 ,0)),"""",RC[-3])"
    Range("d3").Select
    Selection.Copy
    Range(Cells(3, 4), Cells(ActiveSheetRowCount, 4)).Select
    ActiveSheet.Paste
    Range(Cells(3, 4), Cells(ActiveSheetRowCount, 4)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Then the filtering is applied to show nonblanks only

Selection.AutoFilter Field:=4, Criteria1:="<>"

Then I try to select the filtered data

CopyRange = ActiveSheet.UsedRange.Range(Cells(3, 3), Cells(4,ActiveSheet.UsedRange.Rows.Count))

and get debug error.
Run-time error '1004': Application-defined or object-defined error

This is the code that i expect to work after the CopyRange is fed with data.

    FilteredRange = CopyRange.SpecialCells(x1CellTypeVisible)
    FilteredRange.Copy Destination:=Sheets("Prepaid Payer").Range("A1")

I have the variables defined as

Dim ActiveSheetRowCount As Long
Dim SheetShipToRows As Long
Dim CopyRange As Range
Dim FilteredRange As Range

This all code is a desperate compilation from various sources, I kinda am still battling with the object approach to selecting stuff i want. So any help is appreciated, and if you see a fundamental misunderstanding of how this works on my part, please drop a note with explanations what and why do I do wrong.

Thanks a million!

 

[MarkK]

Not sure if this is it, but when you assign an object to an object variable you have to use the Set keyword, so ...

dim xlapp as new excel.application
dim o as object
[COLOR="Green"]'set keyword is required for object assignments[/COLOR]
set o = xlapp

 

[camylarde]

I am not sure exactly what you ment, but I've tried to add set into the command line, with no results.

I've narrowed the problem rather to something more fundamental than the syntax itself. Instead of my selection code, i simulated only a portion of it, and still got error after:

    Sheets("PY").Select
    ActiveSheet.UsedRange.Select
    ActiveSheetRowCount = Selection.Rows.Count
    Application.CutCopyMode = False
    Range(Cells(3, 3), Cells(4, 43243)).Select

UPDATE: And now it hit me, wrong order of variables. Rows first, columns second in the Cells parameters. Who came with that?? X axis always goes first before the Y one.... anyway, looks like problem solved.

When I have more time, ill post a polished code to allow others to benefit from it as well.