It still just insert a cell, it doesn't insert the copied data... Don't know
what up, do you have a link to a vba coding list?

"Joel" wrote:

Is this better?


Sub test10()

Sheets("SFP").Select
Selection.AutoFilter Field:=12, Criteria1:="IN"
addr = ActiveSheet.AutoFilter.Range.Address
Set addrRange = Range(addr)
Set newRange = addrRange.Resize(addrRange.Rows.Count - 1)
Set newRange = newRange.Offset(1, 0)
newRange.Copy

Sheets("IN").Select
Range("B2").Select
Selection.Insert Shift:=xlDown

End Sub


"Schwimms" wrote:

Joel,

The part that I think your missing is that the data is filtered, this is
what the whole script looks like:

Sheets("SFP").Select
Selection.AutoFilter Field:=12, Criteria1:="IN"
Range("A2:H2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("IN").Select
Range("B2").Select
Selection.Insert Shift:=xlDown

What that does is it inserts a cell in b2 and shifts everything else
down...I also necessarily don't want it to start at A2:H2, I would like it to
start at the first row ,after the filter is changed, that has data in it,
except for the header.

I would just like to thank you for helping me
also!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

"Joel" wrote:

Either
set CopyRange = Range(A100:H150)
with Sheets("IL")
LastRow = .Range("A195").End(xlDown).Row
CopyRange.Copy destination:=.Range("A" & LastRow)
end with

or
set CopyRange = Range(A100:H150)
with Sheets("IL")
LastRow = .Range("A195").End(xlDown).Row
.Range("A" & LastRow).Insert Shift:=xlDown
end with


LastRow = Range("A195").End(xlDown).Row
rows("195:" & LastRow).copy
Sheets("IL").Range("B2").Insert Shift:=xlDown


"Schwimms" wrote:

Joel,

Thats not exactly it... I think I can simplify this. I want it to copy data
in column A100:H150 and paste it right below the data that I have in the
other spreadsheet.

"Joel" wrote:

LastRow = Range("A195").End(xlDown).Row
rows("195:" & LastRow).copy
Sheets("IL").Range("B2").Insert Shift:=xlDown



"Schwimms" wrote:

HI!

Heres the issue:

I have a range of data in columns A-H. I have a macro that filters those
columns. So sometimes the Row is 5 sometimes 20 and sometimes 43, all random.
What I need the macro to do is the highest column to the last row that has
data in it, the data needs to be copied. Then I need it to insert into
another spreadsheet, same amount of columns but B-I.

This is what I have:

Range("A195:H195").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("IL").Select
Range("B2").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown

It only iserts 1 cell and moves all contents below b2 down 1 row, it also
only grabs row 195, not the highest row...