Updating worksheets

[joebloggs]

Hi All,

I've got a couple of hundred spreadsheets that have a default worksheet listing dates.

Is there a vba, batch file script that will copy a new version of that worksheet to every spreadsheet in a single directory?

The worksheet is named exactly the same "Holidays".

thanks in advance

 

[Joe4]

I've got a couple of hundred spreadsheets that have a default worksheet listing dates.

Do you really mean "spreadsheets/worksheets", which mean multiple tab in a single Excel workbook, or do you really mean "workbooks", which means multiple files?

If really "worksheets", how can we identify which sheets need updating? Is it ALL of them in the workbook, or just specific ones?
If really "workbooks", are they all in the same folder? Is it every workbook in that folder? If not, how do we identify which ones need updating?

And where is the file that has the new data located? How is it structured?

 

[joebloggs]

Hi Joe4,

correct, I'm talking about a single tab within a spreadsheet or as you refer "spreadsheet/worksheet". There are several tabs within each document, but I'm looking to update the data within one specific tab (worksheet) across a lot of spreadsheets all with unique names. The key is that the tab is named exactly the same across all spreadsheets.

the new data can be in the same or different directory, whatever is needed to make the process easy.

 

[Joe4]

the new data can be in the same or different directory, whatever is needed to make the process easy.

Before we can write code to do what you want, you need to laid out the conditions for this, i.e.
- Where should we be looking for these files?
- How do we identify which files we should be opening and updating and which ones we shouldn't?

 

[joebloggs]

Look for the files in c:/spreadsheets

update all xlsm files in that directory no matter what they are called. Ie *.xlsm

get the updated data from c:/new/spreadsheets/newdata.xls and tab "holiday".

 

[Joe4]

Try this code. I added lots of documentation so that you can see what is going on.

Sub MyHolidayReplace()

    Dim srcWB As Workbook
    Dim dstWB As Workbook
    Dim fpath As String
    Dim fname
    
    Application.ScreenUpdating = False
    
'   Open workbook with new data to copy
    Workbooks.Open Filename:="c:/new/spreadsheets/newdata.xls"
    
'   Capture workbook as source workbook
    Set srcWB = ActiveWorkbook
    
'   Activate holiday sheet
    Sheets("Holiday").Activate
    
'   Set file path to look through for files (include extension)
    fpath = "C:\Spreadsheets\*.xlsm"
    
'   Loop through all "xlsm" files in folder
    fname = Dir(fpath)
    While fname <> ""
'       Open workbook
        Workbooks.Open Filename:=fname
'       Capture workbook
        Set dstWB = ActiveWorkbook
'       Go to holiday tab and clear data
        Sheets("Holiday").Activate
        Cells.ClearContents
'       Go to source WB and copy cells
        srcWB.Activate
        Cells.Copy
'       Go to destination workbook and paste
        dstWB.Activate
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
'       Save and close destination WB
        dstWB.Save
        dstWB.Close
'       Reset for next file
        fname = Dir
    Wend
        
'   Close source wb
    srcWB.Close
        
    Application.ScreenUpdating = True
    
    MsgBox "Replacement Complete!", vbOKOnly
        
End Sub