Results 1 to 2 of 2

Append multiple table into one excel table

Threaded View

  1. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Append multiple table into one excel table

    With the macro integratie_Oeldere_revisted_vs3

    See the result in the sheet consolidated in the attached file.


    Sub integratie_Oeldere_revisted_vs3()
    
    'I got a lot of help from AB33, to get this code working; thanks for that AB33.
    
    Dim wsTest As Worksheet
    
    'check if sheet "Consolidated" already exist
    
    Const strSheetName As String = "Consolidated"
     
    Set wsTest = Nothing
    On Error Resume Next
    Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
    On Error GoTo 0
     
    If wsTest Is Nothing Then
        Worksheets.Add.Name = strSheetName
    End If
    
    With Sheets("Consolidated")
        .UsedRange.ClearContents
        .Range("A1:D1").Value = Array("sheet", "Date", "Address", "Price")
        For Each Sh In Sheets
            With Sh
                If .Name <> "Consolidated" And .Name <> "Agents" And .Name <> "RECAP" And .Name <> "PivotTable" Then
                     lr = .Cells(.Rows.Count, 1).End(xlUp).Row
                    If lr >= 4 Then
                       Rng = .Cells.Find("*", , , , xlByRows, xlPrevious).Row - 1
                       NR = Sheets("Consolidated").Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1
                       If Rng > 0 Then
    '                    Sheets("Consolidated").Cells(NR, 1).Resize(Rng) = .Name
                        Sheets("Consolidated").Cells(NR, 1).Resize(Rng, 4) = .Range("A2").Resize(Rng, 4).Value
                      End If
                   End If
                End If
            End With
        Next
        On Error Resume Next
        .Range("C2:C" & .Rows.Count).SpecialCells(4).EntireRow.Delete
    
        For Each cl In .Range("C2:C" & .Rows.Count)
        If cl = "Address" Then
        cl.Rows.EntireRow.Delete
        End If
        Next
    
        Call Sort_columnB
        .Columns("A:Z").EntireColumn.AutoFit
    End With
    End Sub
    
    
    Sub Sort_columnB()
    
        ActiveWorkbook.Worksheets("Consolidated").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Consolidated").Sort.SortFields.Add Key:=Range( _
            "B2:B5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Consolidated").Sort
            .SetRange Range("A1:D5")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy All But the First Row of a Table Databody and Append to the End of Another Table
    By TheScott in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-21-2015, 02:43 AM
  2. New column in Excel append to Access table
    By myles8908 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-29-2015, 11:32 AM
  3. Trying to Append an Excel file based on a look up table
    By dcilley@live.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-06-2013, 08:25 PM
  4. Access VBA or SQL to add all records from a table to another table (Append)
    By ewong in forum Access Programming / VBA / Macros
    Replies: 3
    Last Post: 09-14-2012, 06:49 PM
  5. Update & Append Access Table Using Excel Macro - ADO
    By erock24 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-19-2010, 04:49 AM
  6. How can i Append to Table using table Variable name
    By champs in forum Access Tables & Databases
    Replies: 1
    Last Post: 03-18-2009, 04:34 AM
  7. How do I Append excel data to a Access database table in a excel macro?
    By Pete in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2005, 06:20 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1