Announcement

Collapse
No announcement yet.

Trying to merge workbooks in a folder, but converting from a full sheet to a range

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Trying to merge workbooks in a folder, but converting from a full sheet to a range



    Iím looking for some help in converting my current code into a range instead of a full sheet

    Instead of merging sheet 1 from multiple workbooks into a master workbook, I only need to copy a specific range on sheet one of each workbook and paste it within the master workbook. I need each sheet in the master workbook to have the original sheet name from sheet1 from the workbooks.

    Code:
    Sub MergeMultipleWorkbooks()
    
    'Declare variables
        Dim Path
        FileName As String
        Dim was As Worksheet
    
    ' Disable updates to increase performance.
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        
    'Set path and file extensions
        Path = "D:\Workbooks\"
        FileName = Dir(Path & "*.xlsm")
    
    'Start of loop
        Do While FileName <> "" 'Open if filename has a name
    
            With Workbooks.Open(FileName:=Path & FileName, ReadOnly:=True)
                .Worksheets(1).Copy After:=ThisWorkbook.Sheets(1)
                .Close False
            End With
    
            FileName = Dir()
        Loop
        
    ' Re-enable updating.
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        
        MsgBox "Files has been copied Successfully", , "MergeMultipleExcelFiles"
    End Sub

  • #2


    What is the specific range on sheet one of each workbook that you want to copy?
    You can say "THANK YOU" for help received by clicking "Like" in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    Comment

    Working...
    X