How to separate data between sheets
Closed
oWICKo
Posts
1
Registration date
Tuesday August 23, 2016
Status
Member
Last seen
August 23, 2016
-
Aug 23, 2016 at 08:40 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Aug 25, 2016 at 03:28 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Aug 25, 2016 at 03:28 AM
Related:
- How to separate data between sheets
- Sheets right to left - Guide
- Tmobile data check - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- Gta 5 data download for pc - Download - Action and adventure
- Digital data transmission - Guide
1 response
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Aug 25, 2016 at 03:28 AM
Aug 25, 2016 at 03:28 AM
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:-
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.
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.
