Results 1 to 6 of 6

Thread: Need help in merging file using macro

  1. #1
    Seeker rahiljaved's Avatar
    Join Date
    Oct 2017
    Posts
    19
    Articles
    0
    Excel Version
    2013

    Need help in merging file using macro



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have various excel files that I recieved every week with data in it. I need a macro to pick data from each of those files from cell B9 to the end where data ends (its different in all files) and paste it in target file one over the other. Example in file 1 data starts from B9 - G59 so it copy and paste in target file from A2 - F52 then pick data from file 2 data starts from B9 - G69 and paste in target file from A53 - F102 and so on so forth. The target file I have set some formula in pivot table so that when the data moves in it automatically picks up and from column H - N formula will work using data that is copied from these files.

    Background: I have these files coming in email so I write a macro in outlook to save all the attachments automatically one click in a folder. Now the second step is to merge all those files in one but only specific cells which has data in it becuase from A1 - B8 there are headers and other unnecessary data which i dont need in target file so that is why i need it from B9 - the end of the data range. Please help thanks in advance.

  2. #2
    Acolyte RET's Avatar
    Join Date
    Nov 2020
    Location
    Spain
    Posts
    22
    Articles
    0
    Excel Version
    2019
    And why don't you use PowerQuery? It is easier than with a macro

  3. #3
    Seeker rahiljaved's Avatar
    Join Date
    Oct 2017
    Posts
    19
    Articles
    0
    Excel Version
    2013
    becuase its for multiple users and not all users have power query option available.

  4. #4
    Seeker jolivanes's Avatar
    Join Date
    May 2020
    Posts
    19
    Articles
    0
    Excel Version
    2007 & 2016
    Try so.
    The Master Workbook and all the workbooks that you are copying from need to be (saved) in the same folder.
    Change all references, sheet names etc as required.
    Code:
    Sub Copy_First_Sheets_Only()
        Dim wb As String
        Application.ScreenUpdating = False
        wb = Dir(ThisWorkbook.Path & "\*")
        Do Until wb = ""
            If wb <> ThisWorkbook.Name Then
                Workbooks.Open ThisWorkbook.Path & "\" & wb
                With Workbooks(wb).Sheets(1)
                    .Range("B9:G" & .Cells(.Rows.Count, 2).End(xlUp).Row).Copy ThisWorkbook.Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1)
                End With
                Workbooks(wb).Close False
            End If
            wb = Dir
        Loop
        Application.ScreenUpdating = True
    End Sub

  5. #5
    Seeker rahiljaved's Avatar
    Join Date
    Oct 2017
    Posts
    19
    Articles
    0
    Excel Version
    2013
    Working great!!! Thanks alot for your help, realy apprecaited.

  6. #6
    Seeker jolivanes's Avatar
    Join Date
    May 2020
    Posts
    19
    Articles
    0
    Excel Version
    2007 & 2016
    Thank you for letting us know.
    Good luck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •