Copy/Paste macro

[stlstephens]

Hello all,

Let me start off by saying I am a newbie to excel macros. So any help would be appreciated. What I need help with is a financial report that I produce monthly. What it is a a report which shows the perfromance orfeach store as well as a total for the manager of an area. So each manager has approx. 8-10 stores. As it works now I have a macro built that prints a report for all the managers totals and another that prints each store. We then manually split the stores out and create a package for each manager. What I would like to do is the following:

1. Create an excel file for each manager and save it based on the managers name.
2. For each store, copy the store info to a new tab on the managers file.


Is this something that would be possible to do? Any help would be greatl appreciated.

Thanks,
Shawn

[mdmackillop]

Hi Shawn
Welcome to VBAX.
If you can create a sample workbook to show the layout etc. I'm sure we can assist. Post it using Manage Attachments in the Go Advanced section.
Regards
MD

[stlstephens]

I believe that the file is attached. there are apprx 100 field managers and 1500 stores at this time. I got the copy to a file part to work so far but it errors out after 5 attempts when it goes to open the template file. Could I put a pause there? Since it might be a network issue. The current macro is pasted below:

[VBA]
Private Sub DDO_Button_Click()
Dim DDOField As String
Dim Fname As String
Sheets("P1").PivotTables("GL_Pivot").PivotFields("STATE").CurrentPage = "All"
Sheets("P1").PivotTables("GL_Pivot").PivotFields("Comp").CurrentPage = "All"
Sheets("P1").PivotTables("GL_Pivot").PivotFields("DVP").CurrentPage = "All"
Sheets("P1").PivotTables("GL_Pivot").PivotFields("RVP").CurrentPage = "All"
Sheets("P1").PivotTables("GL_Pivot").PivotFields("DDO").CurrentPage = "All"
Sheets("P1").PivotTables("StoreCount_Pivot").PivotFields("Company").Current Page = "All"
Sheets("P1").PivotTables("StoreCount_Pivot").PivotFields("DVP").CurrentPage = "All"
Sheets("P1").PivotTables("StoreCount_Pivot").PivotFields("RVP").CurrentPage = "All"
Sheets("P1").PivotTables("StoreCount_Pivot").PivotFields("DDO").CurrentPage = "All"
For i = 1 To 101
Sheets("Criteria").Range("G1").FormulaR1C1 = i
Calculate
DDOField = Sheets("Criteria").Range("H1")
Sheets("P1").PivotTables("GL_Pivot").PivotFields("DDO").CurrentPage = DDOField
Sheets("P1").PivotTables("StoreCount_Pivot").PivotFields("DDO").CurrentPage = DDOField
Calculate
Range("A6:P82").Select
Selection.Copy
Workbooks.Open Filename:= _
"\\Financial Scorecard\Field_Level\Field_Template.xls"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Fname = Sheets("DDO_total").Range("K1").Text
ActiveWorkbook.SaveAs Filename:= _
"\\Financial Scorecard\Field_Level\" & Fname, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close
Range("A7").Select
Next
Sheets("P1").PivotTables("GL_Pivot").PivotFields("DDO").CurrentPage = "All"
Sheets("P1").PivotTables("StoreCount_Pivot").PivotFields("DDO").CurrentPage = "All"
Sheets("Criteria").Range("G1") = 1
Sheets("Exec").DDO_Combo.Value = ""
End Sub
[/VBA]

[mdmackillop]

I can't test this, but it seems a big overhead to open a new file each time. Can you open the file outside the loop and then use SaveCopyAs to create your new documents? If the layout is identical for each pivot table, pasting over should delete the previous data.

[stlstephens]

For the first step that should be doable. But when I add the store side I will have to open the saved file correct?

Like I said I am new at this.

Oh..and I got the error to go away. I am doing it on my C drive now. Network was giving me fits.

[mdmackillop]

My thoughts are
Open a file
do
Clear/overwrite the sheets
SaveCopyAs
loop

BTW, Be careful misusing the Template name. It leads to confusion if you are really opening a workbook called "template", and not creating a workbook from a template.

[stlstephens]

How would I go about pasting the store data into a new sheet. I don't want to have to re-open the file each time. The stores will be sorted by manager order already.

[rbrhodes]

Hi Shawn,

Not much we can do with only the template file. Could you provides some fake before/after data in a workbook?