Copy Rows from Master List to different Sheets based on a Cell Value

kazamimi

New Member
Joined
Jun 30, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hello, I have a workbook with a few sheets: All Forms, Form A, Form B, Form C, and Form D.

I want a macro that will search column C on the All Forms Sheet, and depending on the Value listed there, Copy the entire row to the relative Sheet.

So, if Row 1 on the All Forms Sheet has “Form B” in Column C, it will copy that entire row to the Form B sheet…if Row 2 says “Form A” in Column C, it will copy that entire Row to the Form A sheet.

I would like to create a button that runs the macro, so that when I click it, it will overwrite the existing rows on the various sheets-effectively updating those sheets with the most current info from the All Forms sheet (the “master sheet”) without creating duplicate entries every time I run the macro…
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

kazamimi

New Member
Joined
Jun 30, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Additional info: there are about 300 entries on the All Forms sheet, with about 30-50 Rows that need to be copied to each of the relative sheets (Form A, Form B, etc.)
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,176
Office Version
  1. 365
Platform
  1. Windows
Hello Kazamimi,

Try the following:-

VBA Code:
Option Explicit
Sub Test()

        Dim ar As Variant, wsAF As Worksheet, wsD As Worksheet, i As Long
        
        Set wsAF = Sheets("All Forms")
        ar = Array("Form A", "Form B", "Form C", "Form D")

Application.ScreenUpdating = False

        For i = 0 To UBound(ar)
        Set wsD = Sheets(ar(i))
        wsD.UsedRange.Offset(1).Clear
                With wsAF.[A1].CurrentRegion
                        .AutoFilter 3, ar(i)
                        .Offset(1).EntireRow.Copy wsD.Range("A" & Rows.Count).End(3)(2)
                        .AutoFilter
                End With
        Next i

Application.ScreenUpdating = True

End Sub

As I don't know the set out of your worksheets, I'm assuming that the All Forms sheet has headings in row1 with data starting in row2.

I hope that this helps.

Cheerio,
vcoolio.
 

Forum statistics

Threads
1,141,704
Messages
5,707,966
Members
421,538
Latest member
Krisco

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
Top