Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut from one sheet, paste in another
Greetings folks. I need to cut the active row from one sheet, and paste it
into the first blank row in another sheet. So far this is what I have, and I am not sure how to do it. I can activate the target row, but I am unable to paste into it. Any ideas? Thank you. Dim WS As Worksheet Dim LastRow As Integer 'Cut the active row from the present sheet ActiveCell.EntireRow.Cut 'Set the target sheet Set WS = ActiveWorkbook.Sheets!held 'Set start row of the destination sheet LastRow = 8 With WS 'Get the first blank row on destination sheet For i = 8 To 300 Step 1 If Len(.Cells(i, 4).Value & "") 0 Then LastRow = LastRow + 1 End If Next i 'Need to paste the row that was cut from the previous sheet 'This is where I am getting errors. .Cells(LastRow, 1).Paste End With 'WS.Activate Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut from one sheet, paste in another
Hi Greg,
For some reason when using Cut, Paste does not work unless you select the worksheet then the cell and use ActiveSheet.Paste. Try the following. ..Cells(LastRow, 1).EntireRow.Insert Shift:=xlDown Also the method you use to find the LastRow. Is it because you are looking for the first available row and there is other data below it or is it the row below all other data? If the former then leave as is but if the latter, the following finds the next blank row on a worksheet. With WS LastRow = .Cells(.Rows.Count, 4).End(xlUp).Offset(1, 0).Row -- Regards, OssieMac "Greg Snidow" wrote: Greetings folks. I need to cut the active row from one sheet, and paste it into the first blank row in another sheet. So far this is what I have, and I am not sure how to do it. I can activate the target row, but I am unable to paste into it. Any ideas? Thank you. Dim WS As Worksheet Dim LastRow As Integer 'Cut the active row from the present sheet ActiveCell.EntireRow.Cut 'Set the target sheet Set WS = ActiveWorkbook.Sheets!held 'Set start row of the destination sheet LastRow = 8 With WS 'Get the first blank row on destination sheet For i = 8 To 300 Step 1 If Len(.Cells(i, 4).Value & "") 0 Then LastRow = LastRow + 1 End If Next i 'Need to paste the row that was cut from the previous sheet 'This is where I am getting errors. .Cells(LastRow, 1).Paste End With 'WS.Activate Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut from one sheet, paste in another
That's a good tip about the paste not working with cut unless the destination
is not selected. The user has some validation lists in the columns starting at row 300, so I need to find the first blank row after row 8, but before row 300, thus the method of finding the blank row. "OssieMac" wrote: Hi Greg, For some reason when using Cut, Paste does not work unless you select the worksheet then the cell and use ActiveSheet.Paste. Try the following. .Cells(LastRow, 1).EntireRow.Insert Shift:=xlDown Also the method you use to find the LastRow. Is it because you are looking for the first available row and there is other data below it or is it the row below all other data? If the former then leave as is but if the latter, the following finds the next blank row on a worksheet. With WS LastRow = .Cells(.Rows.Count, 4).End(xlUp).Offset(1, 0).Row -- Regards, OssieMac "Greg Snidow" wrote: Greetings folks. I need to cut the active row from one sheet, and paste it into the first blank row in another sheet. So far this is what I have, and I am not sure how to do it. I can activate the target row, but I am unable to paste into it. Any ideas? Thank you. Dim WS As Worksheet Dim LastRow As Integer 'Cut the active row from the present sheet ActiveCell.EntireRow.Cut 'Set the target sheet Set WS = ActiveWorkbook.Sheets!held 'Set start row of the destination sheet LastRow = 8 With WS 'Get the first blank row on destination sheet For i = 8 To 300 Step 1 If Len(.Cells(i, 4).Value & "") 0 Then LastRow = LastRow + 1 End If Next i 'Need to paste the row that was cut from the previous sheet 'This is where I am getting errors. .Cells(LastRow, 1).Paste End With 'WS.Activate Greg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut from one sheet, paste in another
Hi again Greg,
Because you say that there is other data from row 300 then the method I gave you will continually push that data down. Therefore you need to delete a row each time you insert one. Note you can still use a similar method to the one I posted for finding the next blank row by replacing '.Rows.Count' with 300. 'Find next blank row LastRow = .Cells(300, 4).End(xlUp).Offset(1, 0).Row 'Inserts data but pushes all other data down. ..Cells(LastRow, 1).EntireRow.Insert Shift:=xlDown 'Deletes the row below inserted data ..Cells(300, 4).End(xlUp).Offset(1, 0).EntireRow.Delete -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
loop through the workbook and paste every sheet it into newly createdworkbook with same sheet name | Excel Programming | |||
Line count in sheet determines paste range in another sheet | Excel Programming | |||
Line count in 1 sheet to determine paste range in 2nd sheet. | Excel Programming | |||
Copy Paste from Class Sheet to Filtered List on Combined Sheet | Excel Programming | |||
Help to code Macro to Copy fron one sheet and paste in other sheet | Excel Programming |