Posts by TinoBradica

    I will try in a bit.
    Right now I am opening one file at a time and using ASAP Utilituies to remove all empty rows one sheet at a time. Taking forever.

    I did post another thread asking for some sort of a VBA to remove all Empties in an entire workbook. Waiting for response.


    with your latest code I get this message:

    runtime error '-2147021892(80070bbc)':
    Office has detected a problem with this file. To help protect your computer this file cannot be opened.

    When I hit debug,.it goes to this line:

    Workbooks.Open directory & Application.PathSeparator & file.Name

    This is so strange.
    I leave the one file in the test folder and it works with no problem. All sheets copy over.
    When I add another file to the test folder, it merges all the workbooks and sheets except for 1 sheet data.

    That same wrk book and sheet load when alone in folder.

    never seen anything like this.

    Any Ideas????

    Ok, sorry for the delay. Was testing a strange result.
    I took 7 files to combine.

    it seems to be skipping data from one file and 1 of the 8 sheets. Very Strange.
    All other information for that file is being combined. It is skipping step 4.

    I checked the file and there is data in that sheet.

    The VBA does seem to work other than that,.

    I was able to get a partial result. It is only merging the first tab in all the workbooks. It does not move to next tab and combine. The result of tab1 is what I am looking for.
    Another NOTE: the data range is different per sheet.

    Sheet1= A2:AP

    and vary in ROWS from 2 to 1000 row entries.

    Let me explain a bit of my project if it helps. I am a web designer doing a massive 100k item data migration. I have a master excel sheet template for uploading items to my web site. That file consists of 8 tabs labeld 1 - 8 9 Step 1 Step 2 ... etc.

    Step 1 would be categories
    Step 2 would be Sub Cats
    Step3 Items to upload
    Step 4 Images
    ... ETC...
    I have 500 individual XLS files Templates with dif cat and sub cats and items
    I need to bring it all together to make a MASTER UPLOAD xls with all Cats listed in order in tab called Step 1 - All Items Listed together in Step 3 and so on.

    Hello, New here. Hope Im in the right place.
    I found a thread that seems to be same problem I am having. I tried using the code provided to other member and am NOT getting results. Here is what I need.
    I have 500 workbooks, all with same header and sheet names.
    I need to combine the content all into a single workbook with all sheets merged into workbook. data all starts at A2 and varies in row content from 1 row to 5000 per workbook/sheet.
    The code is what I found on this site and tried to modify to work before I asked the question.

    Option Explicit

    Sub Extract_Files()

    Application.ScreenUpdating = False

    Dim ParentFolder As String
    Dim WS As Worksheet
    Dim ThisWB As Workbook
    Dim ThisWS As Worksheet
    Dim OtherWB As Workbook
    Dim WScount As Integer
    Dim File As Variant

    Set ThisWB = ActiveWorkbook
    Set ThisWS = ActiveSheet

    ParentFolder = "E:\TestMergeVAI\test" 'Change as required

    If Right(ParentFolder, 1) <> "" Then ParentFolder = ParentFolder & ""

    File = Dir(ParentFolder)

    WScount = 0

    While File <> ""

    Set OtherWB = Workbooks.Open(ParentFolder & File)

    For Each WS In OtherWB.Sheets

    If Left(UCase(WS.Name), 4) = "Step" Then
    WScount = WScount + 1

    If WScount = 1 Then WS.Rows(1).EntireRow.Copy Destination:=ThisWS.Rows(1).EntireRow

    WS.Range("A2:N" & Range("A" & WS.Rows.Count).End(xlUp).Row).Copy
    ThisWS.Range("A" & ThisWS.Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False

    End If

    Next WS

    OtherWB.Close False

    Set OtherWB = Nothing

    File = Dir



    Set ThisWB = Nothing
    Set ThisWS = Nothing

    Application.ScreenUpdating = True

    MsgBox WScount & " worksheets transferred successfully.", vbInformation, "Done"

    End Sub

    When I run the code. I get a window that says 0 book combined

    Hope you all can help