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

late1day

New Member
Joined
Jan 23, 2016
Messages
7
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top