Hi All

Any help greatly appreciated. I'm new to the forum and excel so please forgive me if I don't explain things very well.

I have created an excel spreadsheet for recording my monthly expenses for working with different clients. Called Sheet 1. There is a table in Sheet which summaries my expenses. Within the table, there are multiple rows - each row represents an expense item which is assigned to a Client and each Client may appear several times in the table as follows:

Client 1, Travel, £22.00
Client 2, Subsistence, £18.11
Client 1, Meeting, 33.00
Client 2, Travel, 94.13

So I have created a pivot table to summarise the expenses by each client. The pivot table appears a couple of columns over from the table of expenses in Sheet 1.

What I'd like to do is create new worksheets and rename these based on the info contained in the pivot table (Client 1, Client 2, etc) which I can do using VBA to "add sequence worksheets based on list of cells" so I have included the following:

----------

Sub CreateWorkSheetByRange()
'Update 20130829
Dim WorkRng As Range
Dim ws As Worksheet
Dim arr As Variant
On Error Resume Next
xTitleID = "Select Clients"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Clients", xTitleID, WorkRng.Address, Type:=8)
arr = WorkRng.Value
Application.ScreenUpdating = False
For i = 1 To UBound(arr, 1)
For j = 1 To UBound(arr, 2)
Set ws = Worksheets.Add(After:=Application.ActiveSheet)
ws.Name = arr(i, j)
Next

Application.ScreenUpdating = True
End Sub

----------

My question is this. Is there a way that I can copy the layout and structure of Sheet 1 into each of the new sequenced worksheets, but only paste (so using a filter perhaps?) the data that now relates to the name of the Worksheet (Client 1, Client 2) so that only the expenses relevant to each client, appear on each new sequenced worksheet?

I'm using a Mac (if that makes any difference).

Thanks

Rubitek