Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 19

Thread: Macro to Pull Specific Data Based on tomorrow's date.

  1. #1
    Join Date
    2nd June 2017
    Posts
    12

    Macro to Pull Specific Data Based on tomorrow's date.

    I'm trying to create a macro that will search for specific data based on a day ahead date and drop it into a different tab in excel in a specific order. I've attached an example of the spreadsheet which will help in the explanation of what I'm trying to do.

    The macro looks in the Data tab and looks at day ahead information (ie: if today is June 1, I need it to look at June 2....its always day ahead information) and drop the information in the tab labeled Up. But not all information from the Data tab is needed. Only specific data which is labeled in the Up tab. The sheet has a column labelled Buy/Sell and needs to only pull the sell data for June 2 and drop it in the Up tab in a specific order.

    I've tried to manually build a macro but get tripped up on pulling specific data based on date and specific cell data.

    Hope that makes sense and any help would be much appreciated.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2011
    Location
    Steamboat Springs
    Posts
    755

    Re: Macro to Pull Specific Data Based on tomorrow's date.

    Give this a try. I wasn't sure what your static information was all about. I ignored it for the purposes of this exercise as you did not explain that as part of the issue.

    VB:
    Option Explicit 
    Sub SellPlusOne() 
        Dim s1 As Worksheet, s2 As Worksheet 
        Set s1 = Sheets("Data") 
        Set s2 = Sheets("Up") 
        Dim lr As Long, i As Long, lrU As Long 
        lr = s1.Range("E" & Rows.Count).End(xlUp).Row 
        For i = 2 To lr 
            lrU = s2.Range("D" & Rows.Count).End(xlUp).Row 
            If s1.Range("E" & i) = Date + 1 Then 
                If s1.Range("H" & i) = "Sell" Then 
                    s1.Application.Union(Range("A" & i & ":C" & i), Range("G" & i), Range("I" & i)).Copy 
                    s2.Range("D" & lrU + 1).PasteSpecial xlPasteAll 
                End If 
            End If 
        Next i 
        Application.CutCopyMode = False 
         
         
    End Sub 
    
    
    Alan

    Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
    FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

    If someone has helped you, say "thank you" by clicking on their reputation button (*) below their name.

  3. #3
    Join Date
    2nd June 2017
    Posts
    12

    Re: Macro to Pull Specific Data Based on tomorrow's date.

    Thanks Alan Sidman for you timely response.

    It works.
    Couple other things I should have mentioned:

    - How can I get the volume (number) to be transposed (remove the negative)?
    - On the Up tab how do I add the word Pool after the cp code?



    Thanks
    Last edited by palchy; 3 Weeks Ago at 01:09.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    26th January 2011
    Location
    Steamboat Springs
    Posts
    755

    Re: Macro to Pull Specific Data Based on tomorrow's date.

    Here is some modified code

    VB:
    Option Explicit 
     
     
    Sub SellPlusOne() 
        Dim s1 As Worksheet, s2 As Worksheet 
        Set s1 = Sheets("Data") 
        Set s2 = Sheets("Up") 
        Dim lr As Long, i As Long, lrU As Long 
        lr = s1.Range("E" & Rows.Count).End(xlUp).Row 
        For i = 2 To lr 
            lrU = s2.Range("D" & Rows.Count).End(xlUp).Row 
            If s1.Range("E" & i) = Date + 1 Then 
                If s1.Range("H" & i) = "Sell" Then 
                    s1.Application.Union(Range("A" & i & ":C" & i), Range("G" & i), Range("I" & i)).Copy 
                    s2.Range("D" & lrU + 1).PasteSpecial xlPasteAll 
                    s2.Range("G" & lrU + 1) = s2.Range("G" & lrU + 1) * -1 
                    s2.Range("F" & lrU + 1) = s2.Range("G" & lrU + 1) & " + Pool" 
                End If 
            End If 
        Next i 
        Application.CutCopyMode = False 
         
         
    End Sub 
    
    
    Alan

    Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
    FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

    If someone has helped you, say "thank you" by clicking on their reputation button (*) below their name.

  5. #5
    Join Date
    2nd June 2017
    Posts
    12

    Re: Macro to Pull Specific Data Based on tomorrow's date.

    It worked perfectly.

    One last thing:

    re: s1.Application.Union(Range("A" & i & ":C" & i), Range("G" & i), Range("I" & i)).Copy

    How would I alter the code if I wanted to remove
    (Range("A" & i & ":C" & i) and copy and paste C & i twice in the up tab. So under the CP and CP location? So basically copy a cell in one tab and paste it in two different cells of another tab. Its probably no longer an application.union but I'm getting all sorts of issues when trying to alter it.


    Thanks

    Last edited by palchy; 3 Weeks Ago at 05:04.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    26th January 2011
    Location
    Steamboat Springs
    Posts
    755

    Re: Macro to Pull Specific Data Based on tomorrow's date.

    I'm not understanding what you want. Please explain in simple English. Don't try to use the current VBA. Just explain what it is you wish to do with an example.
    Alan

    Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
    FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

    If someone has helped you, say "thank you" by clicking on their reputation button (*) below their name.

  7. #7
    Join Date
    2nd June 2017
    Posts
    12

    Re: Macro to Pull Specific Data Based on tomorrow's date.

    Quote Originally Posted by AlanSidman View Post
    I'm not understanding what you want. Please explain in simple English. Don't try to use the current VBA. Just explain what it is you wish to do with an example.
    Sorry about that. What I wanted to see was a slight variation to what you have already helped me with.

    it just had to do with this line of code:
    s1.Application.Union(Range("A" & i & ":C" & i), Range("G" & i), Range("I" & i)).Copy

    Instead of copying the first three columns from the data tab I wanted to copy only column C (CP code) and paste it into the up tab in column D (CP) and column E (CP Location).

    Everyting else would stay the same.

    Thanks

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    26th January 2011
    Location
    Steamboat Springs
    Posts
    755

    Re: Macro to Pull Specific Data Based on tomorrow's date.

    Try this:

    VB:
    s1.range("C" & i).copy s2.Range("D" & lrU +1) 
    s1.range("C" & i).copy s2.range("E" & lrU + 1) 
    
    
    Make sure to delete the next line in the original code which contains the pastespecial command as it will be replaced with the above lines.
    Alan

    Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
    FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

    If someone has helped you, say "thank you" by clicking on their reputation button (*) below their name.

  9. #9
    Join Date
    2nd June 2017
    Posts
    12

    Re: Macro to Pull Specific Data Based on tomorrow's date.

    Quote Originally Posted by AlanSidman View Post
    Try this:

    VB:
    s1.range("C" & i).copy s2.Range("D" & lrU +1) 
    s1.range("C" & i).copy s2.range("E" & lrU + 1) 
    
    
    Make sure to delete the next line in the original code which contains the pastespecial command as it will be replaced with the above lines.
    Almost there:

    Here is the VB code I am running and I am getting a "Type Mismatch" error after it copies one row in the Up tab. Also, how can I get the information to copy values. Some of the cells contain formula's that are getting copied over.

    VB:
    Sub SellPlusOne() 
        Dim s1 As Worksheet, s2 As Worksheet 
        Set s1 = Sheets("Deals") 
        Set s2 = Sheets("Upload") 
        Dim lr As Long, i As Long, lrU As Long 
        lr = s1.Range("E" & Rows.Count).End(xlUp).Row 
        For i = 2 To lr 
            lrU = s2.Range("D" & Rows.Count).End(xlUp).Row 
            If s1.Range("E" & i) = Date + 1 Then 
                If s1.Range("H" & i) = "Sell" Then 
                    s1.Range("C" & i).Copy s2.Range("D" & lrU + 1) 
                    s1.Range("C" & i).Copy s2.Range("E" & lrU + 1) 
                    s1.Range("C" & i).Copy s2.Range("F" & lrU + 1) 
                    s1.Range("G" & i).Copy s2.Range("G" & lrU + 1) 
                    s1.Range("K" & i).Copy s2.Range("H" & lrU + 1) 
                    s2.Range("G" & lrU + 1) = s2.Range("G" & lrU + 1) * -1 
                    s2.Range("F" & lrU + 1) = s2.Range("F" & lrU + 1) & " Pool" 
                End If 
            End If 
        Next i 
        Application.CutCopyMode = False 
         
         
    End Sub 
    
    
    MODERATOR COMMENT: I have added code tags for you. Please use them in the future.
    Thanks
    Last edited by AlanSidman; 2 Weeks Ago at 04:04. Reason: code tags added

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    26th January 2011
    Location
    Steamboat Springs
    Posts
    755

    Re: Macro to Pull Specific Data Based on tomorrow's date.

    I've tested your code with the sheet you supplied in the first thread and I am unable to duplicate your error. You may wish to upload your updated spreadsheet for analyzation as the code works for me.
    Alan

    Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
    FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

    If someone has helped you, say "thank you" by clicking on their reputation button (*) below their name.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Macro (or formula) to Pull Data on Specific Date
    By dlewis0610 in forum Excel General
    Replies: 3
    Last Post: August 28th, 2014, 23:48
  2. Replies: 1
    Last Post: March 16th, 2012, 09:50
  3. Replies: 1
    Last Post: December 22nd, 2011, 09:58
  4. Replies: 3
    Last Post: August 2nd, 2006, 10:24
  5. Revise code to pull data based on date
    By tough_guy in forum Excel General
    Replies: 9
    Last Post: March 1st, 2006, 12:16

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
  •  
porno