+ Reply to Thread
Results 1 to 4 of 4

Code help to pull data into Combined tab

  1. #1
    Forum Contributor
    Join Date
    02-24-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 2010
    Posts
    164

    Code help to pull data into Combined tab

    I could use some help. I am looking for code to pull data from the 3rd tab, which is labeled "01" to the last tab, usually "31" and pull all of the data from cells A2 to the last row of data in my "Combined" tab, which is the 2nd tab. Each month I have new tab that get replaced for tabs 01 thru 31 with the new month, however the combined tab is always there. The combined tab has a header set up already and each new set of data from each tab needs to go to the last row. Does that make sense.

    I tried looking for code or a macro only, but nothing works correctly. Any help would be greatly appreciated. I figure out code out how to split data from a master tab to different tabs, but not how to pull data in.

    Thanks,
    Dan

  2. #2
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Code help to pull data into Combined tab

    If I understand, in a Module:
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    02-24-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Office 2010
    Posts
    164

    Re: Code help to pull data into Combined tab

    I don't believe the code above will do what I'm looking for, because the tab called combined is the only static tab. The tabs 01 thru 31 get replaced every month or time I have rerun it.

    I did find this code online that pulls in all other tabs into combined and it works good, except it also pulls in tab 1 data, which I'm trying to avoid. Beside "Combined" the is "Sheet1", which I don't want to pull in. How do I change the code below to exclude both of these tab, or at least exclude the tab called "Sheet1"? Also, how put the code into a window properly?

    [Code]

    Sub Sample()
    Dim wsInput As Worksheet, wsOutput As Worksheet
    Dim rng As Range
    Dim LRowO As Long, LRowI As Long

    On Error GoTo Whoa

    '~~> Set your Output Sheet
    Set wsOutput = ThisWorkbook.Sheets("Combined")

    '~~> Loop through all sheets
    For Each wsInput In ThisWorkbook.Worksheets
    '~~> Ensure that we ignore the output sheet
    If wsInput.Name <> wsOutput.Name Then
    '~~> Working with the input sheet
    With wsInput
    '~~> Get the last row of input sheet
    LRowI = .Range("A" & .Rows.Count).End(xlUp).Row
    '~~> Set your range for copying
    Set rng = .Range("A2:K" & LRowI)
    '~~> Copy your range
    rng.Copy
    '~~> Pasting data in the output sheet
    With wsOutput
    '~~> Get the next available row in output sheet for pasting
    LRowO = .Range("A" & .Rows.Count).End(xlUp).Row + 1

    '~~> Finally paste
    .Range("A" & LRowO).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
    End With
    End If
    Next wsInput

    Exit Sub
    Whoa:
    MsgBox Err.Description
    End Sub
    Last edited by Delta729; 11-12-2016 at 08:50 PM.

  4. #4
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Code help to pull data into Combined tab

    Insert code tags by clicking the # icon on the toolbar and pasted between them. OR, type (code)(/code) but replace ()'s with []'s.

    I was confused by your use of 01. I thought you meant sheet's named 01 to 31 for days of the month.

    Here is how to skip the sheet's named Combined and Sheet1.
    Please Login or Register  to view this content.

+ 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. [SOLVED] VBA Code to pull data from cell that has data validation
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-16-2016, 11:02 PM
  2. Replies: 0
    Last Post: 07-08-2015, 06:31 AM
  3. help on vba code to pull data with conditions ,filename,avoid header,unfiltered data
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-27-2013, 05:57 AM
  4. VBA code to pull data between workbooks
    By codesRus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-20-2012, 06:51 PM
  5. Code to Pull Data from a Different File
    By HRBonne in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-26-2010, 08:40 PM
  6. Easiest way to pull value using Date & Time combined
    By mystic342005 in forum Excel General
    Replies: 4
    Last Post: 07-03-2007, 10:28 PM
  7. Code to pull data from webpage
    By imimin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-22-2006, 02:06 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