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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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

gymwrecker
  • Solved
Formula to copy data from one sheet to another sheet based on specific criteria
Replies
4
Views
270
Fluff
Fluff
Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
402
sijpie
sijpie
D
  • Question Question
VBA Code Copy/Replace Values base on Date
Replies
1
Views
170
Anthony47
A
T
  • Solved
Extracting and Formatting Numbers from Data Rows
2
Replies
12
Views
393
TheWes
T
N
  • Question Question
Spitting every 2000 rows of data to new workbook based on duplicate value
Replies
1
Views
103
Skyybot
Skyybot

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

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