Copy/transfer rows to multiple worksheets based on a condition

Neoc98

New Member
Joined
Aug 18, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi,

I have a similar issue to the one in this thread: <VBA> Copy rows to multiple worksheets based on a condition
Only I have a different condition: I don't want it to copy the same rows from the master sheet and paste it at the first blank row.
So either it has to:

  • Not copy over the same identical rows from the master that are already exist in the sheet
  • Just copy over all cells over entire range (expect for the first row as it functions as the header) and overwrite all previous data
  • Delete master sheet data so that old values don't get copied over (not preferred)

Option 1 seems the best. Because of my (very) limited skills of VBA I can't seem to understand the macro and to make the right adjustments according to my needs.
If someone is willing to help me I would be very grateful. I'd be happy to clarify if more details are needed.

Thanks!

VBA Code:
ub Knop4_Klikken()

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("Input")
cflr = copyfromws.Cells(Rows.Count, "B").End(xlUp).Row
    
'   Copy Row of Data to Specific Worksheet based on value in Column E
'   Existing Formulas in Columns F through H or J are automatically extended to the new row of data
For i = 2 To cflr
    currval = copyfromws.Cells(i, 10).Value
    Set copytows = Sheets(currval)
    ctlr = copytows.Cells(Rows.Count, "B").End(xlUp).Row + 1
    Set cfrng = copyfromws.Range("A" & i & ":L" & i)
    Set ctrng = copytows.Range("A" & ctlr & ":L" & ctlr)
    ctrng.Value = cfrng.Value

    
Next
End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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