VBA Create new workbook and copy paste data

[jayt91]

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:

Province	Region	Office	2011	2012	2013	Total
Ontario	A	AA	5	8	5	18
		AB	3	6	2	11
		AC	3	3	7	13
	Total		11	17	14	42
	B	BA	1	7	2	10
		BC	6	2	2	10
		BD	6	8	4	18
	Total		13	17	8	38

<TBODY>
</TBODY>

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 (which I failed miserably at trying) 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

 

[jayt91]

Bump - Can anyone help with this? Any pointers would be appreciated.