VBA Code

[Deepu.Hermoine]

Hi All,
Hope you are well...
I am on a beginner stage in VBA coding. I have learnt some of the coding from this community.

I am trying to work out on a VBA which i am not going anywhere near
Scenario :-
I have a data on my main excel sheet where I am assigning the orders to other colleagues.
Col B - Orders Assigned
I will be assigning the orders on Col B.
I want a Code , where if i assign to person on COl B , it should copy all the data on that particular row and then paste them on the analyst excel on different location.
Example :-
I have received a Order and assigned to Suresh (on Col B)
Once I have assigned this , this data should be copied and pasted on Suresh Excel (which is on different location).

Likewise I have 20 associates where excel should copy from assigning sheet and should paste in their respective workbooks
Please help me in retrieving the code for the above scenario.
Thank you in advance

 

[Abdul Matheen]

Hi Deepu,

Can you send a sample workbook with data and exact requirement sample, so that i can design in the same way.

Regards
Abdul Matheen

 

[Deepu.Hermoine]

Hi Abdul,

Thank you so much for your quick response. I have attached the sample worksheets for your kind reference. Awaiting for your response at the earliest.

 

[Abdul Matheen]

Hi Deepu,

Find attached macro as per the requirement, download the attached workbook and save in the same folder where you save the individual workbooks and click on "Run Macro!!!" button.

Regards
Abdul Matheen

 

[rumshar]

Hi little mid of modification from Matheen's code.

Sub assdata()

Application.DisplayAlerts = False
Dim wks As String
Dim wkd As Workbook
Dim dpath As String
Dim fltr As String
Dim i As Integer
Dim lr As Integer

i = 2

wks = ThisWorkbook.Name
dpath = ThisWorkbook.Path & "\"

Windows(wks).Activate

Range("I3").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Do While Cells(i, "i").Value <> ""
fltr = Cells(i, "i").Value
Cells(1, 1).CurrentRegion.AutoFilter 2, fltr ' 2 is column number and fltr is filtering criteria
'Range("A1:D1").Select
'Selection.AutoFilter
'Range("B1").Select

'copy filter data
'ActiveSheet.Range("$A$1:$D$7").AutoFilter Field:=2, Criteria1:=fltr
'Range("A1").Select
lr = Selection.End(xlDown).Row
ActiveSheet.Range("a1:d" & lr).Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy

'paste filter data

Workbooks.Open (dpath & fltr & ".xlsx")
Sheets("Assigned orders").Range("a1048576").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Range("a1").Select
ActiveWorkbook.Close True

i = i + 1
Loop


End Sub

 

[Deepu.Hermoine]

Hi Abdul & Rumshar,

Thank you so so much for the VBA code. I will check the VBA in my office system and will provide you the status.

Meanwhile to be honest there is a long way for me to learn many things in excel and MS office.

Is there any classes conducted to improvise our skills in office and also please let me know whether you have any videos blog to teach us basic and advanced excel with VBA.

Right now ,I am in a thinking of improvise my understanding on these codes and will surely help others like you both did for me.

Many thanks once again for your timely. I wont forget you both

Take care

Best Regards,
Deepak