Macro to copy parts of data from a row to specific location based on a value

G

galbatrox9

New Member
Joined
Aug 30, 2017
Messages
23
Hi Guys,

I have a raw excel sheet with rows of data that all have an account number in he first row. And i want to transfer those rows of data to a dedicated sheet per the account number. Now here is the thing. This i got with this code
Code: 
If (InStr(1, Cells(i, 1).Value, "55711") > 0)  Then        Rows(i).Copy
        srow = ThisWorkbook.Sheets("DeltaMan").Range("A65536").End(xlUp).Row + 1
        ThisWorkbook.Sheets("DeltaMan").Cells(srow, 1).PasteSpecial (xlPasteValues)

But here comes the biggest challenge.

Raw sheet details:
jkCLt5u.jpg


I want the data to be copied to their respective sheets , but the row's data needs to be shuffled in this way

IWbZfSa.jpg




Can this be done? So the macro should do this :
-If a row that 55711 in Col. A , then copy data from column C (date) and paste in Column A of 'DeltaMan' sheet, etc etc.


Can someone please help me here.
 
Sort by date Sort by votes
Try:
Code: 
Sub CopyData()
    Application.ScreenUpdating = False
    Dim bottomA As Long
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim AccNum As Range
    For Each AccNum In Range("A2:A" & bottomA)
        Select Case AccNum.Value
            Case Is = 557111
                Sheets("DeltaMan").UsedRange.Offset(1, 0).ClearContents
                Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
            Case Is = 557118
                Sheets("GalbaBax").UsedRange.Offset(1, 0).ClearContents
                Sheets("GalbaBax").Cells(Sheets("GalbaBax").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("GalbaBax").Cells(Sheets("GalbaBax").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("GalbaBax").Cells(Sheets("GalbaBax").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("GalbaBax").Cells(Sheets("GalbaBax").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("GalbaBax").Cells(Sheets("GalbaBax").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
            Case Is = 883913
                Sheets("StockBoy").UsedRange.Offset(1, 0).ClearContents
                Sheets("StockBoy").Cells(Sheets("StockBoy").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("StockBoy").Cells(Sheets("StockBoy").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("StockBoy").Cells(Sheets("StockBoy").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("StockBoy").Cells(Sheets("StockBoy").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("StockBoy").Cells(Sheets("StockBoy").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
            Case Is = 931585
                Sheets("RusselT").UsedRange.Offset(1, 0).ClearContents
                Sheets("RusselT").Cells(Sheets("RusselT").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("RusselT").Cells(Sheets("RusselT").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("RusselT").Cells(Sheets("RusselT").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("RusselT").Cells(Sheets("RusselT").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("RusselT").Cells(Sheets("RusselT").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
            Case Is = 758474
                Sheets("4462Tral").UsedRange.Offset(1, 0).ClearContents
                Sheets("4462Tral").Cells(Sheets("4462Tral").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("4462Tral").Cells(Sheets("4462Tral").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("4462Tral").Cells(Sheets("4462Tral").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("4462Tral").Cells(Sheets("4462Tral").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("4462Tral").Cells(Sheets("4462Tral").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
            Case Is = 114221
                Sheets("555Hotel").UsedRange.Offset(1, 0).ClearContents
                Sheets("555Hotel").Cells(Sheets("555Hotel").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("555Hotel").Cells(Sheets("555Hotel").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("555Hotel").Cells(Sheets("555Hotel").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("555Hotel").Cells(Sheets("555Hotel").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("555Hotel").Cells(Sheets("555Hotel").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
            Case Is = 113251
                Sheets("BountyCap").UsedRange.Offset(1, 0).ClearContents
                Sheets("BountyCap").Cells(Sheets("BountyCap").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("BountyCap").Cells(Sheets("BountyCap").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("BountyCap").Cells(Sheets("BountyCap").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("BountyCap").Cells(Sheets("BountyCap").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("BountyCap").Cells(Sheets("BountyCap").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
            Case Is = 114229
                Sheets("Dabur12").UsedRange.Offset(1, 0).ClearContents
                Sheets("Dabur12").Cells(Sheets("Dabur12").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("Dabur12").Cells(Sheets("Dabur12").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("Dabur12").Cells(Sheets("Dabur12").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("Dabur12").Cells(Sheets("Dabur12").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("Dabur12").Cells(Sheets("Dabur12").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
            Case Is = 100005
                Sheets("ViteSam01").UsedRange.Offset(1, 0).ClearContents
                Sheets("ViteSam01").Cells(Sheets("ViteSam01").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("ViteSam01").Cells(Sheets("ViteSam01").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("ViteSam01").Cells(Sheets("ViteSam01").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("ViteSam01").Cells(Sheets("ViteSam01").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("ViteSam01").Cells(Sheets("ViteSam01").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
            Case Is = 982742
                Sheets("Arouq2").UsedRange.Offset(1, 0).ClearContents
                Sheets("Arouq2").Cells(Sheets("Arouq2").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("Arouq2").Cells(Sheets("Arouq2").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("Arouq2").Cells(Sheets("Arouq2").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("Arouq2").Cells(Sheets("Arouq2").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("Arouq2").Cells(Sheets("Arouq2").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
        End Select
    Next AccNum
    Application.ScreenUpdating = True
End Sub
The macro assumes you have headers in row 1 of all the sheets and your data starts in row 2.
 
Last edited:
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
mumps said:
Try:
Code: 
Sub CopyData()
    Application.ScreenUpdating = False
    Dim bottomA As Long
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim AccNum As Range
    For Each AccNum In Range("A2:A" & bottomA)
        Select Case AccNum.Value
            Case Is = 557111
                Sheets("DeltaMan").UsedRange.Offset(1, 0).ClearContents
                Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
            Case Is = 557118
                Sheets("GalbaBax").UsedRange.Offset(1, 0).ClearContents
                Sheets("GalbaBax").Cells(Sheets("GalbaBax").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("GalbaBax").Cells(Sheets("GalbaBax").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("GalbaBax").Cells(Sheets("GalbaBax").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("GalbaBax").Cells(Sheets("GalbaBax").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("GalbaBax").Cells(Sheets("GalbaBax").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
            Case Is = 883913
                Sheets("StockBoy").UsedRange.Offset(1, 0).ClearContents
                Sheets("StockBoy").Cells(Sheets("StockBoy").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("StockBoy").Cells(Sheets("StockBoy").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("StockBoy").Cells(Sheets("StockBoy").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("StockBoy").Cells(Sheets("StockBoy").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("StockBoy").Cells(Sheets("StockBoy").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
            Case Is = 931585
                Sheets("RusselT").UsedRange.Offset(1, 0).ClearContents
                Sheets("RusselT").Cells(Sheets("RusselT").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("RusselT").Cells(Sheets("RusselT").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("RusselT").Cells(Sheets("RusselT").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("RusselT").Cells(Sheets("RusselT").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("RusselT").Cells(Sheets("RusselT").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
            Case Is = 758474
                Sheets("4462Tral").UsedRange.Offset(1, 0).ClearContents
                Sheets("4462Tral").Cells(Sheets("4462Tral").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("4462Tral").Cells(Sheets("4462Tral").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("4462Tral").Cells(Sheets("4462Tral").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("4462Tral").Cells(Sheets("4462Tral").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("4462Tral").Cells(Sheets("4462Tral").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
            Case Is = 114221
                Sheets("555Hotel").UsedRange.Offset(1, 0).ClearContents
                Sheets("555Hotel").Cells(Sheets("555Hotel").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("555Hotel").Cells(Sheets("555Hotel").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("555Hotel").Cells(Sheets("555Hotel").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("555Hotel").Cells(Sheets("555Hotel").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("555Hotel").Cells(Sheets("555Hotel").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
            Case Is = 113251
                Sheets("BountyCap").UsedRange.Offset(1, 0).ClearContents
                Sheets("BountyCap").Cells(Sheets("BountyCap").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("BountyCap").Cells(Sheets("BountyCap").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("BountyCap").Cells(Sheets("BountyCap").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("BountyCap").Cells(Sheets("BountyCap").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("BountyCap").Cells(Sheets("BountyCap").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
            Case Is = 114229
                Sheets("Dabur12").UsedRange.Offset(1, 0).ClearContents
                Sheets("Dabur12").Cells(Sheets("Dabur12").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("Dabur12").Cells(Sheets("Dabur12").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("Dabur12").Cells(Sheets("Dabur12").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("Dabur12").Cells(Sheets("Dabur12").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("Dabur12").Cells(Sheets("Dabur12").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
            Case Is = 100005
                Sheets("ViteSam01").UsedRange.Offset(1, 0).ClearContents
                Sheets("ViteSam01").Cells(Sheets("ViteSam01").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("ViteSam01").Cells(Sheets("ViteSam01").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("ViteSam01").Cells(Sheets("ViteSam01").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("ViteSam01").Cells(Sheets("ViteSam01").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("ViteSam01").Cells(Sheets("ViteSam01").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
            Case Is = 982742
                Sheets("Arouq2").UsedRange.Offset(1, 0).ClearContents
                Sheets("Arouq2").Cells(Sheets("Arouq2").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("Arouq2").Cells(Sheets("Arouq2").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
                Sheets("Arouq2").Cells(Sheets("Arouq2").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
                Sheets("Arouq2").Cells(Sheets("Arouq2").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
                Sheets("Arouq2").Cells(Sheets("Arouq2").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
        End Select
    Next AccNum
    Application.ScreenUpdating = True
End Sub
The macro assumes you have headers in row 1 of all the sheets and your data starts in row 2.
Click to expand...

This worked with the data i gave you! Awesome man!

However, if there were to be more columns added/inserted in between, i thought i could just add another row of code like yours, and just play with the column references, but that is not working.


Code: 
    Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)                Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
                Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 5)
                Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 6)
                Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 11)
                Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "F").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 13)
                Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "H").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 15)

I tried making amends, but i see that only 4 columns of data was getting moved, and the rest didnt.
 
Upvote 0
It should work. Can you post a screen shot of what your Raw sheet and one of the account sheets looks like with the added columns, similar to what you posted in your original post?
 
Upvote 0
Based on your pics try
Code: 
Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum
Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "F").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "K").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "M").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 5)
Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "O").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 7)
 
Upvote 0
Also in post#8 you said
The sheet names are based on nothing from the raw sheet.
Click to expand...
Yet from the pics you've just posted, it looks as though col B in the Raw Sheet, contains the sheet name.
 
Upvote 0
Fluff said:
Based on your pics try
Code: 
Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum
Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "F").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "K").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "M").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 5)
Sheets("DeltaMan").Cells(Sheets("DeltaMan").Rows.Count, "O").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 7)
Click to expand...

OMG! I was editing the code wrong. You're answer did it.

Thank you mumps and Fluff you guys just helped me big time!
 
Upvote 0
I just thought I would throw this in. The revised macro would look something like this referencing column B of the Raw sheet for the sheet name:
Code: 
Sub CopyData()
    Application.ScreenUpdating = False
    Dim bottomA As Long
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim AccNum As Range
    For Each AccNum In Range("A2:A" & bottomA)
        Sheets(AccNum.Offset(0, 1).Value).Cells(Sheets(AccNum.Offset(0, 1).Value).Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum
        Sheets(AccNum.Offset(0, 1).Value).Cells(Sheets(AccNum.Offset(0, 1).Value).Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
        Sheets(AccNum.Offset(0, 1).Value).Cells(Sheets(AccNum.Offset(0, 1).Value).Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
        Sheets(AccNum.Offset(0, 1).Value).Cells(Sheets(AccNum.Offset(0, 1).Value).Rows.Count, "F").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
        Sheets(AccNum.Offset(0, 1).Value).Cells(Sheets(AccNum.Offset(0, 1).Value).Rows.Count, "K").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
        Sheets(AccNum.Offset(0, 1).Value).Cells(Sheets(AccNum.Offset(0, 1).Value).Rows.Count, "M").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 5)
        Sheets(AccNum.Offset(0, 1).Value).Cells(Sheets(AccNum.Offset(0, 1).Value).Rows.Count, "O").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 7)
    Next AccNum
    Application.ScreenUpdating = True
End Sub
If you run the macro twice, however, you will get duplicate values in the account sheets. If this is a problem, please let me know and I will revise the macro.
 
Upvote 0
Or maybe
Code: 
Sub CopyData()
    Application.ScreenUpdating = False
    Dim bottomA As Long
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim AccNum As Range
    For Each AccNum In Range("A2:A" & bottomA)
        With Sheets(AccNum.Offset(0, 1).Value).Cells(Sheets(AccNum.Offset(0, 1).Value).Rows.Count, "A").End(xlUp)
            .Offset(1, 0) = AccNum
            .Offset(1, 1) = AccNum.Offset(0, 1)
            .Offset(1, 2) = AccNum.Offset(0, 2)
            .Offset(1, 5) = AccNum.Offset(0, 3)
            .Offset(1, 10) = AccNum.Offset(0, 4)
            .Offset(1, 12) = AccNum.Offset(0, 5)
            .Offset(1, 14) = AccNum.Offset(0, 7)
         End With
    Next AccNum
    Application.ScreenUpdating = True
End Sub
Not as much to type :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

Kaz09
  • Question
Copy rows from multiple sheets based on data
Replies
1
Views
319
Fluff
Fluff
B
  • Question
VBA Copying data from sheet 1 to sheet 2 not including duplicates
2
Replies
10
Views
716
Bobsta_666
B
F
  • Question
No cells were found - trying to copy a hidden row range
Replies
3
Views
146
Micron
M
B
  • Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
355
B-radK
B
L
  • Question
VBA code for macro button not working to transfer row data to another worksheet
Replies
1
Views
77
rjwalker1973
R

Forum statistics

Threads
1,216,725
Messages
6,132,347
Members
449,719
Latest member
excel4mac

Share this page

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
Back
Top