How to separate data between sheets

[Closed]
Report
Posts
1
Registration date
Tuesday August 23, 2016
Status
Member
Last seen
August 23, 2016
-
Posts
1316
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 21, 2021
-
Hello,
I'm don't know much about Excel in regards to functions and formulas, what I'm trying to do seemed like it may be a simple task but I'm learning its a bit more complex than I had anticipated I'm guessing. Which brings me here to ask a community that may be able to help and guide me in the right direction. I have workbook, and what I'm trying to do is move data between sheets based on the status of a cell on the first sheet. In other words, if a cell is marked complete it will transfer to the 3rd sheet "Completed" if it's "Pending it will transfer to the 2nd sheet. I thought this may have been a simple sort or if function, but I'm not getting it. I've been told I need Visual basic experience, what do I need to do? Please help. Thanks in Advance



1 reply

Posts
1316
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 21, 2021
237
Hello OwicKo,

I'm not entirely sure how your work book is set out, but I believe that the following code, placed in a standard module, should do the trick for you:-


Option Explicit

Sub TransferData()

Dim ar As Variant, i As Integer

ar = [{"Pending","Completed";"Pending","Completed"}]

Application.ScreenUpdating = False

    For i = 1 To UBound(ar, 2)
      Sheets(ar(1, i)).UsedRange.Offset(1).ClearContents
        With Sheet1
            .AutoFilterMode = False
                With Range("J1", Range("J" & Rows.Count).End(xlUp))
                    .AutoFilter 1, ar(2, i)
                    .EntireRow.Copy
                    Sheets(ar(1, i)).Range("A" & Rows.Count).End(xlUp).PasteSpecial xlPasteValues
                    ActiveSheet.AutoFilterMode = False
                    Sheets(ar(1, i)).Columns.AutoFit
                End With
        End With
    Next i
    
Application.ScreenUpdating = True
Application.CutCopyMode = False
MsgBox "Data transfer completed!", vbExclamation, "STATUS"

End Sub


As I don't know how many columns you are using, I've assumed that your "Status" column is Column J (refer to line 15 in the code above). The code filters Column J for the criteria "Pending" or "Completed" and transfers any relevant rows of data to the relevant individual sheet.

Following is the link to my test work book for you to peruse. Type "Pending" or "Completed" as many times as you like in Column J and then click on the "RUN" button to see it work.

https://www.dropbox.com/s/96jq90ueyifxuh8/Owicko%28Master%20sht%20to%20multi%2Carray%2Cautofilter%29.xlsm?dl=0

If you intend to use the code above in your work book, test it in a copy of your work book first.

It would also be a good idea if you could upload a sample of your work book (please use dummy data) to a free file sharing site such as DropBox, ge.tt or SpeedyShare then post the link to your file back here. This way, should an error arise when you test the code, we will be able to check what the problem is in your sample.

I hope that this helps.

Cheerio,
vcoolio.