Dragging cell data down across 1000 workbooks possible?

**notanaccountant** · 05-09-2018, 05:24 PM

I have a tedious accounting dilemma

Each month (until I find a better accounting system) I must open about 1000 workbooks individually to drag down one cell from the previous months workbook (its an ongoing monthly task with 1000 workbooks). I am looking for a way to make this easier since this just one aspect of what I need to accomplish. Ideally I would like to see if there was a way to update a workbook, take data from a cell and move it another workbook to make a list of 1000 updates/changes.

My company uses MS Office 2010.

I hope my dilemma made a little sense!

**alansidman** · 05-09-2018, 05:28 PM

Are all 1000 workbooks in the same sub folder?
Are there any files in the sub folder that will not get updated?

Explain the manual mechanics in detail of what you do so that we can determine an automated solution that replicates this task.

**notanaccountant** · 05-10-2018, 10:09 AM

Originally Posted by alansidman

Are all 1000 workbooks in the same sub folder?
Are there any files in the sub folder that will not get updated?

Explain the manual mechanics in detail of what you do so that we can determine an automated solution that replicates this task.

All 1000 workbooks can be in the same folder, so I will say yes to that question. All 1000 workbooks need to get updated each month and I will explain the process below:

Each month an account reconciliation process is done and the bank statement shows the new interest rate to update on each of the 1000 workbooks. Each workbook has a configured formula to show the balance and interest accrued and by adding the ability to input the new interest rate, it would save me time from opening 1000 workbooks, adding the new interest rate and jotting down the new balance total.

**Richard Buttrey** · 05-09-2018, 06:23 PM

As Alan intimates you'll need a VBA macro for this.

But a more fundamental question is why are you using 1000 workbooks? From where does the data come, how are they updated and by whom? 1000 different people?
Once all the data was combined into a single workbook could you see new data being added to the single workbook instead?

In other words should our effort be concentrated into getting stuff into a single more easily manageable workbook (and don't assume tnat necessarily means 1000 worksheets in the single wb), or providing something that has to iterte through 1000 workbooks each day?

**notanaccountant** · 05-10-2018, 10:14 AM

Originally Posted by Richard Buttrey

As Alan intimates you'll need a VBA macro for this.

But a more fundamental question is why are you using 1000 workbooks? From where does the data come, how are they updated and by whom? 1000 different people?
Once all the data was combined into a single workbook could you see new data being added to the single workbook instead?

In other words should our effort be concentrated into getting stuff into a single more easily manageable workbook (and don't assume tnat necessarily means 1000 worksheets in the single wb), or providing something that has to iterte through 1000 workbooks each day?

Hi Richard,

To answer your first points, the reason I have 1000 workbooks is because each workbook belongs to an account that we have records for and the workbook contains the original balance plus an interest calculation that I add in a new interest rate to each month. This increases the balance and I need to get the total accrual for all 1000 accounts each month and show the increase on my balance books. So yes, they are technically 1000 different people. Since all 1000 workbooks contain deposits at different dates, I couldnt just combine all 1000 "people" into one workbook since the deposit amounts are different and the dates as well.

I just need to do this each month as our bank statement that holds all 1000 "people" has a new interest rate to be input for the formula workbook.

To reiterate the reason, I'll paste what I replied with above:
Each month an account reconciliation process is done and the bank statement shows the new interest rate to update on each of the 1000 workbooks. Each workbook has a configured formula to show the balance and interest accrued and by adding the ability to input the new interest rate, it would save me time from opening 1000 workbooks, adding the new interest rate and jotting down the new balance total.

**alansidman** · 05-10-2018, 03:17 PM

Here is some code gleaned from https://www.thespreadsheetguru.com/t...a-given-folder

You will need to give us specifically what you need to change in each of the 1000 workbooks as that will need to be added into the code where I have indicated. Be as specific as possible. The locations in each workbook needs to be in the exact same location.

Option Explicit

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

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

'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) & "\"
    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 wb = Workbooks.Open(Filename:=myPath & myFile)
    
    'Ensure Workbook has opened before moving on to next line of code
      DoEvents
    
    'Here is where you would enter code to update the cell with appropriate information
    'You will need to change the following code as this updates a range of cells color
    
      wb.Worksheets(1).Range("A1:Z1").Interior.Color = RGB(51, 98, 174)
    
    'Save and Close Workbook
      wb.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

Dragging cell data down across 1000 workbooks possible?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Dragging cell data down across 1000 workbooks possible?

Re: Dragging cell data down across 1000 workbooks possible?

Re: Dragging cell data down across 1000 workbooks possible?

Re: Dragging cell data down across 1000 workbooks possible?

Re: Dragging cell data down across 1000 workbooks possible?

Re: Dragging cell data down across 1000 workbooks possible?

Thread Information

Users Browsing this Thread

Similar Threads

How to apply Formula throughout the column by data validation without dragging the cell

Dragging an inconsistent formula but cell reference from source data is still in series

Insert 10 templates sheets with formulas to 1000 workbooks in a folder

Linking to data in 1000+ source workbooks?

transfer data from 1000 workbooks to one workbook w

Need macro to updated column C's width on 1000+ workbooks.

Dragging cell data right, excel starts adding to value

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions