software

Hi,

Current Manual Process for Sales Data by Branch is:-

Open Workbook A (List of all data by operative with branch code in column a,
operative in B, with the remaing columns containing all the data.)

Open B

Type the branch code in a cell, vlookups then pull all the data for that
branch by operator.

That bit I'm happy with.

That file is then copied and paste specialled in a new workbook, which is
saved with the branchcode as a file name.

I'd love a Macro which works through a list of branch codes, pulls the data
from wookbook A by vlookup, then pastes the values to a vew workbook which
it saves with the branchcode as the file name.

Can anyone suggest a macro to do this ?
It would help a lot if you could post some sample data of Workbook A, or
better still, upload it to cjoint.com and post back the link.

TIA
--
AP

quot;Richquot; gt; a écrit dans le message de news:
...
gt; Hi,
gt;
gt; Current Manual Process for Sales Data by Branch is:-
gt;
gt; Open Workbook A (List of all data by operative with branch code in column
gt; a, operative in B, with the remaing columns containing all the data.)
gt;
gt; Open B
gt;
gt; Type the branch code in a cell, vlookups then pull all the data for that
gt; branch by operator.
gt;
gt; That bit I'm happy with.
gt;
gt; That file is then copied and paste specialled in a new workbook, which is
gt; saved with the branchcode as a file name.
gt;
gt; I'd love a Macro which works through a list of branch codes, pulls the
gt; data from wookbook A by vlookup, then pastes the values to a vew workbook
gt; which it saves with the branchcode as the file name.
gt;
gt; Can anyone suggest a macro to do this ?
gt;

quot;Ardus Petusquot; gt; wrote in message
...
gt; It would help a lot if you could post some sample data of Workbook A, or
gt; better still, upload it to cjoint.com and post back the link.
gt;
gt; TIA
gt; --
gt; AP
gt;
gt; quot;Richquot; gt; a écrit dans le message de news:
gt; ...
gt;gt; Hi,
gt;gt;
gt;gt; Current Manual Process for Sales Data by Branch is:-
gt;gt;
gt;gt; Open Workbook A (List of all data by operative with branch code in column
gt;gt; a, operative in B, with the remaing columns containing all the data.)
gt;gt;
gt;gt; Open B
gt;gt;
gt;gt; Type the branch code in a cell, vlookups then pull all the data for that
gt;gt; branch by operator.
gt;gt;
gt;gt; That bit I'm happy with.
gt;gt;
gt;gt; That file is then copied and paste specialled in a new workbook, which is
gt;gt; saved with the branchcode as a file name.
gt;gt;
gt;gt; I'd love a Macro which works through a list of branch codes, pulls the
gt;gt; data from wookbook A by vlookup, then pastes the values to a vew workbook
gt;gt; which it saves with the branchcode as the file name.
gt;gt;
gt;gt; Can anyone suggest a macro to do this ?If I've done it right, the sample data is here :-

cjoint.com/?frqFibMWfC

quot;Ardus Petusquot; gt; wrote in message
...
gt; It would help a lot if you could post some sample data of Workbook A, or
gt; better still, upload it to cjoint.com and post back the link.
gt;
gt; TIA
gt; --
gt; AP
gt;
gt; quot;Richquot; gt; a écrit dans le message de news:
gt; ...
gt;gt; Hi,
gt;gt;
gt;gt; Current Manual Process for Sales Data by Branch is:-
gt;gt;
gt;gt; Open Workbook A (List of all data by operative with branch code in column
gt;gt; a, operative in B, with the remaing columns containing all the data.)
gt;gt;
gt;gt; Open B
gt;gt;
gt;gt; Type the branch code in a cell, vlookups then pull all the data for that
gt;gt; branch by operator.
gt;gt;
gt;gt; That bit I'm happy with.
gt;gt;
gt;gt; That file is then copied and paste specialled in a new workbook, which is
gt;gt; saved with the branchcode as a file name.
gt;gt;
gt;gt; I'd love a Macro which works through a list of branch codes, pulls the
gt;gt; data from wookbook A by vlookup, then pastes the values to a vew workbook
gt;gt; which it saves with the branchcode as the file name.
gt;gt;
gt;gt; Can anyone suggest a macro to do this ?

If I've done it OK, the sample data is he-

cjoint.com/?frqFibMWfC

In that small sample, the second workbook would lookup the data from ytg567,
then I'd save it under filename ytg567.xls.

I want to automate working through the branch list, looking up the data and
saving as branch name.

quot;Richquot; gt; wrote in message
...
gt;
gt; quot;Ardus Petusquot; gt; wrote in message
gt; ...
gt;gt; It would help a lot if you could post some sample data of Workbook A, or
gt;gt; better still, upload it to cjoint.com and post back the link.
gt;gt;
gt;gt; TIA
gt;gt; --
gt;gt; AP
gt;gt;
gt;gt; quot;Richquot; gt; a écrit dans le message de news:
gt;gt; ...
gt;gt;gt; Hi,
gt;gt;gt;
gt;gt;gt; Current Manual Process for Sales Data by Branch is:-
gt;gt;gt;
gt;gt;gt; Open Workbook A (List of all data by operative with branch code in
gt;gt;gt; column a, operative in B, with the remaing columns containing all the
gt;gt;gt; data.)
gt;gt;gt;
gt;gt;gt; Open B
gt;gt;gt;
gt;gt;gt; Type the branch code in a cell, vlookups then pull all the data for that
gt;gt;gt; branch by operator.
gt;gt;gt;
gt;gt;gt; That bit I'm happy with.
gt;gt;gt;
gt;gt;gt; That file is then copied and paste specialled in a new workbook, which
gt;gt;gt; is saved with the branchcode as a file name.
gt;gt;gt;
gt;gt;gt; I'd love a Macro which works through a list of branch codes, pulls the
gt;gt;gt; data from wookbook A by vlookup, then pastes the values to a vew
gt;gt;gt; workbook which it saves with the branchcode as the file name.
gt;gt;gt;
gt;gt;gt; Can anyone suggest a macro to do this ?
gt;
gt; If I've done it OK, the sample data is he-
gt;
gt; cjoint.com/?frqFibMWfC
gt;
gt; In that small sample, the second workbook would lookup the data from
gt; ytg567, then I'd save it under filename ytg567.xls.
gt;
gt; I want to automate working through the branch list, looking up the data
gt; and saving as branch name.Try this instead cjoint.com/?frqUcVcJ4s I should have checked the
dummy data before I uploaded it !
Here is your macro.

See example: cjoint.com/?ftkyKVoGnc

HTH
--
AP

'-------------
Option Explicit

Sub SaveBranches()

Dim rBranch As Range
Dim lBranchCount As Long

' Create list of unique Branch codes
Range(quot;A1:A9quot;).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range(quot;I1quot;), Unique:=True
' Check list size
lBranchCount = Range(quot;I1quot;).End(xlDown).Row - 1
If lBranchCount = Rows.Count - 1 Then
MsgBox quot;Empty Branch listquot;
Exit Sub
End If
' Loop thru branches
For Each rBranch In Range(quot;I2quot;).Resize(lBranchCount)
' Filter data pertaining to current branch
Range(quot;A1:G1quot;).AutoFilter Field:=1, Criteria1:=rBranch.Value
' Copy filtered data
Range(quot;A1quot;).CurrentRegion.Copy
' Create new workbook
Workbooks.Add
' Paste data, formats amp; col width
Range(quot;A1quot;).PasteSpecial Paste:=xlPasteAll
' Save workbook
With ActiveWorkbook
Application.DisplayAlerts = False
.SaveAs _
Filename:=ThisWorkbook.Path amp; quot;\quot; amp; rBranch.Value amp; quot;.xlsquot;
Application.DisplayAlerts = True
.Close
End With
' Get back to data workbook
ThisWorkbook.Activate
Next rBranch
' Clean up
ActiveSheet.AutoFilterMode = False
Range(quot;I1quot;).Resize(lBranchCount 1).ClearContents

End Sub
'----------