2

I have sales global sales data in very large files which I need to filter by country and product and then analyze. Since I will need to perform this same analysis over and over again with different country/product combinations I am trying to do this with a macro.

This is what the macro does:

  1. Open the source files with global data;
  2. Filters the data;
  3. Copies it and pastes it into a workbook which houses the macro;
  4. Recalculates and refreshes the workbook and;
  5. Saves a copy of the file to another folder.

The macro appears to run fine and the files are being saved, however I am running into 2 problems on steps 4 and 5:

  • The pivot tables do not seem to be refreshing - this may be because the second problem:
  • All pivot tables in the saved copy still refer to the original file

I'd prefer to fix both problems by generating the copied file without links, but I'm also open to any bandaids that might allow me to force the copied file to link to itself (this doesn't seem to work when I do it manually).

I have the full code if you want to see it all, but because I suspect the issue is in how I'm saving the file I'll just paste that piece here. Let me know if you need to see another part of the macro. Any help would be much appreciated, thanks in advance.

Save Function:

Public Sub SaveAsCopy(filePath As String)
   Dim updateStatus As Boolean

   'Check current status of Alerts
   updateStatus = Application.DisplayAlerts
   'Turn off alerts
   Application.DisplayAlerts = False

   ThisWorkbook.Sheets.Copy 'creates new workbook without macros"
   'The New workbook copy is now the Active workbook
   'Delete Control Sheet
   ActiveWorkbook.Sheets(1).Delete

   'Save Macro free version and close
   ActiveWorkbook.SaveAs Filename:=filePath, FileFormat:=51
   ActiveWorkbook.Close

   'Revert back to origional alert status
   Application.DisplayAlerts = updateStatus

End Sub

Function Call:

Call SaveAsCopy(filePath)
pnuts
  • 58,317
  • 11
  • 87
  • 139
l85m
  • 808
  • 1
  • 10
  • 19
  • +1 good question. The normal solution is to replace the link created back to the source workbook with the activeworkbook (ie the newly created file). – brettdj Jul 11 '12 at 05:18
  • Thanks Brett - I had the same thought and tried it, there were lots of examples to look at out there, but was unable to get it to work for some reason. I kept getting errors on the source data assignment no matter how many different ways I tried it. What did work was not copying the file and saving under a different name. This isn't ideal though because the files still have macros and a bunch of unnecessary data. Hopefully someone who knows the save functions really well can shed some light on a better way. – l85m Jul 11 '12 at 15:58

1 Answers1

0

Will share the workaround I developed since I didn't get any bites on a more elegant solution:

Public Sub SaveAsCopy(filePath As String)
   Dim updateStatus As Boolean

   'Check current status of Alerts
   updateStatus = Application.DisplayAlerts
   'Turn off alerts
   Application.DisplayAlerts = False

   'Hide Control Sheet
   ActiveWorkbook.Sheets(1).Visible = False

   'Save Macro free version and close
   ActiveWorkbook.SaveAs Filename:=filePath, FileFormat:=52

   'Unhide
   ActiveWorkbook.Sheets(1).Visible = True

   'Revert back to original alert status
   Application.DisplayAlerts = updateStatus

End Sub

At the end of the parent function I close the current file, reopen the original, and loop through the new workbooks to remove macros.

l85m
  • 808
  • 1
  • 10
  • 19