Makro for finding dates and copy/paste content from below

rokandil

New Member
Joined
Jul 8, 2019
Messages
2
Hi I have this code which basically finds the date I need from a table and copies a certain number of cells below and pastes it somewhere else. My question is how I could amend it so that after the first round a question box appears which asks if more dates are needed. If yes again it should be possible to type in the date and number of cells but this time the content will be automatically placed one column right from the previous one.. so in the end I get a listing of selected data. Would be happy if you got a solution...

Thats the code so far:


Public Sub Date__Suchen()
Dim rngFind As Range
Dim strDate As String
Dim a As Long, b As Long

strDate = InputBox("Date:", , CDate(Date))
If strDate = "" Then Exit Sub

a = Sheets("Spreads").Cells(58, Columns.Count).End(xlToLeft).Column

Set rngFind = Sheets("Spreads").Range(Cells(58, 1), Cells(58, a)).Find(strDate, LookIn:= _
xlFormulas)
If Not rngFind Is Nothing Then
b = InputBox("How many rows needed?", "Rows", "1")
Range(Cells(rngFind.Row, rngFind.Column), Cells(rngFind.Row + b, rngFind.Column)).Copy
Sheets("Spreads2").Range("C95").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Else
MsgBox "Date not found!"
End If

End Sub
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Forum statistics

Threads
1,223,268
Messages
6,171,100
Members
452,379
Latest member
IainTru

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