use an input box to select a range and copy to another worksheet

[Paul]

Hello, I would like a sub that allows a user to select a date. That row is
copied down to and including the person on call, and pasted into a newly
created sheet, displaying their corresponding start and finishing times. The
worksheet looks like this, column A is hidden

.........B..........C...........D............E...............F...................G..............H
.........MAIN................Monday...Tuesday...Wednesday...Thursday...Friday
..........Start....Finish...3 Apr...... 4
APR.........5.Apr.............6.Apr.........7 Apr
1.......7:00 15:00 TINA
2.......7:00 15:30 TRICIA.... SUE...........THERESE.......JACKIE.......ANNA
3.......7:30 16:00 JEANNE....ANNA .......FIONA............ANNA.........THERESE
4.......7:30 16:00 CHRIS.......................CHRIS
5.......7:30 15:00
..........................................................TRICIA
6................................................................................................
7.......Level 5............
8.......9:00 18:00....Jackie.......John..........ag Jeff...........ag James
9......10:00 20:30
....Carrie.....................................................................
10.....................................................................................................
11.....Transport.............................................................................
12 8:30 23:00
Harry...........................Peter.................
13..........................................................................................
7........CALL................james.....stuart.........aimee
8.............................................................................
9.......Rdo.......Jess
10.....Lsl.........carol

and I want the output to look like this
Tuesday
4 APR

7:00 15:30 SUE
7:30 16:00 JEANNE
7:30 16:00 CHRIS
7:30 15:00

Level 5............
9:00 18:00 Jackie
10:00 20:30 Carrie

Transport.
8:30 23:00 Harry

CALL................james


Rows are added at anytime, and taken away, so a simple select is not enough.
What do you suggest?

Your help is greatly appreciated !!!!!

 

[Tom Ogilvy]

What happened to the code I gave you?

 

[Paul]

Well Tom as you can see this is a totally different sheet, and a totally
different question. This time I would like the user to enter a date and then
print the times and the names, from the date selected, down to and including
the person on call. Your last post was extremely helpful, and worked with
that roster. This is a different roster. I would love your help... but I am
getting the feeling that I am undeserving.

 

[Paul]

Well Tom as you can see this is a totally different sheet, and a totally
different question. This time I would like the user to enter a date and then
print the times and the names, from the date selected, down to and including
the person on call. Your last post was extremely helpful, and worked with
that roster. This is a different roster. I would love your help... but I am
getting the feeling that I am undeserving.

 

[Tom Ogilvy]

Actually, it is difficult to tell what you have there. I will assume the
numbers on the left edge represent some type of references in column A and
are not part of the data. Start Times and tasks are in column B. Assumes
dates like 6 Apr are actually entered in the cell a 04/06/2006 so an actual
date is in the cell.

dt holds the users selected date

This should get you started


Dim rng as Range, rng2 as Range
Dim sh as Worksheet, sh1 as Worksheet
Dim res as Variant
Dim dt as Date, s as String

s =InputBox("Enter date")
if not isdate(s) then
msgbox "Entry was not a date"
exit sub
end if
dt = cdate(s)
set rng = Range("D2:H2")
res = application.match(clng(dt),rng,0)
if iserror(res) then
msgbox "Bad date provided"
exist sub
End if
set sh = Activesheet
sheets.Add after:=worksheets(worksheets.count)
set sh1 = activesheet.
sh.columns(2).Resize(,2).copy sh1.Range("A1")
rng(1,res).entireColumn.Copy sh1.Range("C1")
set rng2 = sh1.Columns(1).Find(What:="CALL", _
After:=Sh1.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if rng2 is nothing then
msgbox "Problems, Call not found"
exist sub
End if
sh1.Cells(rng2.row+1,1).Resize(100).EntireRow.Delete

 

[Paul]

Thank you soo much!!! That looked so much better than the procedure I had
typed. Sorry about the formatting... looked great when typing post.... So
extra thanks for taking the time. I was given this project from my boss who
changes her mind abit, so that is why it was different.

Tears of frustration have been flowing, and was not going to post as tried
to work it out. After a few days of frustration finally caved and posted.
so THANK YOU THANK YOU THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!

Just another question I was wondering how you could get the pasted columns
to be absolute values that changed when updating the main sheet. Any Ideas?

 

[Nicole Seibert]

Hi Paul,
You can use the absolute function in VBA.
For example ONLY:

my number = Abs(user inputed number)

would always give me a absolute.

If you want to refresh your data I believe that you have to set the data
source. You might want to try Microsoft help. There are special conditions
under which this will not work and I have not tried it. There may be others
you can help you further with this issue.

But the Abs function I have tried and it works well. Just incorporate that
into the code that Tom gave you.

HTH
Nicole