vba to copy value and paste in worksheet with the same name as value

[husker32]

Hello all,

I have a workbook that has 121 different worksheets. I would like some help creating a macro that would search the first sheet titled "ledger" in column c. Column C has sudsiary names. Each one of my subsiary names already has a worksheet with the same name. So the macro would need to copy the entire row for all of the claims with the same subsidiary. For example there is a subsiary name reg and there are hundreds of rows of reg claims I want to copy all of those claims and put them in the sheet title reg. Currenlty I have a macro that uses the auto filter to achieve this but it is slow and long. I was hoping that there was a loop that could achieve this goal. Thanks in advance.

 

[MPW]

You can loop through each sheet . . .

For Each Sheet in ActiveWorkBook.Sheets

And you can Loop through rows in sheet . . .

Set ledgr = Sheets("Ledger").Range("C1:C65536")
For Each lgr in ledger

And you can use an "If" statement to compare the two.

The question I have is do you want to add to these sheets every time the macro is run, or wipe the sheets clean and start over?

 

[husker32]

Each month I will replace the ledger and the 120 other sheets will be cleared.

 

[MPW]

This worked when I tested it.
Just to be sure it does what you want always test it on a copy

    lstrow = Sheets("Sheet1").Range("C65536").End(xlUp).Row
    For Each Sheet In ActiveWorkbook.Sheets
    If Sheet.Name <> "Sheet1" Then
        Sheets(Sheet.Name).Cells.ClearContents
    End If
    Next
    For i = 1 To lstrow
        For Each Sheet In ActiveWorkbook.Sheets
            If Sheet.Name = Sheets("Sheet1").Cells(i, 3) Then
                lstrow2 = Sheets(Sheet.Name).Range("A65536").End(xlUp).Row
                Sheets("Sheet1").Rows(i).EntireRow.Copy
                Sheets(Sheet.Name).Cells(lstrow2, 1).PasteSpecial Paste:=xlValues
                Application.CutCopyMode = False
                Exit For
            End If
        Next
    Next i

You will need to switch out my "Sheet1" refs with what ever you called yours.

 

[husker32]

When I run the macro it just pastes rows in the first row of the sheet it needs, over and over.

 

[JackDanIce]

Try:

Sub macro1()

Dim ws As Worksheet
Dim LR As Long, LR2 As Long, x As Long

Application.ScreenUpdating = False

With Sheets("Sheet1")
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    x = .Cells(1, Columns.Count).End(xlToLeft).Column
End With

For Each ws In ActiveWorkbook.Sheets
    If ws.name <> "Sheet1" Then ws.Cells.ClearContents
Next ws

For i = 1 To LR
    For Each ws In ActiveWorkbook.Sheets
        With ws
            If .name = Sheets("Sheet1").Range("C" & i).Value Then
                LR2 = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                .Range("A" & LR2).Resize(1, x).Value = Sheets("Sheet1").Range("A1").Resize(1, x).Value
                Exit For
            End If
        End With
    Next ws
Next i

Application.ScreenUpdating = True

End Sub

 

[MPW]

Sorry 'bout that. Amazing how a couple of keystrokes can make all the difference!

Try this.

Change this line

[COLOR=#574123]lstrow2 = Sheets(Sheet.Name).Range("A65536").End(xlUp).Row[/COLOR]

with this line

[COLOR=#574123]lstrow2 = Sheets(Sheet.Name).Range("A65536").End(xlUp).Row + 1[/COLOR]

Also I used column A but you could use a different column if A doesn't work well with your data. You might want to use Column C instead.

 

[JackDanIce]

MPW, small pointer, Excel 2007 and later contains more than 65536 rows so Range("A65536") isn't necessarily the last available row in a sheet in later versions of Excel.

Range("A" & Rows.Count) finds the last row, regardless of Excel version because Rows.Count is the count of rows in a (given) sheet

 

[MPW]

All True! I did base it on 2003. It was also just an example of what he could do. It was never an issue for me since I knew what version I would be working in and it was quicker for me to just hard code it. I liked how you counted the rows for the sheet. Much cleaner to use across different versions.

I just was not sure that column A had data down to the bottom. That is why I suggested using column C.

MPW, small pointer, Excel 2007 and later contains more than 65536 rows so Range("A65536") isn't necessarily the last available row in a sheet in later versions of Excel.

Range("A" & Rows.Count) finds the last row, regardless of Excel version because Rows.Count is the count of rows in a (given) sheet

 

[JackDanIce]

No prob. I'm so used to thinking "last row in column A", I didn't even consider column C. Plus I never remember the number 65,536 let alone 1,048,576!