Good Day,

I'm trying to write a macro and i have never used this before so i'm teaching
my self as i go along
This is what i have so far but the problem is with the "Reported to Payroll".
The situation is the way i have it now is good if everyone works everyday and
and is never absent, but we all know how that goes so therefore "Reported to
Payroll" will keep moving around and thats no problem it's just that i need
the 8 cells next to it and the values in each of those cells are always
changing so i cant use the FIND command. The way i have it working now is it
will find a person's name copy to the other spreadsheet and then it will find
that persons "Report to Payroll" because it's underneath(its about 3 columns
over and 35 down) their name and I have over 300 employees to do this for so
is there a formula that i can use that will always find "Reported To Payroll"
and the 8 cells next to no matter where it is on the spreadsheet (keep in
mind that Reported To Payroll appears over 300 times on the spreadsheet)

(this is how it looks when i dwnld the report)
"Name"
...
...
...
...
"Report to payroll" x x x x x x x
x

(this is what i have now)

Cells.Find(What:="name", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Windows("TMSPhatomReport.xls").Activate
Windows("TMSDL.XLS").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("TMSPhatomReport.xls").Activate
ActiveSheet.Paste
Range("B33").Select
Windows("TMSDL.XLS").Activate
Cells.Find(What:="REPORTED TO PAYROLL", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=
_
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Windows("TMSPhatomReport.xls").Activate
Windows("TMSDL.XLS").Activate
Range("C974:K974").Select
Application.CutCopyMode = False
Selection.Copy
Windows("TMSPhatomReport.xls").Activate
ActiveSheet.Paste
Range("A34").Select
Windows("TMSDL.XLS").Activate
Range("A974").Select
Windows("TMSPhatomReport.xls").Activate
Windows("TMSDL.XLS").Activate

If anyone can help this would be greatly appreicated

MartÃ*n Hinojosa

--
Message posted via http://www.officekb.com

Try something like this:
Sub FindPayRoll()
Dim r As Range
Dim fC As Range 'First Cell
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set r = ws1.Cells.Find(What:="REPORTED TO PAYROLL", _
After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If r Is Nothing Then Exit Sub
Set fC = r
Do
r.Resize(1,9).Copy ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1)
Set r = ws1.Cells.Find(What:="REPORTED TO PAYROLL", _
After:=r, LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
Loop Until r.Address = fC.Address
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"Hinojosa via OfficeKB.com" wrote:

> Good Day,
>
> I'm trying to write a macro and i have never used this before so i'm teaching
> my self as i go along
> This is what i have so far but the problem is with the "Reported to Payroll".
> The situation is the way i have it now is good if everyone works everyday and
> and is never absent, but we all know how that goes so therefore "Reported to
> Payroll" will keep moving around and thats no problem it's just that i need
> the 8 cells next to it and the values in each of those cells are always
> changing so i cant use the FIND command. The way i have it working now is it
> will find a person's name copy to the other spreadsheet and then it will find
> that persons "Report to Payroll" because it's underneath(its about 3 columns
> over and 35 down) their name and I have over 300 employees to do this for so
> is there a formula that i can use that will always find "Reported To Payroll"
> and the 8 cells next to no matter where it is on the spreadsheet (keep in
> mind that Reported To Payroll appears over 300 times on the spreadsheet)
>
> (this is how it looks when i dwnld the report)
> "Name"
> ...
> ...
> ...
> ...
> "Report to payroll" x x x x x x x
> x
>
> (this is what i have now)
>
> Cells.Find(What:="name", After:=ActiveCell, LookIn:= _
> xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
> xlNext, MatchCase:=False, SearchFormat:=False).Activate
> Windows("TMSPhatomReport.xls").Activate
> Windows("TMSDL.XLS").Activate
> Application.CutCopyMode = False
> Selection.Copy
> Windows("TMSPhatomReport.xls").Activate
> ActiveSheet.Paste
> Range("B33").Select
> Windows("TMSDL.XLS").Activate
> Cells.Find(What:="REPORTED TO PAYROLL", After:=ActiveCell, LookIn:= _
> xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=
> _
> xlNext, MatchCase:=False, SearchFormat:=False).Activate
> Windows("TMSPhatomReport.xls").Activate
> Windows("TMSDL.XLS").Activate
> Range("C974:K974").Select
> Application.CutCopyMode = False
> Selection.Copy
> Windows("TMSPhatomReport.xls").Activate
> ActiveSheet.Paste
> Range("A34").Select
> Windows("TMSDL.XLS").Activate
> Range("A974").Select
> Windows("TMSPhatomReport.xls").Activate
> Windows("TMSDL.XLS").Activate
>
> If anyone can help this would be greatly appreicated
>
> MartÃ*n Hinojosa
>
> --
> Message posted via http://www.officekb.com
>
>

I'm sorry i'm new at this, what does r=to, and would i do this for each
person?

Charles Chickering wrote:
>Try something like this:
>Sub FindPayRoll()
>Dim r As Range
>Dim fC As Range 'First Cell
>Dim ws1 As Worksheet
>Dim ws2 As Worksheet
>Set ws1 = Worksheets("Sheet1")
>Set ws2 = Worksheets("Sheet2")
>Set r = ws1.Cells.Find(What:="REPORTED TO PAYROLL", _
> After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole, _
> SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False, SearchFormat:=False)
>If r Is Nothing Then Exit Sub
>Set fC = r
>Do
> r.Resize(1,9).Copy ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1)
> Set r = ws1.Cells.Find(What:="REPORTED TO PAYROLL", _
> After:=r, LookIn:=xlFormulas, LookAt:=xlWhole, _
> SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False, SearchFormat:=False)
>Loop Until r.Address = fC.Address
>End Sub
>> Good Day,
>>
>[quoted text clipped - 55 lines]
>>
>> MartÃ*n Hinojosa

--
Message posted via http://www.officekb.com

Martin, Can you send a copy of the workbook and a more detailed description
of what you want to happen to: (E-Mail Removed) ?
--
Charles Chickering

"A good example is twice the value of good advice."


"Hinojosa via OfficeKB.com" wrote:

> I'm sorry i'm new at this, what does r=to, and would i do this for each
> person?
>
> Charles Chickering wrote:
> >Try something like this:
> >Sub FindPayRoll()
> >Dim r As Range
> >Dim fC As Range 'First Cell
> >Dim ws1 As Worksheet
> >Dim ws2 As Worksheet
> >Set ws1 = Worksheets("Sheet1")
> >Set ws2 = Worksheets("Sheet2")
> >Set r = ws1.Cells.Find(What:="REPORTED TO PAYROLL", _
> > After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole, _
> > SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> > MatchCase:=False, SearchFormat:=False)
> >If r Is Nothing Then Exit Sub
> >Set fC = r
> >Do
> > r.Resize(1,9).Copy ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1)
> > Set r = ws1.Cells.Find(What:="REPORTED TO PAYROLL", _
> > After:=r, LookIn:=xlFormulas, LookAt:=xlWhole, _
> > SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> > MatchCase:=False, SearchFormat:=False)
> >Loop Until r.Address = fC.Address
> >End Sub
> >> Good Day,
> >>
> >[quoted text clipped - 55 lines]
> >>
> >> MartÃ*n Hinojosa
>
> --
> Message posted via http://www.officekb.com
>
>

you'll have it by 0900

Charles Chickering wrote:
>Martin, Can you send a copy of the workbook and a more detailed description
>of what you want to happen to: (E-Mail Removed) ?
>> I'm sorry i'm new at this, what does r=to, and would i do this for each
>> person?
>[quoted text clipped - 26 lines]
>> >>
>> >> MartÃ*n Hinojosa

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1