| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Insert Macro
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... |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Insert Macro
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... |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Insert Macro
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... |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Insert Macro
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... |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Insert Macro
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... |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Insert Macro
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... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to insert a row and copy down formulas from row above | Excel Discussion (Misc queries) | |||
| Macro - Insert/Copy | Excel Discussion (Misc queries) | |||
| Macro to Insert and copy entire row | Excel Discussion (Misc queries) | |||
| Macro that will Cut rows and then insert-copy or append | Excel Discussion (Misc queries) | |||
| Copy and insert cell info macro help | Excel Discussion (Misc queries) | |||
Hybrid Mode
