Copy row to sheets if cell in col A=sheetname

[Mike Moran]

Hi-

Been all over the web today and in this forum, and so close, but no cigar.

I need to copy a row from "Master" sheet to other named sheets in one workbook, based upon CellValue Col A = sheet name. Continue down col A, copying the row to the appropriate sheet (jumping to End.xlUp.offset1 of course).

Here's one I tried (edited and edited to try and match what I'm attempting - this is the original I found):

Sub CopyYes()  'Static Sheets and rows ONLY
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet


    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Sheet1")
    Set Target = ActiveWorkbook.Worksheets("Sheet2")


    j = 1     ' Start copying to row 1 in target sheet
    For Each c In Source.Range("A1:A1000")   ' Do 1000 rows
        If c = "yes" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c

Ideally, this will be a workbook that's updated constantly, so I don't keep adding the same data from Master to the bottom of the sheets every time I run the macro. That's probably a follow-up research issue.

 

[dUBBINS]

What is the last column in the row? Not sure you even can, but you don't want Excel copying an entire row; it is not made for heavy lifting.

Also, if you are only copying values it is better to avoid copy and paste and use something like Range("a1").Value=Range("b1").value This "copies" from B1 and "pastes" into A1. I think it must skip the clipboard. But it runs smoother if you are doing a long list.

And you can add a helper column in, say, AA, or something just outside of your data. Have your code put, say, a 1 in that column if it copies that row. Then your code can be written so that an IF Boolean checks if there is a 1, and skips it if there is. But of course this suggestion can be tailored to your exact needs.

 

[Trebor76]

Hi Mike,

Welcome to MrExcel!!

Though in my humble opinion this is rarely the way to go, try this:

Option Explicit
Sub Macro1()

    Dim lngMyRow    As Long
    Dim lngLastRow  As Long
    Dim lngPasteRow As Long
    Dim wsSourceTab As Worksheet
    Dim wsTargetTab As Worksheet
    
    Application.ScreenUpdating = False
    
    Set wsSourceTab = Sheets("Master")
    
    lngLastRow = wsSourceTab.Cells(Rows.Count, "A").End(xlUp).Row
    
    For lngMyRow = 2 To lngLastRow 'Starts at Row 2. Change to suit if necessary.
        On Error Resume Next 'Account for the sheet not existing in the workbook or no data on the tab
            Set wsTargetTab = Sheets(CStr(wsSourceTab.Range("A" & lngMyRow)))
            If Err.Number <> 0 Then
                Worksheets.Add after:=Sheets(Sheets.Count)
                Sheets(Sheets.Count).Name = wsSourceTab.Range("A" & lngMyRow)
                Set wsTargetTab = Sheets(CStr(wsSourceTab.Range("A" & lngMyRow)))
            End If
            lngPasteRow = wsTargetTab.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
            If lngPasteRow = 0 Then
                lngPasteRow = 2 'Default row number if there's no data on the sheet. Change to suit if necessary.
            End If
        On Error GoTo 0
        wsSourceTab.Rows(lngMyRow).Copy wsTargetTab.Rows(lngPasteRow)
        lngPasteRow = 0
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Ideally, this will be a workbook that's updated constantly, so I don't keep adding the same data from Master to the bottom of the sheets every time I run the macro. That's probably a follow-up research issue.

Yes, this will have to be looked at as the data will keep appending to the tabs. This along with the large number of tabs that will eventually be generated are some of the reasons why I think this method is inefficient.

Regards,

Robert

 

[Mike Moran]

Hi dUBBINS,

It's max 10 columns- I thought about that detail last night and should have mentioned it.

It is value copy only; there are formulas (Vlookups) on the Master, but pasting values only.

Great thought on the Boolean skip row; no issue with helper columns.

Mike

 

[Mike Moran]

Hi Robert-

Thank you! I'll try this in a few minutes.

Did you have another attack angle in mind? I saw one method that reversed it (sheets looked for data on the master Col A), but that seemed illogical since there's a lot more data analysis.

Max tab count is probably going to end at 15. As mentioned to dUBBINS, I didn't mention that column count is Max ten.

Mike

 

[Trebor76]

Hi Mike,

I would go for just one tab with a drop-down that refreshes the data via lookup formulas back to the main data tab. So instead of clicking on a separate tab you just select the option from a drop-down.

Thanks,

Robert

 

[Mike Moran]

So kill the VBA and just run V/Hlookups?

Actually I didn't know you could run lookups based on a drop-down.

I think I'm not following you.

Mike

 

[Mike Moran]

On a related note, all these other tabs end up getting pasted (one sheet at a time) into other workbooks to add data and format correctly for a CSV import to a website. Those other workbooks are lookup intensive, which is no big deal except that any time I do a mod that affects formula cell results the workbook hangs for about 10 minutes; slightly better with Autocalc turned off. Not a problem I'd like to replicate here...

 

[Trebor76]

So kill the VBA and just run V/Hlookups?

Yes. So similar to a database structure where's there's a table that a report with filters is run.

Actually I didn't know you could run lookups based on a drop-down.

Just have a data validation in a particular cell that formulas work off.

Those other workbooks are lookup intensive

No doubt there's external lookups which can cause Excel to slow.

slightly better with Autocalc turned off

True but be wary that the figures are actually correct as they have not have been calculated!!

 

[Mike Moran]

Thanks Trebor; I'll start playing with this method. I always turn autocalc back on (knock on wood for that that I don't blow it...)

Mike