Copy Insert Macro

S

Schwimms

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

Joel

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

Schwimms

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

Joel

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
 
S

Schwimms

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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
 
J

Joel

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
 
S

Schwimms

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?
 
J

Joel

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

Schwimms

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

Joel

Use this code instead of autofilter

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

Schwimms

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

Schwimms

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?
 
J

Joel

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
 
S

Schwimms

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!"
 
J

Joel

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top