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
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