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

potsy311

New Member
Joined
Sep 17, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
 
Glad it's sorted & thanks for the feedback.
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,223,268
Messages
6,171,100
Members
452,379
Latest member
IainTru

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