Results 1 to 6 of 6

find columns with same headers in multiple worksheet and coping column in new sheet

Threaded View

  1. #1
    Registered User
    Join Date
    08-17-2012
    Location
    Serbia
    MS-Off Ver
    Excel 2003
    Posts
    24

    find columns with same headers in multiple worksheet and coping column in new sheet

    i have workbook, where i need a macro that he finds columns with same headers, and he copies data new sheet. colomns in different order in sheets. this is my code, he works with the sheets that that have same order of columns

    Sub Merge_Sheets()
    
      Dim startRow, startCol, lastRow, lastCol As Long
    Dim headers As Range
    Dim ws As Worksheet
    Dim pas As Worksheet
    
    'Set Master sheet for consolidation
    Set wb = ActiveWorkbook
    Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = "AllSheets"
    Set mtr = Worksheets("AllSheets")
    Sheets("Sheet1").Activate
    'Get Headers
    Set headers = Application.InputBox("Izaberi opseg Header-a", Type:=8)
    
    'Copy Headers into master
    headers.Copy mtr.Range("A1")
    startRow = headers.Row + 1
    startCol = headers.Column
    
    Debug.Print startRow, startCol
    'loop through all sheets
    For Each ws In wb.Worksheets
         'except the master sheet from looping
         If ws.Name <> "AllSheets" Then
            ws.Activate
            lastRow = Cells(Rows.Count, startCol).End(xlUp).Row
            lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column
            'get data from each worksheet and copy it into AllSheets sheet
            Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
            mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
               End If
    Next ws
    
    Sheets("AllSheets").Activate
    End Sub
    my macro is in private workbook
    Attached Files Attached Files
    Last edited by gogi100; 05-16-2022 at 03:11 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Find and Replace Multiple Columns in Sheet 1 Based on a Column Values in Sheet 2
    By adblog3 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-07-2020, 01:49 PM
  2. [SOLVED] Extract multiple row headers and column headers if criteria is met in multiple columns
    By PaulM100 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-20-2019, 04:56 AM
  3. [SOLVED] Add column headers to newly created worksheet and add formatting to all columns
    By moosetales in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-18-2016, 07:21 AM
  4. Copy/Paste Columns from Multiple Worksheets based on Headers into new Worksheet
    By casper3043 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-01-2015, 03:06 PM
  5. Replies: 2
    Last Post: 06-14-2013, 10:01 AM
  6. Search for value in multiple columns, Return Column Headers
    By Zach51215 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-30-2013, 06:49 AM
  7. Help with Converting Multiple Rows to Columns and add Column Headers
    By Lmsloman in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-27-2010, 10:45 AM

Tags for this Thread

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