| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| aconcat UDF throwing a #NAME error | Excel Worksheet Functions | |||
| Copy and paste versus copy and insert copied cells | New Users to Excel | |||
| How to insert code | Excel Discussion (Misc queries) | |||
| Throwing Out top & lower percentages | Excel Worksheet Functions | |||
| Insert Code | New Users to Excel | |||
Hybrid Mode
