Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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.

    Code:
    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 the Like Button.

    Comment


    • #3
      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; June 3rd, 2017, 01:09.

      Comment


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

        Here is some modified code

        Code:
        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 the Like Button.

        Comment


        • #5
          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; June 3rd, 2017, 05:04.

          Comment


          • #6
            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 the Like Button.

            Comment


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

              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

              Comment


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

                Try this:

                Code:
                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 the Like Button.

                Comment


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

                  Originally posted by AlanSidman View Post
                  Try this:

                  Code:
                  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.

                  Code:
                  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; June 6th, 2017, 04:04. Reason: code tags added

                  Comment


                  • #10
                    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 the Like Button.

                    Comment


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

                      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.

                      Code:
                      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

                      Comment


                      • #12
                        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


                        Code:
                        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 the Like Button.

                        Comment


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

                          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


                          Code:
                          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.

                          Comment


                          • #14
                            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 the Like Button.

                            Comment


                            • #15
                              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, 02:19.

                              Comment

                              Trending

                              Collapse

                              There are no results that meet this criteria.

                              Working...
                              X