VB Code

[RP121]

Having some minor problems with some code, the code below when input a date into a input box, picks out the whole line of data within a workbook and copies it over into a other.

What I require is that currently when the data is copied pastes over to cell A2, I want it to start at M2 instead and move therefore in column M, also there seems to be a problem with the loop, it only picks out one single line of data when a date is inputted when I know there are around another 100 lines to be copied, don't think its lopping through all the data.


Sub Update_Button()
Dim x, y As Range, rThis As Long, rPrev As Long
x = InputBox("Enter Date of Update")
x = CDate(x)
ChDir "S:\Broker"
Workbooks.Open Filename:= _
"\\24\opsales\Broker\Total.xls"
With Workbooks("Records_File")
Set y = .Sheets("sheet1").Cells.Find(x)
rThis = y.Row
rPrev = rThis - 1
Do While (rPrev < rThis)
Windows("Summary 2005.xls").Activate
With .Sheets("Sheet1")
y.EntireRow.Copy .Cells(.[A1].CurrentRegion.Rows.Count + 1, 1)
End With
Set y = Cells.FindNext
rPrev = rThis
rThis = y.Row
Loop
End With
MsgBox ("Update Complete")
End Sub

Hope you can help

 

[erik.van.geit]

I would suggest you EDIT your two last posts, just delete everything in it
and try again to post the sample
first try out in the TEST-forum like suggested, see also FAQ: so many people before you succeeded to do this: you can do it too !

kind regards,
Erik

 

[RP121]

Below I've pasted a line of sample data, the first column is the column heading and the second is the data which should be copied a few times so there is a good sample, pasted it over a transpose so it's set in the same ways as I have it.

CallSign 228101
Patrol's PGA Check!
Successful Sale Y
Date of OT 38491.81528
Start 500
Finish 600
Hours 100
Accepted Y
OT PGA AT61
D42 Cost 24
Operator NA
Team BL
Date 38216.49854
Time 38216.49854
Comments
Re-Sale
Outgoing Sale
OT PGA AH63
Blue Status Hrs
Blue Status %

Cheers