Help with user prompt
 

Hi all,

I have a macro with the following code:

thisDate = Range("Dates!A4").Value
Sheets("Data").Select
Range("G1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("E6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Find(What:=thisDate, After:=ActiveCell,
LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("PAC Track").Select
Range("C6").Select
Application.CutCopyMode = False
End Sub

As there is the possibility that a user may forget to change the date
before running this, I wish to replace the first line with code that
will open a prompt asking the user to enter the date for the data
entered. This date will then become "thisDate" and the rest of the
code can run.

Please could someone help with this

Thanks...Rob

thisdate = InputBox("Pls enter date mm/dd/yy")

untested but this might work from anywhere in the workbook.
thisdate = InputBox("Pls enter date mm/dd/yy")
with sheets("Data")
ds=.columns(5).find(thisdate).address
..[ds].offset(,2)=.range("g1").end(xltoright).value
end with

--
Don Guillett
SalesAid Software

"Rob Gould" wrote in message
oups.com...
Hi all,

I have a macro with the following code:

thisDate = Range("Dates!A4").Value
Sheets("Data").Select
Range("G1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("E6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Find(What:=thisDate, After:=ActiveCell,
LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("PAC Track").Select
Range("C6").Select
Application.CutCopyMode = False
End Sub

As there is the possibility that a user may forget to change the date
before running this, I wish to replace the first line with code that
will open a prompt asking the user to enter the date for the data
entered. This date will then become "thisDate" and the rest of the
code can run.

Please could someone help with this

Thanks...Rob

Thanks Don, but that doesn't seem to set the date entered into the
input box as "thisDate" and the process stops during the search for the
date in column E on Data sheet.

Rob

Don Guillett wrote:
thisdate = InputBox("Pls enter date mm/dd/yy")

untested but this might work from anywhere in the workbook.
thisdate = InputBox("Pls enter date mm/dd/yy")
with sheets("Data")
ds=.columns(5).find(thisdate).address
.[ds].offset(,2)=.range("g1").end(xltoright).value
end with

--
Don Guillett
SalesAid Software

"Rob Gould" wrote in message
oups.com...
Hi all,

I have a macro with the following code:

thisDate = Range("Dates!A4").Value
Sheets("Data").Select
Range("G1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("E6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Find(What:=thisDate, After:=ActiveCell,
LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("PAC Track").Select
Range("C6").Select
Application.CutCopyMode = False
End Sub

As there is the possibility that a user may forget to change the
date
before running this, I wish to replace the first line with code
that
will open a prompt asking the user to enter the date for the data
entered. This date will then become "thisDate" and the rest of the
code can run.

Please could someone help with this

Thanks...Rob

A couple of changes. Use this instead TESTED. Input date as 6/1 for current
year or 7/1/2004 for another year. I tested on a sheet named Data from
another sheet in the workbook. Worked fine assuming that there was something
in row 1 after column G.

thisdate = InputBox("Pls enter date mm/dd/yyyy")
With Sheets("Data")
ds = .Columns(5).Find(thisdate).Address
..Range(ds).Offset(, 2) = .Range("g1").End(xlToRight).Value
End With

--
Don Guillett
SalesAid Software

"Rob Gould" wrote in message
ups.com...
Thanks Don, but that doesn't seem to set the date entered into the
input box as "thisDate" and the process stops during the search for the
date in column E on Data sheet.

Rob

Don Guillett wrote:
thisdate = InputBox("Pls enter date mm/dd/yy")

untested but this might work from anywhere in the workbook.
thisdate = InputBox("Pls enter date mm/dd/yy")
with sheets("Data")
ds=.columns(5).find(thisdate).address
.[ds].offset(,2)=.range("g1").end(xltoright).value
end with

--
Don Guillett
SalesAid Software

"Rob Gould" wrote in message
oups.com...
Hi all,

I have a macro with the following code:

thisDate = Range("Dates!A4").Value
Sheets("Data").Select
Range("G1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("E6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Find(What:=thisDate, After:=ActiveCell,
LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("PAC Track").Select
Range("C6").Select
Application.CutCopyMode = False
End Sub

As there is the possibility that a user may forget to change the
date
before running this, I wish to replace the first line with code
that
will open a prompt asking the user to enter the date for the data
entered. This date will then become "thisDate" and the rest of the
code can run.

Please could someone help with this

Thanks...Rob

Don,

Well, I'm not sure what I'm doing wrong then. I used that code and it
stops on this line:

ds = .Columns(5).Find(thisdate).Address

Even tried it in a new workbook with a worksheet called Data and same
thing...

As I said, this was tested and worked fine. Either you don't have an
appropriate date in column 5 (a,b,c,d,E) or your dates are not formatted as
dates or you didn't enter the date question as directed.

--
Don Guillett
SalesAid Software

"Rob Gould" wrote in message
oups.com...
Don,

Well, I'm not sure what I'm doing wrong then. I used that code and it
stops on this line:

ds = .Columns(5).Find(thisdate).Address

Even tried it in a new workbook with a worksheet called Data and same
thing...