I posted this in general discussion but I noticed that it would be more appropriate to put it here.

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

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

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. 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 tried to develop a VBA for this and so far this is what I have:

Private Sub CommandButton1_Click()

Dim MP As String
Dim Rng As Range
Dim lastRow As Long
Dim lastRowDest As Long
Dim caddress As String
Dim oldbook As Workbook
Dim newbook As Workbook


Application.ScreenUpdating = False


Set oldbook = ThisWorkbook


On Error GoTo errorM

MP = Application.InputBox("Enter Region")
If MP = "" Then Exit Sub
If Trim(MP) <> "" Then

Columns("B:B").Select
Selection.Find(What:=MP, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Activate

ActiveCell.Select
caddress = ActiveCell.Offset(0, -1).Address(False, False)

'/Create new workbook & save
Set newbook = Workbooks.Add


'/ copies headers to new workbook


oldbook.Sheets("sheet1").Range("B1:G1").Copy newbook.Sheets("sheet1").Range("A1")

With oldbook.Sheets("Sheet1")
lastRow = .Range("B" & Rows.Count).End(xlUp).Row
.Range(caddress & ":G" & lastRow).Copy Destination:= _
newbook.Worksheets("Sheet1").Range("B" & Rows.Count).End(xlUp)(2)

End With
newbook.SaveAs Filename:="C:\" & MP & ".xls"
Application.ScreenUpdating = True
MsgBox "Done!"



End If
Exit Sub
errorM:
MsgBox ("Invalid Region")

End Sub

So far what it does is create a new workbook with the inputted name. It then copies all the data from the matching name in the source document. I'm still trying to figure out how to make it stop at the Total, and also to make the columns copied flexible based on the number of columns I have. Any ideas on how to do this? Any help would be much appreciated!