Select start date and end date from a column of dates and transpose to another sheet.
Select start date and end date from a column of dates and transpose to another sheet.
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Select start date and end date from a column of dates and transpose to another sheet.

  1. #1
    New Member
    Join Date
    Jan 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Select start date and end date from a column of dates and transpose to another sheet.

     
    Hello, I am Frank and I am trying to learn VBA by experimenting with macros and reading other code from other users. I am working with excel 2016. I have been racking my pea brain on this and I know it is something simple. Below is a macro that was working but not allowing me to select a start and end date from dates on spreadsheet. I have drop down boxes for start and end dates on spreadsheet.

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    '

    Range("D3:D5").Select "(This is where I want to be able to select my start and end dates) D3-startdate and D5-enddate"
    Selection.Copy
    Sheets("Reading").Select
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True
    Application.CutCopyMode = False


    End Sub

    I hope this explains what I am trying to do. Thank you in advance

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,087
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select start date and end date from a column of dates and transpose to another sheet.

    This will copy the dates from only cells D3 and D5 of the ActiveSheet and paste them into cells B1:C1 of sheet Reading.

    Code:
    Sub t()
    Dim rng As Range
    With ActiveSheet
        Set rng = Union(.Range("D3"), .Range("D5"))
        rng.Copy
        Sheets("Reading").Range("B1").PasteSpecial xlPasteValues, Transpose:=True
    End With
    Application.CutCopyMode = False
    End Sub
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    New Member
    Join Date
    Jan 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select start date and end date from a column of dates and transpose to another sheet.

    Thank you for the quick response. I want to be able to select a specific range of cells from a start date drop down box and end date drop down box. Below is a snapshot of my Sheets("Dates"). I have established two named ranges for my drop down boxes and they are "startdatemon" and "enddatemon". Those dates in red are the ones transposed to my Sheets("Reading") in cell b1. Thanks for your awesome mind.


    Mondays Mondays
    For the Year Start Date End Date 2/6/2017 2/27/2017
    1/2/2017
    1/9/2017
    1/16/2017
    1/23/2017
    1/30/2017
    2/6/2017
    2/13/2017
    2/20/2017
    2/27/2017
    3/6/2017
    3/13/2017
    3/20/2017
    3/27/2017
    4/3/2017
    4/10/2017
    4/17/2017
    4/24/2017

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,087
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select start date and end date from a column of dates and transpose to another sheet.

      
    Although I am still unsure that I understand the set up, I will make one more attempt. This assumes that your dates are in column D. If not, change the D:D search range in the find statements to the correct column.

    Code:
    Sub t()
    Dim r1 As Range, r2 As Range
    Set r1 = Sheets("Dates").Range("D:D").Find(Range("startdatemon").Value)
    Set r2 = Sheets("Dates").Range("D:D").Find(Range("enddatemon").Value)
        If Not r1 Is Nothing And Not r2 Is Nothing Then
            Sheets("Dates").Range(r1, r2).Copy
            Sheets("Reading").Range("B1").PasteSpecial xlPasteValues, Transpose:=True
        End If
    Application.CutCopyMode = False
    End Sub
    Last edited by JLGWhiz; Nov 7th, 2017 at 11:02 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com