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

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

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

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

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

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

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

I'm not sure if the problem is with the copy or the insert.

Can you step through the code and change this line

from
newRange.Copy
to
newRange.Copy
newRange.select

You should be able to see which cells are being copied (the ones that are
highlighted).

"Schwimms" wrote:

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

Oh and one more thing, does that macro select columns A-H?

"Joel" wrote:

I'm not sure if the problem is with the copy or the insert.

Can you step through the code and change this line

from
newRange.Copy
to
newRange.Copy
newRange.select

You should be able to see which cells are being copied (the ones that are
highlighted).

"Schwimms" wrote:

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

It lost my response, I changed my macro and it still only inserts one cell,
so its something wrong with the insert.

"Joel" wrote:

I'm not sure if the problem is with the copy or the insert.

Can you step through the code and change this line

from
newRange.Copy
to
newRange.Copy
newRange.select

You should be able to see which cells are being copied (the ones that are
highlighted).

"Schwimms" wrote:

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

Use this code instead of autofilter

"Schwimms" wrote:

It lost my response, I changed my macro and it still only inserts one cell,
so its something wrong with the insert.

"Joel" wrote:

I'm not sure if the problem is with the copy or the insert.

Can you step through the code and change this line

from
newRange.Copy
to
newRange.Copy
newRange.select

You should be able to see which cells are being copied (the ones that are
highlighted).

"Schwimms" wrote:

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

Did you mean to give me a code? I have inputed the code that you have given
me but it did not work.

"Joel" wrote:

Use this code instead of autofilter

"Schwimms" wrote:

It lost my response, I changed my macro and it still only inserts one cell,
so its something wrong with the insert.

"Joel" wrote:

I'm not sure if the problem is with the copy or the insert.

Can you step through the code and change this line

from
newRange.Copy
to
newRange.Copy
newRange.select

You should be able to see which cells are being copied (the ones that are
highlighted).

"Schwimms" wrote:

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

Joel,

I would like to thank you again.

I was thinking that you may be able to understand me better if you input
data in a set of 4 columns and 10 rows (4x10). In the last column (D) there
should only be 2 numbers (1 and 2) in the cells d1-d10. Filter on the 1's in
column 4 and copy all the data in columns 1-3 down. take this data to another
spot and insert it.

Does this make sense?

"Schwimms" wrote:

Did you mean to give me a code? I have inputed the code that you have given
me but it did not work.

"Joel" wrote:

Use this code instead of autofilter

"Schwimms" wrote:

It lost my response, I changed my macro and it still only inserts one cell,
so its something wrong with the insert.

"Joel" wrote:

I'm not sure if the problem is with the copy or the insert.

Can you step through the code and change this line

from
newRange.Copy
to
newRange.Copy
newRange.select

You should be able to see which cells are being copied (the ones that are
highlighted).

"Schwimms" wrote:

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

Sub test10()

With Sheets("SFP")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = LastRow To 2 Step -1
If .Range("J" & RowCount) = "IN" Then

.Range("A" & RowCount & ":H" & RowCount).Copy
Sheets("IN").Range("B2").Insert Shift:=xlDown
End If
Next RowCount
End With
End Sub
"Schwimms" wrote:

Joel,

I would like to thank you again.

I was thinking that you may be able to understand me better if you input
data in a set of 4 columns and 10 rows (4x10). In the last column (D) there
should only be 2 numbers (1 and 2) in the cells d1-d10. Filter on the 1's in
column 4 and copy all the data in columns 1-3 down. take this data to another
spot and insert it.

Does this make sense?

"Schwimms" wrote:

Did you mean to give me a code? I have inputed the code that you have given
me but it did not work.

"Joel" wrote:

Use this code instead of autofilter

"Schwimms" wrote:

It lost my response, I changed my macro and it still only inserts one cell,
so its something wrong with the insert.

"Joel" wrote:

I'm not sure if the problem is with the copy or the insert.

Can you step through the code and change this line

from
newRange.Copy
to
newRange.Copy
newRange.select

You should be able to see which cells are being copied (the ones that are
highlighted).

"Schwimms" wrote:

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

You hit the spot!!!!!!!!

Although in column D i need it to select a value equaling 2 or greater.
So it has to have the 2 filters. The IN and the 2 need to be chosen.

I just want to tell you, "your AWESOME!"

"Joel" wrote:


Sub test10()

With Sheets("SFP")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = LastRow To 2 Step -1
If .Range("J" & RowCount) = "IN" Then

.Range("A" & RowCount & ":H" & RowCount).Copy
Sheets("IN").Range("B2").Insert Shift:=xlDown
End If
Next RowCount
End With
End Sub
"Schwimms" wrote:

Joel,

I would like to thank you again.

I was thinking that you may be able to understand me better if you input
data in a set of 4 columns and 10 rows (4x10). In the last column (D) there
should only be 2 numbers (1 and 2) in the cells d1-d10. Filter on the 1's in
column 4 and copy all the data in columns 1-3 down. take this data to another
spot and insert it.

Does this make sense?

"Schwimms" wrote:

Did you mean to give me a code? I have inputed the code that you have given
me but it did not work.

"Joel" wrote:

Use this code instead of autofilter

"Schwimms" wrote:

It lost my response, I changed my macro and it still only inserts one cell,
so its something wrong with the insert.

"Joel" wrote:

I'm not sure if the problem is with the copy or the insert.

Can you step through the code and change this line

from
newRange.Copy
to
newRange.Copy
newRange.select

You should be able to see which cells are being copied (the ones that are
highlighted).

"Schwimms" wrote:

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

I don't use filter so I'm a little rusty on the method. I try to respond
using the same methods people post unless they are totaly wrong. I thought
filte whould work, but I like my style of coding which is usually simplier to
understand and get working.


Sub test10()

With Sheets("SFP")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = LastRow To 2 Step -1
If .Range("J" & RowCount) = "IN" and _
.Range("D" & RowCount) = 2 Then

.Range("A" & RowCount & ":H" & RowCount).Copy
Sheets("IN").Range("B2").Insert Shift:=xlDown
End If
Next RowCount
End With
End Sub


"Schwimms" wrote:

You hit the spot!!!!!!!!

Although in column D i need it to select a value equaling 2 or greater.
So it has to have the 2 filters. The IN and the 2 need to be chosen.

I just want to tell you, "your AWESOME!"

"Joel" wrote:


Sub test10()

With Sheets("SFP")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = LastRow To 2 Step -1
If .Range("J" & RowCount) = "IN" Then

.Range("A" & RowCount & ":H" & RowCount).Copy
Sheets("IN").Range("B2").Insert Shift:=xlDown
End If
Next RowCount
End With
End Sub
"Schwimms" wrote:

Joel,

I would like to thank you again.

I was thinking that you may be able to understand me better if you input
data in a set of 4 columns and 10 rows (4x10). In the last column (D) there
should only be 2 numbers (1 and 2) in the cells d1-d10. Filter on the 1's in
column 4 and copy all the data in columns 1-3 down. take this data to another
spot and insert it.

Does this make sense?

"Schwimms" wrote:

Did you mean to give me a code? I have inputed the code that you have given
me but it did not work.

"Joel" wrote:

Use this code instead of autofilter

"Schwimms" wrote:

It lost my response, I changed my macro and it still only inserts one cell,
so its something wrong with the insert.

"Joel" wrote:

I'm not sure if the problem is with the copy or the insert.

Can you step through the code and change this line

from
newRange.Copy
to
newRange.Copy
newRange.select

You should be able to see which cells are being copied (the ones that are
highlighted).

"Schwimms" wrote:

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