VBA Marco move rows based on cell value to sheet with same name

[potsy311]

I'm new here, but have been browsing post for a while now. I've found some posts that are similar to what I need, but not quite what I am looking for.

I have a file that has 22 sheets, all the data is in sheet1, and I need to move the rows (columns A-W) based on the value in column U. I also have formulas in column V and W. An example of the value in U would be 789, and want to move that row to sheet 789.

I tried the below but keep getting an error on bolded line. Any help would be much appreciated.

Option Explicit
Sub CopyDataToSheets()
Dim copyfromws As Worksheet
Dim copytows As Worksheet
Dim cfrng As Range
Dim ctrng As Range
Dim cflr As Long
Dim ctlr As Long
Dim i As Long
Dim currval As String


Set copyfromws = Sheets("Sheet1")
cflr = copyfromws.Cells(Rows.Count, "A").End(xlUp).Row

' Copy Row of Data to Specific Worksheet based on value in Column U
' Existing Formulas in Columns V through W are automatically extended to the new row of data
For i = 2 To cflr
currval = copyfromws.Cells(i, 5).Value
Set copytows = Sheets(currval)
ctlr = copytows.Cells(Rows.Count, "A").End(xlUp).Row + 1
Set cfrng = copyfromws.Range("A" & i & ":W" & i)
Set ctrng = copytows.Range("A" & ctlr & ":W" & ctlr)
ctrng.Value = cfrng.Value

Next


End Sub

 

[Fluff]

Is the name of the sheet to copy to in col U or col E? your code is looking at col E

 

[potsy311]

Is the name of the sheet to copy to in col U or col E? your code is looking at col E

name of the sheet is in col U. so i changed the (i, 5) to (i, 21). now i get an '1004' error on line ctrng.Value = cfrng.Value

 

[Fluff]

I would have expected those to be the other way round, although that doesn't explain the error.
What is the actual error message?

 

[potsy311]

I would have expected those to be the other way round, although that doesn't explain the error.
What is the actual error message?

"Application-defined or object-defined error"

 

[Fluff]

I have no idea why you get that error, the code works fine for me.
Is that your entire code, or have you left bits out?

 

[potsy311]

I have no idea why you get that error, the code works fine for me.
Is that your entire code, or have you left bits out?

nope that is the entire code. i tried deleting the module and re-adding it, and i get a "subscript out of range" error on line Set copytows = Sheets(currval)

 

[Fluff]

That error means you don't have a sheet name with the value of currval.

 

[potsy311]

That error means you don't have a sheet name with the value of currval.

ok i fixed that but now getting the "application-defined or object-defined error" again

 

[potsy311]

That error means you don't have a sheet name with the value of currval.

Fluff, thanks for the help. i had an error on the spreadsheet that i didnt notice and once i fixed that it worked.