Re Using Same Macro In Different Workbooks

[russlock]

Good morning and i hope someone is able to help.

I have a couple of hundred customer spreadsheets with their product prices on and we are implementing a price increase, so I have a formula in a different spreadsheet which I copy into each customers spreadsheet which changes all their prices. I set this up as a macro to copy and paste the formula but I am having problems where the macro only references the current spreadsheet. if I open a new customers spreadsheet and run the same macro, I hit an error and I think it is the Windows("Test1.xlsx").Activate part below. Is there a way to reference ("current open spreadsheet") for example?

I am sure there are easier ways to achieve what I am looking to do, I just want to cut down on the repetitive work.

many thanks



-----------------

Sub Copy()
'
' Copy Macro
'
' Keyboard Shortcut: Ctrl+a
'
Windows("Copy of CCP-price increase formula-Sheet2").Activate
Selection.Copy
Windows("Test1.xlsx").Activate
ActiveSheet.Paste
Range("H2:J2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("H2:J885")
Range("H2:J885").Select
End Sub

 

[Alex Blakenburg]

Where are you storing the macro and how are you calling it to run it.

 

[russlock]

Where are you storing the macro and how are you calling it to run it.

Thanks for the reply. It is stored in the personal file . I just recorded it and it asked me where to save and a shortcut to run it, ctrl + a

 

[Alex Blakenburg]

I haven't fully tested this but assuming, that you are copying the formula from
"Copy of CCP-price increase formula-Sheet2"
You need to add the file extension eg xlsx or xlsm
You need to provide the sheet name in that workbook
Make sure when you run it the Target (destination) workbook and correct worksheet is active.
- since this is the one that changes names you want this to be the active one when you call the macro.

Sub Copyv02()
'
' Copy Macro
'
' Keyboard Shortcut: Ctrl+a
'
    Dim destWB As Workbook
    Dim destSht As Worksheet
    
    Dim srcWB As Workbook
    Dim srcSht As Worksheet
    
    Set destWB = ActiveWorkbook
    Set destSht = destWB.ActiveSheet
    Set srcWB = Workbooks("Copy of CCP-price increase formula-Sheet2.xlsm")   ' XXX Change the file extension to whatever it is
    Set srcSht = srcWB.Worksheets("Sheet1")                                                          ' XXX Change the sheet name to what it should be
     
    srcSht.Copy destSht

    Application.CutCopyMode = False
    destSht.Range("H2:J2").AutoFill Destination:=Range("H2:J885")
    destSht.Activate
    'Range("H2:J885").Select
    destSht.Range("H2:J885").Select ' Not sure why you would want to do this
End Sub

 

[My Aswer Is This]

When you say:
I have a couple of hundred customer spreadsheets

The term: "spreadsheets"
Is a term used by Excel in the 1980's which referred to what we now know as a "Workbook"
Modern day Excel uses the terms:
Workbook
And
Sheets or Worksheets

So do you really have hundreds of Workbooks or do you mean hundreds of Worksheets?

 

[russlock]

When you say:
I have a couple of hundred customer spreadsheets

The term: "spreadsheets"
Is a term used by Excel in the 1980's which referred to what we now know as a "Workbook"
Modern day Excel uses the terms:
Workbook
And
Sheets or Worksheets

So do you really have hundreds of Workbooks or do you mean hundreds of Worksheets?

(Now you can tell how old I am ?) Yes, I have about 200 workbooks, and each are named differently, and I need to amend prices on each using the formula on another workbook and then upload back into our server for the new prices to take effect. Thanks

 

[Alex Blakenburg]

What is the file extension of the workbook that contains the formulas ?
I assume this is the workbook name "Copy of CCP-price increase formula-Sheet2"
What is the sheetname of the sheet to use, is it Sheet2 ?
Is the intention to copy the whole of the sheet ?

In the 200 workbooks what is the sheet name of the sheet to copy to, if it varies how can the sheet be identified ?
Is it all files in a folder ?

Since I am in a different time zone the below will give you a starting point if @My Aswer Is This want to modify help you with any modifications.

Create a new folder and put a COPY of a few of your 200 workbooks for testing.
Have the workbook with the formulas in it open when you run the macro but the name needs to be right in the code.
Have a look at the lines I have marked with XXX and see if you need to change any of them.

As noted in the code the main code to open all files in a folder came from "www.TheSpreadsheetGuru.com"

Sub LoopAllExcelFilesInFolder()
    'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
    'SOURCE: www.TheSpreadsheetGuru.com
    'Modified by Alex for MrExcel Question
    
    Dim destWB As Workbook                      'XXX Changed for clarity
    Dim myPath As String
    Dim myFile As String
    Dim myExtension As String
    Dim FldrPicker As FileDialog
    
    'XXX Modification for active part of Procedure
    Dim destSht As Worksheet
    Dim srcWB As Workbook
    Dim srcSht As Worksheet
    Set srcWB = Workbooks("Copy of CCP-price increase formula-Sheet2.xlsx")     'XXX Change to Workbook containing formula
    Set srcSht = srcWB.Worksheets("Sheet2")                                                            'XXX Change to Workbook containing formula
    
    'Optimize Macro Speed
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      Application.Calculation = xlCalculationManual
    
    'Retrieve Target Folder Path From User
      Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
    
        With FldrPicker
          .Title = "Select A Target Folder"
          .AllowMultiSelect = False
            If .Show <> -1 Then GoTo NextCode
            myPath = .SelectedItems(1) & Application.PathSeparator
        End With
    
    'In Case of Cancel
NextCode:
      myPath = myPath
      If myPath = "" Then GoTo ResetSettings
    
    'Target File Extension (must include wildcard "*")
      myExtension = "*.xls*"
    
    'Target Path with Ending Extention
      myFile = Dir(myPath & myExtension)
    
    'Loop through each Excel file in folder
      Do While myFile <> ""
        'Set variable equal to opened workbook
          Set destWB = Workbooks.Open(Filename:=myPath & myFile)
          Set destSht = destWB.Worksheets("Sheet1")                     'XXX Confirm this is the sheet to use
        
        'Ensure Workbook has opened before moving on to next line of code
          DoEvents
        
        'XXX Action part of Procedure
        srcSht.UsedRange.Copy destSht.Range(srcSht.UsedRange.Address)   'XXX Confirm intentions to copy whole sheet
        Application.CutCopyMode = False
        destSht.Range("H2:J2").AutoFill Destination:=destSht.Range("H2:J885")
        destSht.Activate
        destSht.Range("H2:J885").Select ' Not sure why you would want to do this
        
        'Save and Close Workbook
          destWB.Close SaveChanges:=True
          
        'Ensure Workbook has closed before moving on to next line of code
          DoEvents
    
        'Get next file name
          myFile = Dir
      Loop
    
    'Message Box when tasks are completed
      MsgBox "Task Complete!"
    
ResetSettings:
      'Reset Macro Optimization Settings
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True

End Sub

 

[russlock]

What is the file extension of the workbook that contains the formulas ?
I assume this is the workbook name "Copy of CCP-price increase formula-Sheet2"
What is the sheetname of the sheet to use, is it Sheet2 ?
Is the intention to copy the whole of the sheet ?

In the 200 workbooks what is the sheet name of the sheet to copy to, if it varies how can the sheet be identified ?
Is it all files in a folder ?

Since I am in a different time zone the below will give you a starting point if @My Aswer Is This want to modify help you with any modifications.

Create a new folder and put a COPY of a few of your 200 workbooks for testing.
Have the workbook with the formulas in it open when you run the macro but the name needs to be right in the code.
Have a look at the lines I have marked with XXX and see if you need to change any of them.

As noted in the code the main code to open all files in a folder came from "www.TheSpreadsheetGuru.com"

Sub LoopAllExcelFilesInFolder()
    'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
    'SOURCE: www.TheSpreadsheetGuru.com
    'Modified by Alex for MrExcel Question
 
    Dim destWB As Workbook                      'XXX Changed for clarity
    Dim myPath As String
    Dim myFile As String
    Dim myExtension As String
    Dim FldrPicker As FileDialog
 
    'XXX Modification for active part of Procedure
    Dim destSht As Worksheet
    Dim srcWB As Workbook
    Dim srcSht As Worksheet
    Set srcWB = Workbooks("Copy of CCP-price increase formula-Sheet2.xlsx")     'XXX Change to Workbook containing formula
    Set srcSht = srcWB.Worksheets("Sheet2")                                                            'XXX Change to Workbook containing formula
 
    'Optimize Macro Speed
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      Application.Calculation = xlCalculationManual
 
    'Retrieve Target Folder Path From User
      Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
 
        With FldrPicker
          .Title = "Select A Target Folder"
          .AllowMultiSelect = False
            If .Show <> -1 Then GoTo NextCode
            myPath = .SelectedItems(1) & Application.PathSeparator
        End With
 
    'In Case of Cancel
NextCode:
      myPath = myPath
      If myPath = "" Then GoTo ResetSettings
 
    'Target File Extension (must include wildcard "*")
      myExtension = "*.xls*"
 
    'Target Path with Ending Extention
      myFile = Dir(myPath & myExtension)
 
    'Loop through each Excel file in folder
      Do While myFile <> ""
        'Set variable equal to opened workbook
          Set destWB = Workbooks.Open(Filename:=myPath & myFile)
          Set destSht = destWB.Worksheets("Sheet1")                     'XXX Confirm this is the sheet to use
     
        'Ensure Workbook has opened before moving on to next line of code
          DoEvents
     
        'XXX Action part of Procedure
        srcSht.UsedRange.Copy destSht.Range(srcSht.UsedRange.Address)   'XXX Confirm intentions to copy whole sheet
        Application.CutCopyMode = False
        destSht.Range("H2:J2").AutoFill Destination:=destSht.Range("H2:J885")
        destSht.Activate
        destSht.Range("H2:J885").Select ' Not sure why you would want to do this
     
        'Save and Close Workbook
          destWB.Close SaveChanges:=True
       
        'Ensure Workbook has closed before moving on to next line of code
          DoEvents
 
        'Get next file name
          myFile = Dir
      Loop
 
    'Message Box when tasks are completed
      MsgBox "Task Complete!"
 
ResetSettings:
      'Reset Macro Optimization Settings
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True

End Sub

That is very precise Alex and thank you for your time. I am going to test this with half a dozen or so of the customers workbooks and i can put them all in the same folder, no problem. The formula is just in a part of "Copy of CCP-price increase formula-Sheet2" as i just need it to increase on the customers workbooks by a set amount, show before and after price and then add the tax, so basically it is just six cells from the "Copy of CCP-price increase formula-Sheet2" workbook. i will have a play and give you an update. Again, many thanks as all this is new to me.

 

[Alex Blakenburg]

Thank for the update and let me know how you go.
It sounds like you will need to specify a range where I have put in "usedrange" as being the range to copy. If you need help with that just let me know.

 

[My Aswer Is This]

I'm glad to see others are here helping you with this need of your.
When attempting to do something like. There is a need for a lot of very specific details. Like the path to the folder where all these workbooks are located. Do we do all the workbooks in the folder or just some. If it is just some then we would need the name of all 200 workbooks and the sheet names and the ranges and maybe more .
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.