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

[galbatrox9]

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

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:

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

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.

 

[mumps]

Do the dedicated sheets per the account number already exist or do they have to be created?

 

[Fluff]

What do the values in Col A look like?

 

[galbatrox9]

Do the dedicated sheets per the account number already exist or do they have to be created?

They already exist. So i have in total 10 account numbers. And i have 10 sheets set for them.

What do the values in Col A look like?

6 digit numbers

 

[mumps]

Try:

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(CStr(AccNum)).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
        Sheets(CStr(AccNum)).Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
        Sheets(CStr(AccNum)).Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
        Sheets(CStr(AccNum)).Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
        Sheets(CStr(AccNum)).Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
    Next AccNum
    Application.ScreenUpdating = True
End Sub

I'm assuming that the sheet names are the account numbers but it looks like they may be named after the name. If they are named after the name, then the macro will have to be modified.

 

[galbatrox9]

Try:

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(CStr(AccNum)).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 2)
        Sheets(CStr(AccNum)).Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum
        Sheets(CStr(AccNum)).Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 1)
        Sheets(CStr(AccNum)).Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 4)
        Sheets(CStr(AccNum)).Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, 3)
    Next AccNum
    Application.ScreenUpdating = True
End Sub

I'm assuming that the sheet names are the account numbers.

It didnt work buddy.

Can a simple code be possible which works on the basis of me writing the account number in the code which will do to a sheet? since the account number will not be the same as the sheet name.

 

[mumps]

Are the sheet names based on Name, Date, Value or Counterparty or are they totally different?

 

[galbatrox9]

The sheet names are based on nothing from the raw sheet. As the name given in the sheet is different from my sheet names.
(Below is an example of my sheet names)
557111 - DeltaMan
557118 - GalbaBax
883913 - StockBoy

 

[mumps]

We have to have some way of linking the account number to the appropriate sheet. We can do this by you listing all 10 accounts with the associated sheet name. You have listed 3 of them. What are the other 7?

 

[galbatrox9]

We have to have some way of linking the account number to the appropriate sheet. We can do this by you listing all 10 accounts with the associated sheet name. You have listed 3 of them. What are the other 7?

Oh i thought if you could come up with a line of code, i would just copy and paste it for the other acc's.

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]557111-DeltaMan[/TD]
[/TR]
[TR]
[TD]557118-GalbaBax[/TD]
[/TR]
[TR]
[TD]883913-StockBoy[/TD]
[/TR]
[TR]
[TD]931585-RusselT[/TD]
[/TR]
[TR]
[TD]758474-4462Tral[/TD]
[/TR]
[TR]
[TD]114221-555Hotel[/TD]
[/TR]
[TR]
[TD]113251-BountyCap[/TD]
[/TR]
[TR]
[TD]114229-Dabur12[/TD]
[/TR]
[TR]
[TD]100005-ViteSam01[/TD]
[/TR]
[TR]
[TD]982742-Arouq2[/TD]
[/TR]
</tbody>[/TABLE]