I hope I can explain this.

My macro takes a record list that starts on A9 of one sheet and pastes it to
another sheet. The record list goes from column A-H w/varying number of
rows. I need the macro to copy the blank cells in columns I-N because I am
pasting this above a separate record list that goes from column A-N. I
thought by doing "Range(Selection, Selection.End(xlToRight)).Select" twice it
would always copy & insert the blank cells, but it doesn't work.

When I run the macro it ignores the blank column I-N cells & the secondary
record list becomes separated. Columns A-H are moved below the inserted
cells, but columns I-N stay in their original place. What am I doing wrong?


Range("A9").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet1").Select
Range("A8").Select
Selection.Insert Shift:=xlDown

hi,
i wouldn't use .xl(direction) for blank rows in this case.
try something like this instead.

Dim r As Range
Dim c As Long
Set r = Range("A9")
Range(r, r.End(xlDown)).Select ' for varing number of rows
c = Selection.Rows.Count
r.Resize(c, 14).Select 'from A to N = 14 columns
Selection.Copy
more code

accually you don't need to select.....

Dim r As Range
Dim c As Long
Set r = Range("A9")
c = Range(r, r.End(xlDown)).rows.count
r.Resize(c, 14).copy
more code

regards
FSt1


"Munchkin" wrote:

I hope I can explain this.

My macro takes a record list that starts on A9 of one sheet and pastes it to
another sheet. The record list goes from column A-H w/varying number of
rows. I need the macro to copy the blank cells in columns I-N because I am
pasting this above a separate record list that goes from column A-N. I
thought by doing "Range(Selection, Selection.End(xlToRight)).Select" twice it
would always copy & insert the blank cells, but it doesn't work.

When I run the macro it ignores the blank column I-N cells & the secondary
record list becomes separated. Columns A-H are moved below the inserted
cells, but columns I-N stay in their original place. What am I doing wrong?


Range("A9").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet1").Select
Range("A8").Select
Selection.Insert Shift:=xlDown