Help with simple cut and paste macro

dvuppx

New Member
Joined
Aug 31, 2017
Messages
12
i have a document which has a lot of information in sheet1, column a right up to column BI. i would like to be able to tick a box in column BI that then removes the row from sheet 1 and puts it into sheet 2, and adds a date. this is my formula so far, but im getting stuck at the text highlighted in BOLD, as it says my subscript is out of range. i have no idea how to fix that.




Sub Cutrows()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = ActiveSheet
Set WS2 = Worksheets("Sheet2")


firstrow = Selection.Rows(1).Row
RowCount = Selection.Rows.Count


NEXTROW = WS9.Cells(Rows.Count, 1).And(xlUp).Row + 1


WS1.Cells(firstrow, 1).Resize(RowCount, 25).Copy Destination:=WS2.Cells(NEXTROW, 1)


WS2.Cells(NEXTROW, 26).Resize(RowCount, 1).Value = Date


WS1.Cells(firstrow, 1).Resize(RowCount, 25).Delete shift:=xlUp
End Sub
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi & welcome to the board.
Do you have a sheet called Sheet2 ?
 
Upvote 0
Make Sheet2 the active sheet & run this
Code:
Sub ShtName()
MsgBox "|" & ActiveSheet.Name & "|"
End Sub
What do you get?
 
Upvote 0
Then change
Code:
Set WS2 = Worksheets("Sheet2")
to
Code:
Set WS2 = Worksheets("Discharge")
 
Upvote 0
okay great thank you. so now when i run it it says object does not support this property or method, and it is highlighting here:

Sub Cutrows()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = ActiveSheet
Set WS2 = Worksheets("Discharge")


firstrow = Selection.Rows(1).Row
RowCount = Selection.Rows.Count


NEXTROW = WS2.Cells(Rows.Count, 1).And(xlUp).Row + 1


WS1.Cells(firstrow, 1).Resize(RowCount, 25).Copy Destination:=WS2.Cells(NEXTROW, 1)


WS2.Cells(NEXTROW, 26).Resize(RowCount, 1).Value = Date


WS1.Cells(firstrow, 1).Resize(RowCount, 25).Delete shift:=xlUp
End Sub
 
Upvote 0
it should be .End(xlup) not .And
 
Upvote 0
okay so i worked out that i didnt have enough row count so i have altered that. #

Sub Cutrows()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = ActiveSheet
Set WS2 = Worksheets("Discharge")


firstrow = Selection.Rows(1).Row
RowCount = Selection.Rows.Count


NEXTROW = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1


WS1.Cells(firstrow, 1).Resize(RowCount, 54).Copy Destination:=WS2.Cells(NEXTROW, 1)


WS2.Cells(NEXTROW, 26).Resize(RowCount, 1).Value = Date


WS1.Cells(firstrow, 1).Resize(RowCount, 54).Delete shift:=xlUp
End Sub

thank you so everything seems to be going lovely there and it doesnt have any bugs. what im struggling with now (super sorry,this is literally my first macro) is actually getting that into sheet 1. how would i do this? every time i try something it ends up moving the first row automatically. the point of this is supposed to be so that it doesnt move it unless i tell it to. what am i missing?
 
Upvote 0
Could you please explain exactly what you are trying do?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top