VBA Create new workbook and copy paste data

I'm new to the forums and just started learning VBA. I was wondering if anyone could provide some assistance in creating a VBA macro to do the following:

I have a set of data in one workbook which I'll call Workbook1 for instance.

For example, if there is information on a PivotTable like this:

I would like to create a macro (an inputbox maybe) to search for a region (A for example) and create a new workbook & save with the region's name (A.xls for example). The more complicated part would be to then copy the information to the new workbook from columns "office - total" and all the rows from office AA to total. I tried to figure this out searching through the forums but couldn't quite put everything together. Any help would be much appreciated!

Thanks

Edit: What i'm trying to aim at is to be able to input a region in the source workbook and automatically create a new workbook with the inputted region. The new workbook should then be automatically populated with information under the region from the source workbook.

I've attached a test spreadsheet which I'm using to try and replicate the effect but with less complex data.

So can you post a sample workbook? A photo doesnt really do much for us.

Also specifically mock up what you are trying to dol, start and finish.

Edit: I noticed it was more appropriate to put this in the VBA subforum. Continued here:
http://www.excelforum.com/excel-prog...ml#post3824647

I posted what I have so far for my VBA script there.

Here is another solution in VBA

Option Explicit

Sub CopyToNew()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    Dim lr As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    Dim i As Long

Application.ScreenUpdating = False
    For i = 2 To lr
    If Range("B" & i) = "" Then Range("B" & i) = Range("B" & i - 1)
    Next i

    Dim myPath As String
    myPath = "C:/Users/Alan Sidman/Desktop/"  'Change this to the correct path for your machine
    Dim RegName As String
    RegName = InputBox("What Region to Move?")
    Dim Aname As String
    Aname = RegName
    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:=RegName & ".xls"
    Dim TargWb As Workbook
    Set TargWb = Workbooks(RegName & ".xls")
    Dim lr2 As Long
    lr2 = TargWb.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    
    wb.Activate
    Sheets("Sheet1").Range("B1:G1").Copy TargWb.Sheets("Sheet1").Range("A1")
    For i = 2 To lr
        lr2 = TargWb.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
        If Sheets("sheet1").Range("B" & i) = RegName Then
        Sheets("Sheet1").Range("B" & i & ":G" & i).Copy TargWb.Sheets("Sheet1").Range("A" & lr2 + 1)
        End If
    Next i
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "complete"
        

End Sub

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

The code works fairly well, thanks!

Edit: Figured out my problems and will try to see if I can complete it myself.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.