+ Reply to Thread
Results 1 to 8 of 8

Formula to pull only cells with data from one sheet to a consolidated sheet

  1. #1
    Registered User
    Join Date
    03-07-2017
    Location
    Ontario
    MS-Off Ver
    2010
    Posts
    21

    Formula to pull only cells with data from one sheet to a consolidated sheet

    I am trying to pull information from a sheet in which I paste in downloaded info from my website. Since the creators of that site can't give me what I need I am attempting to set up my own worksheet. I need only the cells on Sheet 1 in columns F, G and H that have a formula result to pull to pull to sheet 7 without the blank lines between. Ideally it would be nice if it also sorted that date by the earliest time ordered.

    I would also like to say that everything I know about excel has been learned from the site. I consider myself intermediate, not to knowledgeable with VBA but I can create macros that are simple. So thanks for all you help
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    7,268

    Cool Try this ‼


    A demonstration as a beginner starter according to the attachment only with Sheet1's columns A to C
    (formulas columns are useless !), paste this code to the Sheet7 worksheet module :

    PHP Code: 
    Sub Demo()
        
    Dim L&, Rg As Range
            L 
    Cells(Rows.Count1).End(xlUp).Row
            
    If 2 Then Rows("3:" L).Clear
            L 
    2
            Application
    .ScreenUpdating False
            Set Rg 
    Sheet1.Cells(Rows.Count2).End(xlUp)
        While 
    IsNumeric(Rg.Value2)
            
    1
            Cells
    (L1).Resize(, 3).Value = Array(Mid(Rg(00).Value221), Rg.Value2Rg(12).Value2)
            
    Set Rg Rg.End(xlUp)
        
    Wend
            Set Rg 
    Nothing
            Application
    .ScreenUpdating True
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon Add Reputation !

  3. #3
    Registered User
    Join Date
    03-07-2017
    Location
    Ontario
    MS-Off Ver
    2010
    Posts
    21

    Re: Try this ‼

    It seemed to work in the sample book but when I use a full set of data which is 500+ lines it only grabs the very last line of information. What am I missing?

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    7,268

    Arrow


    It not seems but it works with your attachment !
    You just forgot to attach a workbook reflecting the real one

  5. #5
    Registered User
    Join Date
    03-07-2017
    Location
    Ontario
    MS-Off Ver
    2010
    Posts
    21

    Re: Formula to pull only cells with data from one sheet to a consolidated sheet

    Here is a full file. Thanks so much for your help. I am trying my best to understand what you wrote. Is there someway or somewhere I can figure that out? I don't simply want someone else to do the work for me, I would like to learn from it
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    7,268

    Lightbulb Try this !


    As pretty all is yet in the VBA inner help, just open it !

    A new demonstration as a beginner starter according to the last attachment only with Sheet1's columns A to C
    (formulas columns are again useless !), paste this code to the Sheet2 worksheet module :

    PHP Code: 
    Sub Demo1()
           
    Dim L&, Rg As RangeR&
               
    1
               Me
    .UsedRange.Offset(1).Clear
        With Sheet1
    .UsedRange.Columns(1)
               
    Set Rg = .Find("Date: *", , , xlWhole)
            If 
    Not Rg Is Nothing Then
               Application
    .ScreenUpdating False
               R 
    Rg.Row
                 
    Do
                    
    1
                    Cells
    (L1).Resize(, 3).Value = Array(Mid(Rg(2).Value221), Rg(02).Value2Rg(03).Value2)
                    
    Set Rg = .FindNext(Rg)
                 
    Loop Until Rg.Row R
               Set Rg 
    Nothing
               Application
    .ScreenUpdating True
            End 
    If
        
    End With
    End Sub 
    ► Do you like it ? ► ► So thanks to click on bottom left star icon Add Reputation !

  7. #7
    Registered User
    Join Date
    03-07-2017
    Location
    Ontario
    MS-Off Ver
    2010
    Posts
    21

    Re: Formula to pull only cells with data from one sheet to a consolidated sheet

    Genius, thank you so soo much.

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    7,268

    Re: Formula to pull only cells with data from one sheet to a consolidated sheet


    Thanks for the rep' !

+ 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] Consolidated Summary Sheet - Copy data from one sheet to other based on criteria
    By sabha in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-21-2015, 03:29 AM
  2. VBA code to pull range of data from multiple cells into a consolidated file
    By iblair in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 07-10-2015, 02:11 AM
  3. Formula to pull data from one sheet to a new sheet based on date range
    By lisajolley11 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-15-2015, 08:38 PM
  4. [SOLVED] Formula to pull data from one sheet and column for another sheet.
    By Dena in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-05-2013, 06:20 PM
  5. [SOLVED] Create consolidated dynamic sheet from several sheets (pull data)
    By G.Bregvadze in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-23-2013, 03:27 PM
  6. Replies: 5
    Last Post: 11-28-2012, 10:51 AM
  7. Replies: 5
    Last Post: 07-26-2012, 09:51 AM

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