Results 1 to 6 of 6

Loop through tabs in a workbook to consolidate data

Threaded View

  1. #1
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    O365
    Posts
    311

    Loop through tabs in a workbook to consolidate data

    Hello,

    I was hoping someone could help me out? I put together the attached file and code to consolidate information. The macro is running through the tabs and pasting them to the tab I specify but it is not stacking the data to consolidate all of the information to one tab. Instead it copies each tab and pastes completely over the data each time. I want it to copy sheet1 and paste it to sheet 30. Then copy sheet2 and paste it to the bottom of sheet 30 below the area it just pasted for sheet1. Any help is much appreciated.

    Sub loopTotal()
    Dim wk As Worksheet
    Dim wks As Worksheet
    Dim nDatarows As Long
    Dim nLastRow As Long
        For Each wk In ThisWorkbook.Worksheets
            wk.Activate
            Set wks = ThisWorkbook.Sheets("Sheet30")
            wks.Range("A2", "BC65536").Clear
            Range("A1", "Y65000").Copy
            nDatarows = wks.Range("A65536").End(xlUp).Row + 1
            wks.Range("A" & nDatarows).PasteSpecial xlPasteAll
            nLastRow = wks.Range("A65536").End(xlUp).Row
            For J = nDatarows To nLastRow
                wks.Range("AB" & J) = strFileName
            Next J
            Application.CutCopyMode = False
        Next wk
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Extract data from a workbook with multiple tabs, into a workbook with corresponding tabs
    By krackaberr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-28-2013, 11:54 AM
  2. Consolidate text data from multiple tabs into one table
    By neversummer515 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 02-01-2013, 05:52 PM
  3. Replies: 12
    Last Post: 09-20-2011, 08:52 PM
  4. VB Code to Consolidate data from different files / tabs
    By tt388 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-29-2011, 11:53 AM
  5. Append Data From One Workbook to Another Workbook (Consolidate Random Columns)
    By capnhud in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-23-2010, 01:15 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