VBA: Copy / Paste Worksheets into consolidation workbook

chet645

Board Regular
Joined
Nov 10, 2010
Messages
65
Office Version
  1. 365
Platform
  1. Windows
All, I've built a macro (not super experienced with vba) that will copy and paste up to 12 worksheets from various excel models into a consolidator model. Right now, the macro runs by clicking a button, after which the macro will ask me if I want to proceed, then copy and paste all files into the consolidator workbook. The problem is, I don't always need to consolidate all files. I may only need to update 1 or 2 files. Wondering if there is a way to adjust my code so that a dialog box pops up at the beginning of the macro, allowing me to select which workbooks I want to consolidate. Below is my code. Any ideas are much appreciated.

VBA Code:
Sub Rollup()

'Application.ScreenUpdating = False

Dim Loan As Integer
Dim Count As Integer
Dim Rangecount As Integer
Dim File As String
Dim Buttons As Integer, Result As Integer


Count = Range("Count")
Rangecount = Range("Rangecount")
Control = Range("Control")
Range_control = Range("Range_Control")



Buttons = vbYesNo + vbQuestion + vbDefaultButton1

Result = MsgBox("Data from " & Count & " files will be extracted.  Proceed?", Buttons)

If Result = vbYes Then
    
Clear_Macro
    
    For Loan = 1 To Count Step 1

        Application.Goto Reference:="control"
        ActiveCell.Value = Loan
        File = Range("Rollfile")
        Workbooks.Open Filename:=File, UpdateLinks:=0
        
        'Calculate
        
        ActiveWindow.ActivateNext
        
        For Pastenumber = 1 To Rangecount Step 1
        
            Application.Goto Reference:="Range_Control"
            ActiveCell.Value = Pastenumber
            
            Dim MyRef As String
            MyRef = Range("ActiveName").Value
            
            Application.Goto Reference:=MyRef
            'ActiveCell.Offset(Loan, Pastenumber).Range("a1").Select
            Pastetarget = Range("Pasterange")
            ActiveWindow.ActivateNext
            
            
            Worksheets(Pastetarget).Activate
            
            Cells.Copy
            Cells.FormatConditions.Delete
            'Cells.Select
            'Selection.Copy
            
            'Range(Pastetarget).Copy
            ActiveWindow.ActivateNext
            Application.DisplayAlerts = False
            Selection.PasteSpecial Paste:=xlValues
            Selection.PasteSpecial Paste:=xlFormats
        
        Next Pastenumber
          
        ActiveWindow.ActivateNext
        Application.CutCopyMode = False
        ActiveWorkbook.Close SaveChanges:=False
        
    Next Loan
    
MsgBox ("Data extracted.")
    
Else
    MsgBox "You chose to abort the operation"

End If
   
End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Oddball2020

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
All, I've built a macro (not super experienced with vba) that will copy and paste up to 12 worksheets from various excel models into a consolidator model. Right now, the macro runs by clicking a button, after which the macro will ask me if I want to proceed, then copy and paste all files into the consolidator workbook. The problem is, I don't always need to consolidate all files. I may only need to update 1 or 2 files. Wondering if there is a way to adjust my code so that a dialog box pops up at the beginning of the macro, allowing me to select which workbooks I want to consolidate. Below is my code. Any ideas are much appreciated.

VBA Code:
Sub Rollup()

'Application.ScreenUpdating = False

Dim Loan As Integer
Dim Count As Integer
Dim Rangecount As Integer
Dim File As String
Dim Buttons As Integer, Result As Integer


Count = Range("Count")
Rangecount = Range("Rangecount")
Control = Range("Control")
Range_control = Range("Range_Control")



Buttons = vbYesNo + vbQuestion + vbDefaultButton1

Result = MsgBox("Data from " & Count & " files will be extracted.  Proceed?", Buttons)

If Result = vbYes Then
   
Clear_Macro
   
    For Loan = 1 To Count Step 1

        Application.Goto Reference:="control"
        ActiveCell.Value = Loan
        File = Range("Rollfile")
        Workbooks.Open Filename:=File, UpdateLinks:=0
       
        'Calculate
       
        ActiveWindow.ActivateNext
       
        For Pastenumber = 1 To Rangecount Step 1
       
            Application.Goto Reference:="Range_Control"
            ActiveCell.Value = Pastenumber
           
            Dim MyRef As String
            MyRef = Range("ActiveName").Value
           
            Application.Goto Reference:=MyRef
            'ActiveCell.Offset(Loan, Pastenumber).Range("a1").Select
            Pastetarget = Range("Pasterange")
            ActiveWindow.ActivateNext
           
           
            Worksheets(Pastetarget).Activate
           
            Cells.Copy
            Cells.FormatConditions.Delete
            'Cells.Select
            'Selection.Copy
           
            'Range(Pastetarget).Copy
            ActiveWindow.ActivateNext
            Application.DisplayAlerts = False
            Selection.PasteSpecial Paste:=xlValues
            Selection.PasteSpecial Paste:=xlFormats
       
        Next Pastenumber
         
        ActiveWindow.ActivateNext
        Application.CutCopyMode = False
        ActiveWorkbook.Close SaveChanges:=False
       
    Next Loan
   
MsgBox ("Data extracted.")
   
Else
    MsgBox "You chose to abort the operation"

End If
  
End Sub
I didn't notice a "open all files in folder" type of code... are you opening off a list of names in a range in your consolidator sheet?
 

chet645

Board Regular
Joined
Nov 10, 2010
Messages
65
Office Version
  1. 365
Platform
  1. Windows
I didn't notice a "open all files in folder" type of code... are you opening off a list of names in a range in your consolidator sheet?
Yes, that's right. I copy and paste the file names and locations to a range, and the macro loops through each file in sequential order, copying and pasting.
 

Oddball2020

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
Yes, that's right. I copy and paste the file names and locations to a range, and the macro loops through each file in sequential order, copying and pasting.
In that case, on that list, mark with an "X" to the right of of the names of the files you want to open, then change the opening loop that if there's an X, then it'll open, otherwise it doesn't.

You'll have to tweak how you're opening it to a traditional worbork.open "File Path\File Name" type of syntax, and put that inside an IF THEN statement. (So say you're list of files is in Col A and 12 rows long, then mark X's in column B for the ones you want open, and then make a loop stating that for each row of that 12, look for the X and if it's there, then use the file name in Col A to open, otherwise it skips and doesn't open.

Or you can put in the start of the open loop the VBYesNo box, and ask for each file "Do you Want to Open file XYZ?" , and it'll skip over the No's.
 

chet645

Board Regular
Joined
Nov 10, 2010
Messages
65
Office Version
  1. 365
Platform
  1. Windows
In that case, on that list, mark with an "X" to the right of of the names of the files you want to open, then change the opening loop that if there's an X, then it'll open, otherwise it doesn't.

You'll have to tweak how you're opening it to a traditional worbork.open "File Path\File Name" type of syntax, and put that inside an IF THEN statement. (So say you're list of files is in Col A and 12 rows long, then mark X's in column B for the ones you want open, and then make a loop stating that for each row of that 12, look for the X and if it's there, then use the file name in Col A to open, otherwise it skips and doesn't open.

Or you can put in the start of the open loop the VBYesNo box, and ask for each file "Do you Want to Open file XYZ?" , and it'll skip over the No's.
Thanks for taking the time! Let me give that a try, and I'll revert back.
 

Forum statistics

Threads
1,141,309
Messages
5,705,674
Members
421,404
Latest member
Mikecollo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top