Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 2 of 2 FirstFirst 1 2
Results 11 to 19 of 19

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

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

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

    Quote Originally Posted by palchy View Post
    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

    I agree, I was able to get the original sheet to work. In the code I sent how would I get the cells to paste values and not formulas. I've tried entering code to make this work but I'm getting nowhere. Would that have something to do with error I'm getting?

    Thanks

    Excel Video Tutorials / Excel Dashboards Reports


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

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

    No. We have to change the paste option to paste special. This needs to be done on two lines. Amend your code to reflect the following:

    Option Explicit


    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).PasteSpecial xlPasteValues, , , False 
                    s1.Range("C" & i).Copy 
                    s2.Range("E" & lrU + 1).PasteSpecial xlPasteValues, , , False 
                    s1.Range("C" & i).Copy 
                    s2.Range("F" & lrU + 1).PasteSpecial xlPasteValues, , , False 
                    s1.Range("G" & i).Copy 
                    s2.Range("G" & lrU + 1).PasteSpecial xlPasteValues, , , False 
                    s1.Range("K" & i).Copy 
                    s2.Range("H" & lrU + 1).PasteSpecial xlPasteValues, , , False 
                    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 
    
    
    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. #13
    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
    No. We have to change the paste option to paste special. This needs to be done on two lines. Amend your code to reflect the following:

    Option Explicit


    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).PasteSpecial xlPasteValues, , , False 
                    s1.Range("C" & i).Copy 
                    s2.Range("E" & lrU + 1).PasteSpecial xlPasteValues, , , False 
                    s1.Range("C" & i).Copy 
                    s2.Range("F" & lrU + 1).PasteSpecial xlPasteValues, , , False 
                    s1.Range("G" & i).Copy 
                    s2.Range("G" & lrU + 1).PasteSpecial xlPasteValues, , , False 
                    s1.Range("K" & i).Copy 
                    s2.Range("H" & lrU + 1).PasteSpecial xlPasteValues, , , False 
                    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 
    
    

    Ahhh, I didn't realize it had to go on two separate lines. This worked perfectly.

    Thanks Alan for all your help, you make it look easy.

    Excel Video Tutorials / Excel Dashboards Reports


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

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

    You are welcome. Thanks for the Rep.
    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. #15
    Join Date
    2nd June 2017
    Posts
    12

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

    Hey Alan,

    Sorry to bother you......

    Is there a way when the macro is running if it finds a match in Column C it would add the volume in Column G together. The same "Sell" criteria is still the same.

    Thanks
    Last edited by palchy; June 13th, 2017 at 02:19.

    Excel Video Tutorials / Excel Dashboards Reports


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

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

    I am not understanding what this means. Can you clarify with an example?.

    Is there a way when the macro is running if it finds a match in Column C
    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. #17
    Join Date
    2nd June 2017
    Posts
    12

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

    [ATTACH]72725

    It's an add on from the macro you have already helped me with. Everything would stay the same except I would like to eliminate duplicate entries.

    In regards to column C (CP Code) in the data tab. When there is a same name it would then add column G (volume) volumes together to then create only one line in the UP tab.

    Example in the attachment:

    As before its looking for day ahead information and only Sell's. Everything you have already helped me with and created.

    But now:

    It would see in Column C a match with the name ABC and then add the volumes form Column G. So in this case the Sell volume total would be 200.

    I hope that makes sense.

    Also, this macro you have been helping me with can be utilized in ten of my spreadsheets. I have another twenty spreadsheets that would require a new macro. Would I need to start a new thread or just continue with this one.

    Thanks
    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


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

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

    Will work on this issue. You should start a new thread for each new issue. In this manner, you will have more people looking at it. When a thread gets long, it is rarely looked at by anyone other than the OP and the responder. You want to have as many participants look at your issue as possible as not all participants are active all the time and there are 24 times zones.
    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. #19
    Join Date
    26th January 2011
    Location
    Steamboat Springs
    Posts
    807

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

    A quick idea. Once you have the data moved to UP as we did earlier. Sort them by the CP and then use the sub totals function to aggregate the sell amounts. This would be faster and more efficient in my mind. No VBA required, however, these few steps could be automated. You could try to write the code for this by doing a Macro recording and see if that works. If you need help amending the code, then post the recorded code in a new thread and ask for assistance.
    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