Help with simple cut and paste macro

[dvuppx]

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

 

[Fluff]

Hi & welcome to the board.
Do you have a sheet called Sheet2 ?

 

[dvuppx]

Hello. thank you for replying. yes i do. i have 13 other sheets on the workbook.

 

[Fluff]

Make Sheet2 the active sheet & run this

Sub ShtName()
MsgBox "|" & ActiveSheet.Name & "|"
End Sub

What do you get?

 

[dvuppx]

it says |Discharge|

 

[Fluff]

Then change

Set WS2 = Worksheets("Sheet2")

to

Set WS2 = Worksheets("Discharge")

 

[dvuppx]

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

 

[Fluff]

it should be .End(xlup) not .And

 

[dvuppx]

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?

 

[Fluff]

Could you please explain exactly what you are trying do?