Move Specific Text to new worksheet
 

I have 2 worksheets in the same workbook, "Sheet 1" and "Sheet 2". From Sheet
2, I need to pull the count from Col H where Col A states, "Account
Employees" and place that number in Cell B12 on Sheet 1. Then I want to copy
the count from Col H where Col A states, "Accounting Exempt" and copy that
count to Cell B13 on Sheet 1. I have about 100 of these counts I need to
copy each day so I'd like to automate it with a macro. Can anybody help me
get started with this, thanks

Move Specific Text to new worksheet
 

Sub CopyData()
Dim v as Variant, v1 as Variant
Dim sStr as String, i as Long, rng as Range
v = Array("Account Employees", "Accounting Exempt")
v1 = Array("B12","B13")
for i = lbound(v) to ubound(v)
sStr = v(i)
set rng = Worksheets("Sheet2").Range("A:A").Find(What:=sStr, _
After:=Range("A65536"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Worksheets("Sheet1").Range(v1(i)).Value = _
Worksheets("Sheet2").Range("H" & rng.Row).Value
End If
Next i
End Sub

This is representative. It is specific to the situation you described, but
should show how you can generalize what you need to do. If there is a
pattern in what you are doing, you can program to that pattern. If not, you
might need to list the key information fro each of the hundred counts you
need to process.

--
Regards,
Tom Ogilvy



"glensfallslady" wrote in message
...
I have 2 worksheets in the same workbook, "Sheet 1" and "Sheet 2". From
Sheet
2, I need to pull the count from Col H where Col A states, "Account
Employees" and place that number in Cell B12 on Sheet 1. Then I want to
copy
the count from Col H where Col A states, "Accounting Exempt" and copy that
count to Cell B13 on Sheet 1. I have about 100 of these counts I need to
copy each day so I'd like to automate it with a macro. Can anybody help
me
get started with this, thanks

Move Specific Text to new worksheet
 

HI Tom,

I'm sort of following you here but I don't understand what this line is
doing, can you clarify this for me so I'll know for the future.
After:=Range("A65536"), _

Also can I list all +- 100 in one array or would that be too much?

Thanks for your tremendous help.



"Tom Ogilvy" wrote:

Sub CopyData()
Dim v as Variant, v1 as Variant
Dim sStr as String, i as Long, rng as Range
v = Array("Account Employees", "Accounting Exempt")
v1 = Array("B12","B13")
for i = lbound(v) to ubound(v)
sStr = v(i)
set rng = Worksheets("Sheet2").Range("A:A").Find(What:=sStr, _
After:=Range("A65536"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Worksheets("Sheet1").Range(v1(i)).Value = _
Worksheets("Sheet2").Range("H" & rng.Row).Value
End If
Next i
End Sub

This is representative. It is specific to the situation you described, but
should show how you can generalize what you need to do. If there is a
pattern in what you are doing, you can program to that pattern. If not, you
might need to list the key information fro each of the hundred counts you
need to process.

--
Regards,
Tom Ogilvy



"glensfallslady" wrote in message
...
I have 2 worksheets in the same workbook, "Sheet 1" and "Sheet 2". From
Sheet
2, I need to pull the count from Col H where Col A states, "Account
Employees" and place that number in Cell B12 on Sheet 1. Then I want to
copy
the count from Col H where Col A states, "Accounting Exempt" and copy that
count to Cell B13 on Sheet 1. I have about 100 of these counts I need to
copy each day so I'd like to automate it with a macro. Can anybody help
me
get started with this, thanks

Move Specific Text to new worksheet
 

Thanks Tom, my report is working great....thanks for your help!

"Tom Ogilvy" wrote:

Sub CopyData()
Dim v as Variant, v1 as Variant
Dim sStr as String, i as Long, rng as Range
v = Array("Account Employees", "Accounting Exempt")
v1 = Array("B12","B13")
for i = lbound(v) to ubound(v)
sStr = v(i)
set rng = Worksheets("Sheet2").Range("A:A").Find(What:=sStr, _
After:=Range("A65536"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Worksheets("Sheet1").Range(v1(i)).Value = _
Worksheets("Sheet2").Range("H" & rng.Row).Value
End If
Next i
End Sub

This is representative. It is specific to the situation you described, but
should show how you can generalize what you need to do. If there is a
pattern in what you are doing, you can program to that pattern. If not, you
might need to list the key information fro each of the hundred counts you
need to process.

--
Regards,
Tom Ogilvy



"glensfallslady" wrote in message
...
I have 2 worksheets in the same workbook, "Sheet 1" and "Sheet 2". From
Sheet
2, I need to pull the count from Col H where Col A states, "Account
Employees" and place that number in Cell B12 on Sheet 1. Then I want to
copy
the count from Col H where Col A states, "Accounting Exempt" and copy that
count to Cell B13 on Sheet 1. I have about 100 of these counts I need to
copy each day so I'd like to automate it with a macro. Can anybody help
me
get started with this, thanks