Consulting

Results 1 to 11 of 11

Thread: VBA Help needed

  1. #1
    VBAX Newbie
    Joined
    Feb 2018
    Posts
    5
    Location

    VBA Help needed

    Hello all,
    Id like to know if there is a vba code that will combine excel sheets from a workbook in a given folder the data should be consolidated based on headers.

    Ex : say i have a folder that has workbooks from 4 different people so a vba code that will ask me to choose a folder which has all workbooks that needs to be consolidated and save it to same folder

  2. #2
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    549
    Location
    Hi Mayer392 and Welcome to this forum. It doesn't seem like you're having much luck with this thread. I'm guessing the reason being that your question is not clear. Sheets are not the same as workbooks. Sure U can extract sheets from different workbooks and put them into one workbook... I have no idea what "consolidated based on headers" means? Putting a whole bunch of workbooks into 1 workbook... what's the point? Anyways, if you're still looking for help, please provide more specific information. HTH. Dave

  3. #3
    VBAX Newbie
    Joined
    Feb 2018
    Posts
    5
    Location
    Sure dave, will soon

  4. #4
    VBAX Expert
    Joined
    Apr 2005
    Posts
    678
    Location
    And a lot of people just skip a thread that says "VBA Help Needed" I think that it is safe to say that that counts for nearly 100 percent of the threads.
    If you can't be bothered to come up with a proper thread title, we can't be bothered to help.

  5. #5
    VBAX Newbie
    Joined
    Feb 2018
    Posts
    5
    Location
    Hello All,
    Firstly thanks for pointing my mistakes in creating the thread.
    I'm now attaching a zip file that has 2 workbooks WB1.xlsx and WB2.xlsx and a folder called consolidated that has consolidated excel file.

    WB1 has 3 sheets, WB2 has 2 sheets; both the workbooks have data and the first row is considered to be header, based on which the data from other sheets needs to be added up (if the header is present or created if missing)

    So when i run the code, a msg box would popup asking for location of files, once the location is chosen then it would create a folder named consolidated, consolidate the excel sheets and then place the excel sheet into consolidate folder.

    Please let me know if further information is needed.

    EDIT : How can i change the thread's title ?
    As for the name of my thread, my apologies but im really bad at naming things, deciding a subject line for an email and so on... so i'm not sure what do i change the Title to, suggestions are welcome!
    For now changing to Consolidating files in a folder based on Header
    Attached Files Attached Files
    Last edited by Mayur392; 02-18-2018 at 01:04 AM. Reason: Thread title

  6. #6
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    549
    Location
    So... 1) Select wbs to be consolidated
    2) Both have headers... where? In every sheet? If the header is not present then it is created? Based on headers what data needs to be added up?
    3) After selecting files a folder is created where? Consolidate what sheets into what workbook sheet? I'm guessing U mean collect the data from some sheet(s) in some workbook and placing the data in a sheet in some other workbook?
    4) Save the workbook in the consolidated folder. What is the wb/file name and what is the folder name and address. What if the folder exists? What if the file exists
    Are U going to be attempting to provide any code? Dave
    p.s Is VBAX planning on fixing this annoying interface? Add can't scroll thread when using quick reply to the list of annoyances. Cursor shows busy circle... grrr

  7. #7
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,386
    Location
    Quote Originally Posted by Dave View Post
    p.s Is VBAX planning on fixing this annoying interface? Add can't scroll thread when using quick reply to the list of annoyances. Cursor shows busy circle... grrr

    Post the question in the "Site & Forum Related" section under "VBAX Issues"
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    VBAX Newbie
    Joined
    Feb 2018
    Posts
    5
    Location
    1) Select wbs to be consolidated
    2) Both have headers... where? - the very first row is considered as header
    In every sheet? - Yes
    If the header is not present then it is created? - Yes
    Based on headers what data needs to be added up? Column data of respective header

    [Please refer to the attachment once may be it will provide more clarity]

    3) After selecting files a folder is created where? The folder from where the files to be consolidated are chosen
    Consolidate what sheets into what workbook sheet? All Sheets across all the workboks to one Final workbook with one sheet
    I'm guessing U mean collect the data from some sheet(s) in some workbook and placing the data in a sheet in some other workbook? - yes even this need can arise but i believe we can modify the code to choose specific sheets (if needed)
    4) Save the workbook in the consolidated folder. What is the wb/file name and what is the folder name and address. What if the folder exists? What if the file exists - i guess i have mentioned that the file would be named Consolidated* (in order to avoid duplicacy datr / time stamp can be added)
    Are U going to be attempting to provide any code? I have bits n pieces of codes that i can share but honestly im new to vba and not that skilled at it.

    That being said im not shying to learn eithr

  9. #9
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    549
    Location
    Still not clear on the header thing and adding them up. Let's start with getting all the sheets into 1 workbook. Place this code in sheet code and call/run testthat. Dave
    Option Explicit
    Private Sub testthat()
    Dim FileNm As Object, Cnt As Integer
    Dim TargetFiles As FileDialog, sht As Worksheet, Cnt2 As Integer
    'prompt user to select files
     Set TargetFiles = Application.FileDialog(msoFileDialogOpen)
     With TargetFiles
     .AllowMultiSelect = True
     .Title = "Multi-select target data files:"
     .ButtonName = ""
     .Filters.Clear
     .Filters.Add ".xlsx files", "*.xlsx"
     .Show
     End With
    If TargetFiles.SelectedItems.Count = 0 Then
     MsgBox "PICK A FILE!"
     Exit Sub
     End If
    On Error GoTo Below
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Cnt2 = ThisWorkbook.Sheets.Count
    For Cnt = 1 To TargetFiles.SelectedItems.Count
     'open the file and assign the workbook/worksheet
    Set FileNm = Workbooks.Open(TargetFiles.SelectedItems(Cnt))
    For Each sht In Workbooks(FileNm.Name).Worksheets
    sht.Copy After:=ThisWorkbook.Sheets(Cnt2)
    Cnt2 = Cnt2 + 1
    Next sht
    Workbooks(FileNm.Name).Close SaveChanges:=False
    Next Cnt
    Below:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    If Err.Number <> 0 Then MsgBox "File Error"
    End Sub

  10. #10
    VBAX Newbie
    Joined
    Feb 2018
    Posts
    5
    Location
    Ok Dave, will try and share the cores soon
    Btw can you or someone help me in telling the basics so that i can code

  11. #11
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    549
    Location
    .Filters.Add "*.xls* files", "*.xls*"
    U may want to change this line above. U have to learn by doing, googling, reading, copy and pasting, and asking questions when U can't get it to work. U have asked for a whole solution for a fairly involved outcome. It would be better to asked for solutions to smaller segments of the outcome but knowing the big picture is essential before starting. That's why U should write/map out in point form what needs to happen before U start. Dave

Tags for this Thread

Posting Permissions

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