VBA Move specific rows to unique new workbooks

VBA Move specific rows to unique new workbooks

automatically move entire rows from one worksheet into another worksheet in the same workbook
vba to copy and paste rows if condition is met
excel vba copy rows to another worksheet based on criteria
excel vba autofilter copy visible rows
copy auto filtered data to another worksheet automatically with vba
excel automatically move row to another sheet
excel move row to another sheet based on cell value automatically
excel copy rows from one sheet to another based on criteria

This is probably really easy for someone with better knowledge of VBA than me. I can see how to do this if it's a case of copying rows into a new worksheet but haven't figured out how to get it to move to the next row once it has copied the first one that meets the condition. I know I need to tell it to look for the next row but I'm having trouble finding the right command.

I'm looking to get the macro to cycle through my named range of data and copy one row that meets the condition at a time to a new unique workbook. For example, I have 10 records that meet the criteria, I want an output of 10 workbooks with one row of data in each.

Code so far is:

Sub DPD()

Dim Ws As Worksheet
Dim Items As Range
Dim Item As Range

Set Ws = Worksheets("Out")
Set Items = Ws.Range("MyRange")

For Each Item In Items

Application.DisplayAlerts = False

'If value in column C > 0, copy row to new workbook and save
If Item.Value > 0 Then

    'Select row in active Sheet to copy
    Item.EntireRow.Copy

    'Paste row into new spreadsheet
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    ChDir "C:\DPD"
    ActiveWorkbook.SaveAs Filename:="C:\DPD\pf_" & Format(CStr(Now), "yyy_mm_dd_hh_mm") & ".csv", FileFormat:=xlCSV
    ActiveWindow.Close

    Application.DisplayAlerts = True


End If

Next Item

Link to Example sheet


As per my suggestion, try the below UDF

Sub DPD()

    ' Variable Declarations
    Dim rItem As Range
    Dim oNewWB As Workbook
    Dim oNewWS As Worksheet
    Dim iCounter As Long: iCounter = 0

    ' Switch updates off
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    ' Loop through all rows in the range
    For Each rItem In Range("MyRange").Rows

        ' Check if column C has a value greater than 0. NOTE: if the cell has text in it (i.e. header column will have text)
        '                                                     excel counts the number of characters in the cell.. which will
        '                                                     be greater than 0 so that line will be copied in a new workbook
        If rItem.Cells(1, 3).Value > 0 Then

            ' Create new workbook and get the first sheet
            Set oNewWB = Workbooks.Add
            Set oNewWS = oNewWB.Sheets(1)

            ' Copy current row
            rItem.Cells(1, 1).EntireRow.Copy

            ' Paste row in new worksheet
            oNewWS.Cells(1, 1).PasteSpecial Paste:=xlPasteValues

            ' Save new workbook and close it
            iCounter = iCounter + 1
            oNewWB.SaveAs Filename:="C:\DPD\pf_" & Format(CStr(Now), "yyy_mm_dd_hh_mm") & counter & ".csv", FileFormat:=xlCSV
            oNewWB.Close

        End If
    Next

    ' Turn on updates
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

How to move entire row to another sheet based on cell value in Excel?, Move entire row to another sheet based on cell value with VBA code UsedRange.Rows.Count. J = Worksheets( "Sheet2" ).UsedRange.Rows. Press the Ctrl + C keys to copy the selected rows, and then paste them into the destination then copy that row but only columns DE:FN to a new sheet in another new workbook 1. Press Alt+ F11 keys simultaneously to open the Microsoft Visual Basic for Applications window. 2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the below VBA code into the window. VBA code 1: Move entire row to another sheet based on cell value


As I've mentioned in comment, the problem is in your naming. Here is refactored code for you. Check it, because I removed some stuff and moved other one.

Sub DPD()

Dim Items As range
Dim Item As range

'Dim WS As Worksheet
Dim newWS As Worksheet
Dim counter As Long

'Set WS = ThisWorkbook.Sheets("sheet_name") ' try avoiding ActiveWorkbook/Sheet
'Set Items = WS.range("MyRange") ' - this is not necessary if you already have a named range

Application.DisplayAlerts = False ' thats right to switch off notifications
Application.ScreenUpdating = False ' but another good idea is to switch off screen update - this will allow vba to work much faster and you won't see blinking display

For Each Item In Range("MyRange") ' here's where you may use your named range directly

'--------------------------------------------------------------------------
' As per @Zac's comment:    
'For Each Item In Items.Rows ' should change the For loop condition
'If Item.Cells(1, 1).Value > 0 Then ' and also update an If statement
'--------------------------------------------------------------------------


'If value in column C > 0, copy row to new workbook and save
    If Item.Value > 0 Then
        Workbooks.Add
        Set newWS = ActiveSheet ' Here is the place where I can't avoid using "ActiveSheet"
        'Select row in active Sheet to copy
        Item.EntireRow.Copy

 'ChDir "C:\DPD" ' no need to change default directory, as you are providing full file name below ↓

        'Paste row into new spreadsheet
        With newWS
            .Cells(1, 1).PasteSpecial Paste:=xlPasteValues
            .Parent.SaveAs FileName:="C:\DPD\pf_" & Format(CStr(Now), "yyy_mm_dd_hh_mm") & counter & ".csv", FileFormat:=xlCSV
            .Parent.Close
        End With
        counter = counter + 1
    End If
Next Item

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Update

As per Zac's comment - there truly may be more than one column in range, so I've added his suggestion to my answer.

Excel VBA : Filtering and copy pasting to new sheet or workbook, Excel VBA : Filtering and copy pasting to new sheet or workbook. Suppose you Excel Macro : Filter and Paste Unique Values to New Sheets This macro Range("A1:F" & last) 'Specific columns which will be copied Set rng1 = Union(. It also writes 'Total' at last row of column A. It assumes your data starts from column A. I want to have all the column names (columns A through CM) and for the table design to remain the same. My current file has 58 unique values and over 8,000 rows. Later files will have many more unique values and thousands more rows. Please help!!! I have almost 0 experience with VBA - I am a SAS girl, so I need step-by-step instructions.


Your code basically works, the only problem is the files are created so fast that they are all created in the same second and

ActiveWorkbook.SaveAs Filename:="C:\DPD\pf_" & Format(CStr(Now), "yyy_mm_dd_hh_mm") & ".csv", FileFormat:=xlCSV

will not allow you to distinguish the files. So all your files will have the same time stamp and are overwritten. A simple solution is to add a counter

iCounter

that increments with every new file, like in this edited piece of code:

Sub DPD()

Dim Ws As Worksheet
Dim Items As Range
Dim Item As Range
Dim iCounter As Integer

Set Ws = Worksheets("Out")
Set Items = Ws.Range("MyRange")

For Each Item In Items

Application.DisplayAlerts = False

'If value in column C > 0, copy row to new workbook and save
If Item.Value > 0 Then

    'Select row in active Sheet to copy
    Item.EntireRow.Copy

    'Paste row into new spreadsheet
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    ChDir "C:\DPD"

    iCounter = iCounter + 1
    ActiveWorkbook.SaveAs Filename:="C:\DPD\pf_" & Format(CStr(Now), "yyy_mm_dd_hh_mm") & iCounter & ".csv", FileFormat:=xlCSV
    ActiveWindow.Close




End If

Next Item

Application.DisplayAlerts = True

End Sub

Please observe that I also moved the

Application.DisplayAlerts = True

out of the loop. Otherwise, screen updating will be turned on right after the first copy/paste.

Move or Copy Rows & Columns Using Excel VBA, Including Move to , This video tutorial will demonstrate how to move or copy rows and columns within a worksheet Duration: 7:21 Posted: Feb 23, 2018 Filter value then copy to new workbook. Instead of copying data to new worksheet, you may want to copy to new workbook. Previously I wrote a post about copying each worksheet to new workbook, you just need to run the above Macro, and then run the copy to new workbook Macro. Click here to read my previous post.


Create a new sheet for all Unique values, Copy records with the same value in a column to a new sheet or workbook all rows with the same value in the first column of the range to a new worksheet. Delete or add columns as desired and rename the column headings to suit your own data. The drop down menu will automatically account for the new column headings. Set your save path accordingly, this is where the VBA will save all the new data files it creates.


Copy records with the same value in a column to a new sheet or , Copy records with the same value in a column to a new sheet or workbook see the macro examples on this page :VBA code examples for a List or Table in Excel 1) Use headers in the first row of your data (1 Header row) the unique list and not use xlFilterCopy to filter and copy the data to a new sheet or workbook. Copy Data from One Workbook to Another Using Excel Macros. There are a few ways to copy & paste data with VBA. We are first going to use the Range.Copy method. This allows us to perform the entire action in one line of code. Workbooks("New Data.xlsx").Worksheets("Export").Range("A2:D9").Copy _ Workbooks("Reports.xlsm").Worksheets("Data").Range("A2")


How To Export Your Data Into Separate Workbooks Based On The , Set your save path accordingly, this is where the VBA will save all the new Clear 'Loop through our array of unique field values, copy paste into new workbooks and Rows.Count, 6).End(xlUp).Row + 1: Stp = ws.Cells(ws.Rows. Count /transfer-data-multiple-workbooks-master-workbook-automatically/. 'Workbook where VBA code resides Set Workbk = ThisWorkbook 'New Workbook Set newBook = Workbooks.Add(xlWBATWorksheet) Workbk.Activate 'change filter column in the following code last = Workbk.Sheets(sht).Cells(Rows.Count, "F").End(xlUp).Row With Workbk.Sheets(sht) Set rng = .Range("A15:F" & last) End With