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!
Bookmarks