|
copy and insert throwing error- help in code req
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 |
copy and insert throwing error- help in code req
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" wrote: 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 |
copy and insert throwing error- help in code req
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" wrote: 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" wrote: 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 |
copy and insert throwing error- help in code req
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" wrote: 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" wrote: 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" wrote: 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 |
copy and insert throwing error- help in code req
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
copy and insert throwing error- help in code req
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
copy and insert throwing error- help in code req
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
copy and insert throwing error- help in code req
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. "dee" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
copy and insert throwing error- help in code req
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 On Thu, 6 Mar 2008 03:13:00 -0800, Joel wrote: 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. "dee" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
| All times are GMT +1. The time now is 05:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com