Results 1 to 10 of 10

Thread: Import row data from multiple worksheets to generate a report using vba

  1. #1
    Junior Member
    Join Date
    Oct 2014
    Posts
    26
    Rep Power
    0

    Import row data from multiple worksheets to generate a report using vba

    Hi All,

    I would like to ask assistance in exporting data from several worksheets to create a summary report on a daily basis. Data are from xlsm files and they have a common name (Part of Backlog_Report_Generator_ver10 xlsm followed by date and time and .xlsm). The number of workbook where to import data varies also the number or rows to be imported depending on the number or people assigned to do the tasks. Below is the snippet of my file. Please help.

    File snippet:
    backlog_report.png
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by jeremiah_j2k; 11-20-2014 at 09:38 AM.

  2. #2
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Not clear.
    Please provide additional information.

  3. #3
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    Images are pretty much useless to help solve issues. Upload sample worksheets with enough data to allow analysis and determine a suitable solution.

  4. #4
    Junior Member
    Join Date
    Oct 2014
    Posts
    26
    Rep Power
    0
    Hello LalitPandey87,

    I will upload the file shortly... thanks

  5. #5
    Junior Member
    Join Date
    Oct 2014
    Posts
    26
    Rep Power
    0
    Hello alansidman,

    Thanks for the advise. Below are the files that might help you determine a suitable solution. Thanks in advanced


    Excel File:
    BLver10-Test


    File snippet:
    backlog_report.png

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Upload the file here in this forum. Click on Go Advanced button and you can upload the files. If it's too large, zip the file and upload.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  7. #7
    Junior Member
    Join Date
    Oct 2014
    Posts
    26
    Rep Power
    0
    Quote Originally Posted by Admin View Post
    Upload the file here in this forum. Click on Go Advanced button and you can upload the files. If it's too large, zip the file and upload.

    Thanks Admin... file has been attached

  8. #8
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Try this.

    Code:
    Option Explicit
    Sub kTest()
        
        Dim Foldr       As String, FName       As String
        Dim WbkA        As Workbook, WbkT       As Workbook
        Dim Dest        As Range, StartCell   As String
        Dim r   As Long
        
        With Application.FileDialog(msoFileDialogFolderPicker)
            .AllowMultiSelect = False
            .Title = "Select the raw files folder..."
            If .Show = -1 Then
                Foldr = .SelectedItems(1)
            Else: Exit Sub
            End If
        End With
        Foldr = Foldr & Application.PathSeparator
        
        FName = Dir(Foldr & "*.xlsm")
        
        If Len(FName) = 0 Then Exit Sub
        
        Application.ScreenUpdating = 0
        
        Set WbkT = ThisWorkbook
        Set Dest = WbkT.Worksheets("Report").Range("C27")
        
        StartCell = "C8"        '<<< adjust
        
        If MsgBox("Do you want to overwrite the data?", vbQuestion + vbYesNo) = vbNo Then
            Set Dest = Dest.Parent.Cells(Dest.Parent.Rows.Count, Dest.Column).End(3)(2)
        Else
            Dest.Resize(Dest.CurrentRegion.Rows.Count, 8).ClearContents
        End If
        
        Do While Len(FName)
            If Not WbkT.Name = FName Then
                Set WbkA = Workbooks.Open(Foldr & FName, 0)
                With WbkA.Worksheets(1).Range(StartCell) 'data from the 1st sheet
                    Debug.Print Dest.Address
                    r = .CurrentRegion.Rows.Count - 1
                    Dest.Resize(r, 8).Value = .Resize(r, 8).Value2
                    Set Dest = Dest.Offset(r)
                End With
                WbkA.Close 0
                Set WbkA = Nothing
            End If
            FName = Dir()
        Loop
        Application.ScreenUpdating = 1
        MsgBox "Done!", vbInformation, "Excelfox.com"
        
    End Sub
    Allow you to select the folder and the macro will do the rest for you !
    Last edited by Admin; 11-20-2014 at 11:20 AM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  9. #9
    Junior Member
    Join Date
    Oct 2014
    Posts
    26
    Rep Power
    0
    Hello Admin... Thanks so much for the help... Your code works as awesomely cool.. thank you thank you thank you!!

  10. #10
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Quote Originally Posted by jeremiah_j2k View Post
    Hello Admin... Thanks so much for the help... Your code works as awesomely cool.. thank you thank you thank you!!
    You are welcome
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 10
    Last Post: 08-31-2013, 06:56 PM
  2. Import Data From Multiple Workbooks To Another
    By Jorrg1 in forum Excel Help
    Replies: 2
    Last Post: 05-13-2013, 05:00 PM
  3. Replies: 1
    Last Post: 05-09-2013, 08:56 AM
  4. Replies: 7
    Last Post: 05-08-2013, 07:12 PM
  5. Replies: 9
    Last Post: 12-04-2012, 09:45 PM

Posting Permissions

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