Copying data from master sheet to workbooks based on criteria

Question

The below code struggles at the final "paste section". It opens up the new spreadsheet which I want to paste in but instead pastes in the underlying sheet where the data already exists. Any ideas on how to get this into the new sheet?

Option Explicit

Sub newfiles()  
    Dim personname As Variant
    Dim workbookname As Variant
    Dim namerange As Integer
    Dim i As Integer
    Dim personame As Variant
    Dim k As Integer
    Dim l As Integer

    k = Range("A10000").End(xlUp).Row

    Range("N3:N" & k).Copy

    With Range("XFC1:XFC" & k)
        .PasteSpecial xlPasteAll
        .RemoveDuplicates Columns:=1, Header:=xlNo
    End With

    namerange = Range("XFC10000").End(xlUp).Row        

    For i = 1 To namerange
        personname = Range("XFC" & i).Value
        Workbooks.Add 
        workbookname = ActiveWorkbook.Name

        Windows("Test 1.xlsm").Activate
        Sheets("Sheet1").Select

        Cells.Copy
        Range("A1").Select
        Windows(workbookname).Activate
        Sheets("Sheet1").Select

        With Cells
            .PasteSpecial xlPasteAll
            .PasteSpecial xlPasteColumnWidths
        End With

Your problem lies within your use of `.Select` and `.Activate`. Instead you should learn yourself to explicitly reference workbooks and sheets by declaring them. You should read up on how to avoid using select. — Luuklag, Sep 11 '18 at 09:13
Possible duplicate of [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Luuklag, Sep 11 '18 at 09:13

Pᴇʜ · Accepted Answer · 2018-09-11T09:48:00.977

Some recommendations:

Don't use Variant unless you have to.
Use descriptive variable names (eg LastRow is more descriptive than k).
Don't use Integer for row counting variables. Excel has more rows than Integer can handle. It is recommended always to use Long instead of Integer in VBA.
Define a worksheet for every Range(), Cells(), etc. otherwise Excel cannot know in which worksheet the range is and it tries to guess the worksheet (which results in unpredictive behavior).
Set the new added workbook to a variable so you can access it easily later: Set wbNew = Workbooks.Add
Avoid using .Select or .Activate they are not needed to perform actions. Instead directly reference a worksheet/range.

So you probably need to modify the following code but it should give you an idea how to do it:

Option Explicit

Sub newfiles()
    Dim wsSrc As Worksheet 'source worksheet
    Set wsSrc = ThisWorkbook.Worksheets("Sheet1") 'define your worksheet name here

    Dim LastRowA As Long
    LastRowA = wsSrc.Cells(wsSrc.Rows.Count, "A").End(xlUp).Row

    wsSrc.Range("N3:N" & LastRowA).Copy

    With wsSrc.Range("XFC1:XFC" & LastRowA)
        .PasteSpecial xlPasteAll
        .RemoveDuplicates Columns:=1, Header:=xlNo
    End With

    Dim LastRowNameRange As Long
    LastRowNameRange = wsSrc.Cells(wsSrc.Rows.Count, "XFC").End(xlUp).Row


    Dim PersonName As String
    Dim wbNew As Workbook

    Dim iRow As Long
    For iRow = 1 To LastRowNameRange
        PersonName = wsSrc.Range("XFC" & iRow).Value 'note that you never use the PersonName variable

        Set wbNew = Workbooks.Add

        ThisWorkbook.Worksheets("Sheet1").Cells.Copy

        With wbNew.Worksheets(1).Cells 'directly access the first sheet in the new workbook
            .PasteSpecial xlPasteAll
            .PasteSpecial xlPasteColumnWidths
        End With
    Next iRow

End Sub

score 0 · Answer 2 · edited Sep 11 '18 at 09:14

0

Windows(workbookname).Activate should be Workbooks(workbookname).Activate

edited Sep 11 '18 at 09:14

Luuklag

3,897
11
38
57

answered Sep 11 '18 at 09:13

Harassed Dad

4,669
1
10
12

This doesn't seem to fix the issue. It has copied the data, opens up the new workbook where I want to paste. Yet for some reason with the final step it is pasting in the one behind and not the one that I want. I'm baffled – Simmo888 Sep 11 '18 at 09:23

Copying data from master sheet to workbooks based on criteria

2 Answers2