Nevermind

End Function

End

Figured out my original question but now I have another...

Helping a client build 4 separate import templates from a single workbook. I was playing with the basic Record Macro function to filter the original data set and then copy and paste the appropriate data to the template. I got everything working with the existing data sets they have provided. Problem is when the data sets change (more rows, different order, etc...).

The issue as I see it is that when you filter you are no longer on row 2 at the top and when you copy the filtered data in the macro you get code that looks like...

Sheets("X").Select
ActiveSheet.Range("$A$1:$AG$55").AutoFilter Field:=19, Criteria1:="<>"
Range("D40").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Y").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

The next time you add try to run against a different data set your 1st cell on the filter may be D22 but your macro is set to start at D40

My questions are...

1. Is there a better way to accomplish what I'm trying to do?
2. How can I make the Range cell variable so it always selects the 1st cell after the column header when the data is filtered?
3. Sames as #2 but for pasting the copied data. If I am appending a template how can I make the Range cell be the 1st blank cell in a column?

You should never EVER use "select" in a macro.

Use the record function to help translate what you want to do into VBA, then replace all of the select statements using specified ranges.

You will have to be able to translate the criteria you use to choose which cell to click into VBA.

Ok. I'm new to macros and VBA so bear with me...

Using the example above, how would I modify the code to filter my data set and copy the filtered row from the 1st non-header to the last record?

Something like

.Range("D2" & .Sheet.Rows.Count).Copy Destination:= 'a range you want to copy into

Found something online that appears to be working. Changed my example above to...

Sheets("X").Select
ActiveSheet.Range("$A$1:$AG$55").AutoFilter Field:=19, Criteria1:="<>"
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, "D").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Y").Select
Dim ECDMtr As Long
ECDMtr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1
ActiveSheet.Cells(ECDMtr, "B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Replace any sheets().select followed by activesheet.something to sheets().something for one.

The reason to avoid "select" in macros is because an errant click at the wrong moment while you allow the macro to run "in the background" can cause tremendous bugs.

Every time a select call is made, your sheet is activated and takes focus. By coding the actions directly your Macro will not be open to that particular vulnerability.

Found 1 last issue I need to resolve...

On my data sheet I am trying to select data in a column to past on another sheet. I ran into 2 columns that are like...

Data
Data
Blank
Data
Blank
Blank
Data
Data

I would want all of those cells copied including the blanks.

My current code is...
Sheets("X").Select
ActiveSheet.Range("$A$1:$AD$9999").AutoFilter Field:=3, Criteria1:="<>"
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, "C").Select
Range(Selection, Selection.End(xlDown)).Select

This only pulls the 1st 2 cells. How can I tweak that to pull all of them?

Sheets("X").Range("$A$1:$AD$9999").AutoFilter Field:=3, Criteria1:="<>"
Sheets("X").AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, "C").Select
Range(Selection, Selection.End(xlUp)).Select


...maybe. I'm on my phone and don't quite get what you're looking for to create the range you want to copy. There is a way to ditch the remaining select statements.

If the range will always be the same, the just name it. For example, name it, "RANGE11". Then code would be...

Range("RANGE11").Copy

If the range of cells will change from time to time, then you could copy the column, create a starter cell and an end cell in that column. The end cell could be something like D999999.

Another way is to change your filter right before you copy.

You could always look for the last row used and set the range that way. I forget how to do it off the top of my head, but a quick search should turn it up. Excel stores the max used range as a part of reach sheet iirc.

Excel Polo!

ABATTBQ11 is correct in that based on how you are currently doing it the best method is to identify the last line and use a bottom up approach instead of a top down approach.