+ Reply to Thread
Results 1 to 3 of 3

Consolidating Worksheets issue

Hybrid View

  1. #1
    Registered User
    Join Date
    05-14-2019
    Location
    Provo, Utah
    MS-Off Ver
    2013
    Posts
    26

    Consolidating Worksheets issue

    Im having two issues

    Issue 1) at the top of my "IMPORT" sheet its duplicating the header name in A1 so that it is appearing twice once in cell A1 where it should be and in A2.
    Issue 2) I want to change this to actually pastes values into the "Import"

    Here is my code
    Sub Merge_Sheets()
    
    Dim startRow, startCol, lastRow, lastCol As Long
    Dim headers As Range
    
    'Set Master sheet for consolidation
    Set mtr = Worksheets("IMPORT")
    
    Set wb = ThisWorkbook
    'Get Headers
    Set headers = Worksheets("ImportM1").Range("A1:K1")
    
    'Copy Headers into master
    headers.Copy mtr.Range("A1")
    startRow = headers.Row + 1
    startCol = headers.Column
    
    'loop through all sheets
    For Each ws In wb.Worksheets
         'except the master sheet from looping
         If ws.Name <> "Import" And ws.Name <> "Paste Here" And ws.Name <> "M3" And ws.Name <> "M2" And ws.Name <> "M1" And ws.Name <> "Vlookups" And ws.Name <> "Validation" And ws.Name <> "Fail" 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 Master 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
    
    Worksheets("IMPORT").Activate
    
    End Sub

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Consolidating Worksheets issue

    Quote Originally Posted by Glervey View Post
    Issue 1) at the top of my "IMPORT" sheet its duplicating the header name in A1 so that it is appearing twice once in cell A1 where it should be and in A2.
    more than likely that's because the code needs to be modified. see image 1 below for where the issue is at, and image 2 below for what results from it:
    Attached Images Attached Images

  3. #3
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,038

    Re: Consolidating Worksheets issue

    Not sure what your setup is as no sample file...
    Add exclusion sheets if needed...
    Sub sintekJ3v16()
    Dim ws As Worksheet
    Sheets("ImportM1").Range("A1:K1").Copy Sheets("Import").Range("A1")
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "Import" And ws.Name <> "ImportM1" Then
            With ws.UsedRange
                .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0).Copy Sheets("Import").Range("A" & Rows.Count).End(xlUp)(2)
            End With
        End If
    Next ws
    End Sub
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Issue while consolidating multiple excel sheet
    By jjacob84 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-31-2020, 01:29 PM
  2. Consolidating Worksheets with VBA
    By jos283 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-14-2020, 11:50 AM
  3. Consolidating worksheets
    By Tendekai in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-12-2018, 12:43 PM
  4. Consolidating data from Multiple Workbooks to One Workbook issue
    By jeffvnz in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 10-23-2012, 07:13 PM
  5. Consolidating worksheets and then referencing the worksheets themselves in a new column
    By zed commander in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-09-2012, 05:14 AM
  6. Consolidating worksheets
    By Cand in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-09-2009, 05:43 PM
  7. Consolidating worksheets
    By Joeflo in forum Excel General
    Replies: 1
    Last Post: 04-22-2006, 06:47 PM

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