copy and insert throwing error- help in code req

[dee]

hi,
I have the following code which merges cells... but before merging, i am
taking 3 rows from sheet2 , copying it and putting it into my FTP sheet
.. but it throws error.. can any1 help me in the section "inserting 3 rows"..
merging is happening fine.. all i need is taht the 3 rows should be added
dynamically before merging... pls help in rectifying


i have given portion of teh entire code here

For i1 = 3 To e
If (ps <> Range("C" & i1)) Then

'' 'insertion 3 rows
Sheets("Sheet2").Select
Rows("1:3").Select
Selection.Copy
Sheets("FTP").Select
Range("E" & i1).Select
Selection.Insert Shift:=xlDown
ActiveWindow.ActivateNext

'' 'end of insertion of three rows

Range("B" & st & ":B" & i1 - 1).merge
Range("A" & st & ":A" & i1 - 1).merge
Range("C" & st & ":C" & i1 - 1).merge

ps = Range("C" & i1)
st = i1
ElseIf (ps = Range("C" & i1) And i1 <> 3) Then
Range("A" & i1) = ""
Range("B" & i1) = ""
Range("C" & i1) = ""
'Range("D" & i1) = ""


End If


Next i1

 

[Joel]

If you are copying a row you need to paste it into a row number not a cell

For i1 = 3 To e
If (ps <> Range("C" & i1)) Then

'' 'insertion 3 rows
Sheets("Sheet2").Rows("1:3").copy
Sheets("FTP").Rows(i1).insert
ActiveWindow.ActivateNext

'' 'end of insertion of three rows

Range("B" & st & ":B" & i1 - 1).merge
Range("A" & st & ":A" & i1 - 1).merge
Range("C" & st & ":C" & i1 - 1).merge

ps = Range("C" & i1)
st = i1
ElseIf (ps = Range("C" & i1) And i1 <> 3) Then
Range("A" & i1) = ""
Range("B" & i1) = ""
Range("C" & i1) = ""
'Range("D" & i1) = ""


End If


Next i1

 

[dee]

i tried but it throws me the following error :
run time error 1004. to prevent loss of data, cannot shift non blank cells
off the worksheet.
how can i rectify this problem?

 

[Joel]

Are you suppose to have data in row 65536?
Press Shift-CNTL and then down arrow to get to last Row.

When you insert a row and havve data at the last row it will give this error
message. You can copy the data into existing rows, or get rid of the data in
row 65536.

 

[dee]

The thing is taht im automating things...
in such a case i wont know how many rows will be there after the 3 iserted
rows
taht is for each name in column A , before merging all similar names .. i
insert the 3 rows and then merge it. so after each name i will have these 3
rows t be inserted automatically

 

[Joel]

Again, Why do you have data in row 65536? Is this a mistake? Most people
don't use that many rows. The insert will work if you don't fill the
worksheet to the last row. if you have that much data you need to split the
data into more than one worksheet.

 

[dee]

i don hav so much data.. data comes form sheet 1 and can vary to the number
of rows that gets occupied... max it wil take 1000 rows

 

[Joel]

The solution is to delete unused rows. They will show back up after
deleting, but it will remove all data from these rows

1) go to last row of data, for example row 1200
2) Click on row number to highlight entire row.
3) Press Shift-Cntl and then down arrrow which will highlight all rows from
1200 to 65536.
4) Press Delete to delte these rows.


Now re-run macro and see if the error disappears.

 

[Gord Dibben]

You will have to Save the workbook before Excel recognizes that the rows have
been deleted.

Then re-run the macro.


Gord Dibben MS Excel MVP