Hi,

Column A of Sheet1 holds the identity numbers of about 200 casual members of
our staffs. Column F shows their current weekly wages. I need a simple macro
that allows me to update the weekly wages for some of these members by 4.5%
from a list of their identity numbers shown in Column A of Sheet2. Any help
is much appreciated.

TIA
Tom

Here one shot at it:

Sub payBoost()
Dim lr As Long, sh As Worksheet, rng As Range
Dim fRng As Range
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & lr)
Set c = rng.Find(InputBox("Enter an ID number.", "ID NUMBER"), _
LookIn:=xlValues)
If Not c Is Nothing Then
Set fRng = Range("F" & c.Row)
fRng = fRng.Value + (fRng.Value * 0.045)
End If
End Sub

This will ask the user to input an identification number. It will then find
that number in column A and add 4.5% to the amount shown in column F and
post the new value to column F.



"Tom" <(E-Mail Removed)> wrote in message
news:6Vaun.17296$(E-Mail Removed)...
> Hi,
>
> Column A of Sheet1 holds the identity numbers of about 200 casual members
> of our staffs. Column F shows their current weekly wages. I need a simple
> macro that allows me to update the weekly wages for some of these members
> by 4.5% from a list of their identity numbers shown in Column A of Sheet2.
> Any help is much appreciated.
>
> TIA
> Tom
>
>

That was quick. It does what you intended it to do. However it
does not avoid the hard work of having to enter those numbers
one at a time. I was looking for a way for the program to
sequentially read the numbers in Sheet2, finds its corresponding
number in Sheet1 and then carry out the update. This way it saves
a lot of work. See if you can figure out a way for the program to do
just that. Thank you for your eforts.

"JLGWhiz" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Here one shot at it:
>
> Sub payBoost()
> Dim lr As Long, sh As Worksheet, rng As Range
> Dim fRng As Range
> Set sh = ActiveSheet
> lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
> Set rng = sh.Range("A2:A" & lr)
> Set c = rng.Find(InputBox("Enter an ID number.", "ID NUMBER"), _
> LookIn:=xlValues)
> If Not c Is Nothing Then
> Set fRng = Range("F" & c.Row)
> fRng = fRng.Value + (fRng.Value * 0.045)
> End If
> End Sub
>
> This will ask the user to input an identification number. It will then
> find that number in column A and add 4.5% to the amount shown in column F
> and post the new value to column F.
>
>
>
> "Tom" <(E-Mail Removed)> wrote in message
> news:6Vaun.17296$(E-Mail Removed)...
>> Hi,
>>
>> Column A of Sheet1 holds the identity numbers of about 200 casual members
>> of our staffs. Column F shows their current weekly wages. I need a simple
>> macro that allows me to update the weekly wages for some of these members
>> by 4.5% from a list of their identity numbers shown in Column A of
>> Sheet2. Any help is much appreciated.
>>
>> TIA
>> Tom
>>
>>
>
>

Tom,
In defense of JLGWhiz, you did say "...allows me to update the weekly wages
for some of these members by..." And that's what his code does. Had you
initially requested code to update them all, I'm certain he would have
provided exactly that. I'm certain that he overlooked, as I did, the
at-the-end of the post reference to identity numbers on sheet2.

So, try this code in a copy of your workbook and see if it does what you
want or not. You'll need to change the Const values at the beginning of it
after you do the copy to match worksheet names and column IDs in your
workbook before running it.

Sub UpdateWages()
'alter Const values as needed for your workbook
Const wageSheetName = "SheetWithWages" ' sheet1?
Const firstWGIDRow = 2 ' first row w/employee id
Const wsIDColumn = "A"
Const wswagecolumn = "F"
Const amtOfRaise = 0.045 ' 4.5%
Const updateListSheetName = "RaiseListSheet" ' sheet2?
Const lsIDColumn = "A"

Dim wgWS As Worksheet
Dim wgIdList As Range
Dim anywgID As Range
Dim lsWS As Worksheet
Dim lsIDList As Range
Dim anylsID As Range

Set wgWS = ThisWorkbook.Worksheets(wageSheetName)
Set wgIdList = wgWS.Range(wsIDColumn & firstWGIDRow & ":" _
& wgWS.Range(wsIDColumn & Rows.Count).End(xlUp).Address)
Set lsWS = ThisWorkbook.Worksheets(updateListSheetName)
Set lsIDList = lsWS.Range(lsIDColumn & ":" & lsIDColumn)
For Each anywgID In wgIdList

Set anylsID = lsIDList.Find(What:=anywgID, _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If Not anylsID Is Nothing Then
'found a match
wgWS.Range(wswagecolumn & anywgID.Row) = _
wgWS.Range(wswagecolumn & anywgID.Row) * (1 + amtOfRaise)
End If

Next
Set wgIdList = Nothing
Set lsIDList = Nothing
Set wgWS = Nothing
Set lsWS = Nothing
End Sub





"Tom" wrote:

> That was quick. It does what you intended it to do. However it
> does not avoid the hard work of having to enter those numbers
> one at a time. I was looking for a way for the program to
> sequentially read the numbers in Sheet2, finds its corresponding
> number in Sheet1 and then carry out the update. This way it saves
> a lot of work. See if you can figure out a way for the program to do
> just that. Thank you for your eforts.
>
> "JLGWhiz" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Here one shot at it:
> >
> > Sub payBoost()
> > Dim lr As Long, sh As Worksheet, rng As Range
> > Dim fRng As Range
> > Set sh = ActiveSheet
> > lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
> > Set rng = sh.Range("A2:A" & lr)
> > Set c = rng.Find(InputBox("Enter an ID number.", "ID NUMBER"), _
> > LookIn:=xlValues)
> > If Not c Is Nothing Then
> > Set fRng = Range("F" & c.Row)
> > fRng = fRng.Value + (fRng.Value * 0.045)
> > End If
> > End Sub
> >
> > This will ask the user to input an identification number. It will then
> > find that number in column A and add 4.5% to the amount shown in column F
> > and post the new value to column F.
> >
> >
> >
> > "Tom" <(E-Mail Removed)> wrote in message
> > news:6Vaun.17296$(E-Mail Removed)...
> >> Hi,
> >>
> >> Column A of Sheet1 holds the identity numbers of about 200 casual members
> >> of our staffs. Column F shows their current weekly wages. I need a simple
> >> macro that allows me to update the weekly wages for some of these members
> >> by 4.5% from a list of their identity numbers shown in Column A of
> >> Sheet2. Any help is much appreciated.
> >>
> >> TIA
> >> Tom
> >>
> >>
> >
> >
>
>
> .
>

Wow! You are very methodical and it performs exactly what I hope it would
do. Thank you very much.
I have a slightly similar request. This time getting back some data. Much
obliged if you can help with this task as outlined below:

Read a list of column names from two open workbooks - MyWorkbook1 in Sheet1,
starting from Column2 Row 3, then find the same name in Myworkbook2. Copy a
set of 5 row values next to its right, then paste it back in MyWorkbook1
next to the right and stop at the end of the list. Skip if a name is not
found.

"JLatham" <(E-Mail Removed)> wrote in message
news:482D56F9-DCDC-45BA-963C-(E-Mail Removed)...
> Tom,
> In defense of JLGWhiz, you did say "...allows me to update the weekly
> wages
> for some of these members by..." And that's what his code does. Had you
> initially requested code to update them all, I'm certain he would have
> provided exactly that. I'm certain that he overlooked, as I did, the
> at-the-end of the post reference to identity numbers on sheet2.
>
> So, try this code in a copy of your workbook and see if it does what you
> want or not. You'll need to change the Const values at the beginning of
> it
> after you do the copy to match worksheet names and column IDs in your
> workbook before running it.
>
> Sub UpdateWages()
> 'alter Const values as needed for your workbook
> Const wageSheetName = "SheetWithWages" ' sheet1?
> Const firstWGIDRow = 2 ' first row w/employee id
> Const wsIDColumn = "A"
> Const wswagecolumn = "F"
> Const amtOfRaise = 0.045 ' 4.5%
> Const updateListSheetName = "RaiseListSheet" ' sheet2?
> Const lsIDColumn = "A"
>
> Dim wgWS As Worksheet
> Dim wgIdList As Range
> Dim anywgID As Range
> Dim lsWS As Worksheet
> Dim lsIDList As Range
> Dim anylsID As Range
>
> Set wgWS = ThisWorkbook.Worksheets(wageSheetName)
> Set wgIdList = wgWS.Range(wsIDColumn & firstWGIDRow & ":" _
> & wgWS.Range(wsIDColumn & Rows.Count).End(xlUp).Address)
> Set lsWS = ThisWorkbook.Worksheets(updateListSheetName)
> Set lsIDList = lsWS.Range(lsIDColumn & ":" & lsIDColumn)
> For Each anywgID In wgIdList
>
> Set anylsID = lsIDList.Find(What:=anywgID, _
> LookIn:=xlFormulas, _
> LookAt:=xlPart, SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, MatchCase:=False, _
> SearchFormat:=False)
> If Not anylsID Is Nothing Then
> 'found a match
> wgWS.Range(wswagecolumn & anywgID.Row) = _
> wgWS.Range(wswagecolumn & anywgID.Row) * (1 + amtOfRaise)
> End If
>
> Next
> Set wgIdList = Nothing
> Set lsIDList = Nothing
> Set wgWS = Nothing
> Set lsWS = Nothing
> End Sub
>
>

I'm a little confused by "Copy a set of 5 rows next to its right..."

Let's say we are looking at the first name in MyWorkbook 1 (at B3) and have
found a match in MyWorkbook2 (at what cell?) then what rows/address range
gets copied, AND will new rows need to be inserted into MyWorkbook1 to paste
the information into.

Just to make sure: colums go up and down the sheet, rows go across it.

So if you can explain it something like this:
Match name in column B of workbook1 to name in column ?? of workbook2, then
copy 5 rows from workbook2 to workbook1, inserting new rows as needed.
Example:
Cell in workbook 1 B3, matches ??## in workbook2, copy ??## to ??##+4 into
workbook1 starting at ??#.

I need the column IDs for ?? and the row numbers for ## and #.

"Tom" wrote:

> Wow! You are very methodical and it performs exactly what I hope it would
> do. Thank you very much.
> I have a slightly similar request. This time getting back some data. Much
> obliged if you can help with this task as outlined below:
>
> Read a list of column names from two open workbooks - MyWorkbook1 in Sheet1,
> starting from Column2 Row 3, then find the same name in Myworkbook2. Copy a
> set of 5 row values next to its right, then paste it back in MyWorkbook1
> next to the right and stop at the end of the list. Skip if a name is not
> found.
>
> "JLatham" <(E-Mail Removed)> wrote in message
> news:482D56F9-DCDC-45BA-963C-(E-Mail Removed)...
> > Tom,
> > In defense of JLGWhiz, you did say "...allows me to update the weekly
> > wages
> > for some of these members by..." And that's what his code does. Had you
> > initially requested code to update them all, I'm certain he would have
> > provided exactly that. I'm certain that he overlooked, as I did, the
> > at-the-end of the post reference to identity numbers on sheet2.
> >
> > So, try this code in a copy of your workbook and see if it does what you
> > want or not. You'll need to change the Const values at the beginning of
> > it
> > after you do the copy to match worksheet names and column IDs in your
> > workbook before running it.
> >
> > Sub UpdateWages()
> > 'alter Const values as needed for your workbook
> > Const wageSheetName = "SheetWithWages" ' sheet1?
> > Const firstWGIDRow = 2 ' first row w/employee id
> > Const wsIDColumn = "A"
> > Const wswagecolumn = "F"
> > Const amtOfRaise = 0.045 ' 4.5%
> > Const updateListSheetName = "RaiseListSheet" ' sheet2?
> > Const lsIDColumn = "A"
> >
> > Dim wgWS As Worksheet
> > Dim wgIdList As Range
> > Dim anywgID As Range
> > Dim lsWS As Worksheet
> > Dim lsIDList As Range
> > Dim anylsID As Range
> >
> > Set wgWS = ThisWorkbook.Worksheets(wageSheetName)
> > Set wgIdList = wgWS.Range(wsIDColumn & firstWGIDRow & ":" _
> > & wgWS.Range(wsIDColumn & Rows.Count).End(xlUp).Address)
> > Set lsWS = ThisWorkbook.Worksheets(updateListSheetName)
> > Set lsIDList = lsWS.Range(lsIDColumn & ":" & lsIDColumn)
> > For Each anywgID In wgIdList
> >
> > Set anylsID = lsIDList.Find(What:=anywgID, _
> > LookIn:=xlFormulas, _
> > LookAt:=xlPart, SearchOrder:=xlByRows, _
> > SearchDirection:=xlNext, MatchCase:=False, _
> > SearchFormat:=False)
> > If Not anylsID Is Nothing Then
> > 'found a match
> > wgWS.Range(wswagecolumn & anywgID.Row) = _
> > wgWS.Range(wswagecolumn & anywgID.Row) * (1 + amtOfRaise)
> > End If
> >
> > Next
> > Set wgIdList = Nothing
> > Set lsIDList = Nothing
> > Set wgWS = Nothing
> > Set lsWS = Nothing
> > End Sub
> >
> >
>
>
> .
>

At the risk of getting way ahead of myself, I wrote the following code based
on what I am guessing you really want, and my idea of that is:
match names on 2 sheets in 2 different workbooks, and when a match is found,
then copy 5 COLUMNS next to the match in the second workbook into the first
one.

So if you find a match in 2nd workbook at B33 (name in first WB at B4) then
copy C33:G33 from 2nd workbook into C4:G4 of the first one. The various
column IDs are definable in the code.

Here's that code (note that it prompts you for the second workbook, so that
one should not be open when you run the macro). I've tried to keep the lines
short so that the system here doesn't mess things up. Check after you copy
the code for any red lines in your code, that just means that whatever is red
probably should be at the end of the line above it.


Sub CopyFrom2ndWorkbook()
'change these Const values as required
'name of the worksheet in this workbook
'to copy data into, is also the sheet
'with the source list of names
Const destinationSheetName = "Sheet1"
'first row with names in it
Const destSheet1stNameRow = 2
'column with the names in it
Const destSheetNamesCol = "B"
'1st column to copy information into
Const destSheet1stCopyCol = "C"
'last column to copy information into
Const destSheetLastCopyCol = "G"

'information about worksheet in the other
'workbook (one that will be opened and copied from)
Const sourceSheetName = "2ksPublicAssistance (3)"
Const srcSheetNamesCol = "B"
'first row with names in it
Const srcSheet1stNameRow = 2
'first column to copy from
Const srcSheet1stCopyCol = "C"
'last column to copy from
Const srcSheetLastCopyCol = "G"

Dim srcWB As Workbook ' will be copy from workbook
Dim srcWS As Worksheet ' will be copy from sheet
Dim srcNamesList As Range
Dim anySrcName As Range
Dim srcCopyRange As Range
Dim srcWBName As String

Dim destWS As Worksheet ' sheet in this workbook
Dim destNamesList As Range
Dim anyDestName As Range
Dim destCopyRange As Range

'prompt user to open the other workbook
srcWBName = Application.GetOpenFilename
If UCase(Trim(srcWBName)) = "FALSE" Then
'user cancelled the get filename operation
Exit Sub
End If
Application.ScreenUpdating = False
'open w/o updating links and as Read Only
Application.DisplayAlerts = False
Workbooks.Open srcWBName, False, True
Application.DisplayAlerts = True
'opened book becomes active
Set srcWB = ActiveWorkbook
'back to this workbook
ThisWorkbook.Activate
Set srcWS = srcWB.Worksheets(sourceSheetName)
Set srcNamesList = srcWS.Range(srcSheetNamesCol & srcSheet1stNameRow _
& ":" & srcWS.Range(srcSheetNamesCol & Rows.Count).End(xlUp).Address)

Set destWS = ThisWorkbook.Worksheets(destinationSheetName)
Set destNamesList = destWS.Range(destSheetNamesCol & destSheet1stNameRow _
& ":" & destWS.Range(destSheetNamesCol & Rows.Count).End(xlUp).Address)

'note that in VB, case is important: Bill does not = BILL
For Each anyDestName In destNamesList
For Each anySrcName In srcNamesList
If anySrcName = anyDestName Then
'have a match
'NOTE: number of columns in each range must be same
'not their addresses, but total number of columns, as
'C#:G# = 5 columns
Set srcCopyRange = srcWS.Range(srcSheet1stCopyCol _
& anySrcName.Row _
& ":" & srcSheetLastCopyCol & anySrcName.Row)
Set destCopyRange = destWS.Range(destSheet1stCopyCol &
anyDestName.Row _
& ":" & destSheetLastCopyCol & anyDestName.Row)
destCopyRange.Value = srcCopyRange.Value
'we can quit now that we found the match
Exit For ' exit the anySrcName loop
End If
Next
Next
'housekeeping
Set destNamesList = Nothing
Set srcNamesList = Nothing
Set srcWS = Nothing
Set destWS = Nothing
'close the other workbook, do not save changes
Application.DisplayAlerts = False
srcWB.Close False
Application.DisplayAlerts = True
Set srcWB = Nothing
MsgBox "Copy from:" & vbCrLf & srcWBName & vbCrLf & "Completed", _
vbOKOnly + vbInformation, "Task Finished"
End Sub



"Tom" wrote:

> Wow! You are very methodical and it performs exactly what I hope it would
> do. Thank you very much.
> I have a slightly similar request. This time getting back some data. Much
> obliged if you can help with this task as outlined below:
>
> Read a list of column names from two open workbooks - MyWorkbook1 in Sheet1,
> starting from Column2 Row 3, then find the same name in Myworkbook2. Copy a
> set of 5 row values next to its right, then paste it back in MyWorkbook1
> next to the right and stop at the end of the list. Skip if a name is not
> found.
>
> "JLatham" <(E-Mail Removed)> wrote in message
> news:482D56F9-DCDC-45BA-963C-(E-Mail Removed)...
> > Tom,
> > In defense of JLGWhiz, you did say "...allows me to update the weekly
> > wages
> > for some of these members by..." And that's what his code does. Had you
> > initially requested code to update them all, I'm certain he would have
> > provided exactly that. I'm certain that he overlooked, as I did, the
> > at-the-end of the post reference to identity numbers on sheet2.
> >
> > So, try this code in a copy of your workbook and see if it does what you
> > want or not. You'll need to change the Const values at the beginning of
> > it
> > after you do the copy to match worksheet names and column IDs in your
> > workbook before running it.
> >
> > Sub UpdateWages()
> > 'alter Const values as needed for your workbook
> > Const wageSheetName = "SheetWithWages" ' sheet1?
> > Const firstWGIDRow = 2 ' first row w/employee id
> > Const wsIDColumn = "A"
> > Const wswagecolumn = "F"
> > Const amtOfRaise = 0.045 ' 4.5%
> > Const updateListSheetName = "RaiseListSheet" ' sheet2?
> > Const lsIDColumn = "A"
> >
> > Dim wgWS As Worksheet
> > Dim wgIdList As Range
> > Dim anywgID As Range
> > Dim lsWS As Worksheet
> > Dim lsIDList As Range
> > Dim anylsID As Range
> >
> > Set wgWS = ThisWorkbook.Worksheets(wageSheetName)
> > Set wgIdList = wgWS.Range(wsIDColumn & firstWGIDRow & ":" _
> > & wgWS.Range(wsIDColumn & Rows.Count).End(xlUp).Address)
> > Set lsWS = ThisWorkbook.Worksheets(updateListSheetName)
> > Set lsIDList = lsWS.Range(lsIDColumn & ":" & lsIDColumn)
> > For Each anywgID In wgIdList
> >
> > Set anylsID = lsIDList.Find(What:=anywgID, _
> > LookIn:=xlFormulas, _
> > LookAt:=xlPart, SearchOrder:=xlByRows, _
> > SearchDirection:=xlNext, MatchCase:=False, _
> > SearchFormat:=False)
> > If Not anylsID Is Nothing Then
> > 'found a match
> > wgWS.Range(wswagecolumn & anywgID.Row) = _
> > wgWS.Range(wswagecolumn & anywgID.Row) * (1 + amtOfRaise)
> > End If
> >
> > Next
> > Set wgIdList = Nothing
> > Set lsIDList = Nothing
> > Set wgWS = Nothing
> > Set lsWS = Nothing
> > End Sub
> >
> >
>
>
> .
>

"JLatham" <(E-Mail Removed)> wrote in message
news:8B325DBE-6E34-41CF-B146-(E-Mail Removed)...
> I'm a little confused by "Copy a set of 5 rows next to its right..."

No. Copy a set of 5 row values next to its right, i.e. next 5 cells to its
right along that row.
These are the next 5 pieces of information for that particular person like,
Address, Post Code, Phone No., Mobile No. and Starting Date.

>
> Let's say we are looking at the first name in MyWorkbook 1 (at B3) and
> have
> found a match in MyWorkbook2 (at what cell?) then what rows/address range
> gets copied, AND will new rows need to be inserted into MyWorkbook1 to
> paste
> the information into.
>
> Just to make sure: colums go up and down the sheet, rows go across it.
>
> So if you can explain it something like this:
> Match name in column B of workbook1 to name in column ?? of workbook2,
> then
> copy 5 rows from workbook2 to workbook1, inserting new rows as needed.
> Example:
> Cell in workbook 1 B3, matches ??## in workbook2, copy ??## to ??##+4 into
> workbook1 starting at ??#.
>
> I need the column IDs for ?? and the row numbers for ## and #.
>
> "Tom" wrote:
>
>> Wow! You are very methodical and it performs exactly what I hope it would
>> do. Thank you very much.
>> I have a slightly similar request. This time getting back some data. Much
>> obliged if you can help with this task as outlined below:
>>
>> Read a list of column names from two open workbooks - MyWorkbook1 in
>> Sheet1,
>> starting from Column2 Row 3, then find the same name in Myworkbook2. Copy
>> a
>> set of 5 row values next to its right, then paste it back in MyWorkbook1
>> next to the right and stop at the end of the list. Skip if a name is not
>> found.
>>
>> "JLatham" <(E-Mail Removed)> wrote in message
>> news:482D56F9-DCDC-45BA-963C-(E-Mail Removed)...
>> > Tom,
>> > In defense of JLGWhiz, you did say "...allows me to update the weekly
>> > wages
>> > for some of these members by..." And that's what his code does. Had
>> > you
>> > initially requested code to update them all, I'm certain he would have
>> > provided exactly that. I'm certain that he overlooked, as I did, the
>> > at-the-end of the post reference to identity numbers on sheet2.
>> >
>> > So, try this code in a copy of your workbook and see if it does what
>> > you
>> > want or not. You'll need to change the Const values at the beginning
>> > of
>> > it
>> > after you do the copy to match worksheet names and column IDs in your
>> > workbook before running it.
>> >
>> > Sub UpdateWages()
>> > 'alter Const values as needed for your workbook
>> > Const wageSheetName = "SheetWithWages" ' sheet1?
>> > Const firstWGIDRow = 2 ' first row w/employee id
>> > Const wsIDColumn = "A"
>> > Const wswagecolumn = "F"
>> > Const amtOfRaise = 0.045 ' 4.5%
>> > Const updateListSheetName = "RaiseListSheet" ' sheet2?
>> > Const lsIDColumn = "A"
>> >
>> > Dim wgWS As Worksheet
>> > Dim wgIdList As Range
>> > Dim anywgID As Range
>> > Dim lsWS As Worksheet
>> > Dim lsIDList As Range
>> > Dim anylsID As Range
>> >
>> > Set wgWS = ThisWorkbook.Worksheets(wageSheetName)
>> > Set wgIdList = wgWS.Range(wsIDColumn & firstWGIDRow & ":" _
>> > & wgWS.Range(wsIDColumn & Rows.Count).End(xlUp).Address)
>> > Set lsWS = ThisWorkbook.Worksheets(updateListSheetName)
>> > Set lsIDList = lsWS.Range(lsIDColumn & ":" & lsIDColumn)
>> > For Each anywgID In wgIdList
>> >
>> > Set anylsID = lsIDList.Find(What:=anywgID, _
>> > LookIn:=xlFormulas, _
>> > LookAt:=xlPart, SearchOrder:=xlByRows, _
>> > SearchDirection:=xlNext, MatchCase:=False, _
>> > SearchFormat:=False)
>> > If Not anylsID Is Nothing Then
>> > 'found a match
>> > wgWS.Range(wswagecolumn & anywgID.Row) = _
>> > wgWS.Range(wswagecolumn & anywgID.Row) * (1 + amtOfRaise)
>> > End If
>> >
>> > Next
>> > Set wgIdList = Nothing
>> > Set lsIDList = Nothing
>> > Set wgWS = Nothing
>> > Set lsWS = Nothing
>> > End Sub
>> >
>> >
>>
>>
>> .
>>

This time round I'm the one that is confused. I mentioned that the workbooks
are, MyWorkbook1 in Sheet1 starting cursor position Column2 Row 3
(destination) and MyWorkbook2 (source) - no other parameters are needed for
the latter. Both are open. Using those information it should shorten the
codes somewhat.
Also, after a source name is found, can you do an offset like,
activecell.offset(0, 1).range("A1:A5").select to copy the 5 cells on the
right, bring it back to MyWorkbook1, do another offset, activecell.offset(0,
1).select and paste?Then reposition the cursor if needed.
The first line that stumped me is:

Set srcNamesList = srcWS.Range(srcSheetNamesCol & srcSheet1stNameRow _
& ":" & srcWS.Range(srcSheetNamesCol & Rows.Count).End(xlUp).Address)

What are the items that I have to replace? I never learned VBA but know how
to use Excel's automatic macro procedure. So, if you can modify some of the
lines it would be a great help. Thanks!

"JLatham" <(E-Mail Removed)> wrote in message
newsA4EB501-5C00-45C7-924D-(E-Mail Removed)...
> At the risk of getting way ahead of myself, I wrote the following code
> based
> on what I am guessing you really want, and my idea of that is:
> match names on 2 sheets in 2 different workbooks, and when a match is
> found,
> then copy 5 COLUMNS next to the match in the second workbook into the
> first
> one.
>
> So if you find a match in 2nd workbook at B33 (name in first WB at B4)
> then
> copy C33:G33 from 2nd workbook into C4:G4 of the first one. The various
> column IDs are definable in the code.
>
> Here's that code (note that it prompts you for the second workbook, so
> that
> one should not be open when you run the macro). I've tried to keep the
> lines
> short so that the system here doesn't mess things up. Check after you
> copy
> the code for any red lines in your code, that just means that whatever is
> red
> probably should be at the end of the line above it.
>
>
> Sub CopyFrom2ndWorkbook()
> 'change these Const values as required
> 'name of the worksheet in this workbook
> 'to copy data into, is also the sheet
> 'with the source list of names
> Const destinationSheetName = "Sheet1"
> 'first row with names in it
> Const destSheet1stNameRow = 2
> 'column with the names in it
> Const destSheetNamesCol = "B"
> '1st column to copy information into
> Const destSheet1stCopyCol = "C"
> 'last column to copy information into
> Const destSheetLastCopyCol = "G"
>
> 'information about worksheet in the other
> 'workbook (one that will be opened and copied from)
> Const sourceSheetName = "2ksPublicAssistance (3)"
> Const srcSheetNamesCol = "B"
> 'first row with names in it
> Const srcSheet1stNameRow = 2
> 'first column to copy from
> Const srcSheet1stCopyCol = "C"
> 'last column to copy from
> Const srcSheetLastCopyCol = "G"
>
> Dim srcWB As Workbook ' will be copy from workbook
> Dim srcWS As Worksheet ' will be copy from sheet
> Dim srcNamesList As Range
> Dim anySrcName As Range
> Dim srcCopyRange As Range
> Dim srcWBName As String
>
> Dim destWS As Worksheet ' sheet in this workbook
> Dim destNamesList As Range
> Dim anyDestName As Range
> Dim destCopyRange As Range
>
> 'prompt user to open the other workbook
> srcWBName = Application.GetOpenFilename
> If UCase(Trim(srcWBName)) = "FALSE" Then
> 'user cancelled the get filename operation
> Exit Sub
> End If
> Application.ScreenUpdating = False
> 'open w/o updating links and as Read Only
> Application.DisplayAlerts = False
> Workbooks.Open srcWBName, False, True
> Application.DisplayAlerts = True
> 'opened book becomes active
> Set srcWB = ActiveWorkbook
> 'back to this workbook
> ThisWorkbook.Activate
> Set srcWS = srcWB.Worksheets(sourceSheetName)
> Set srcNamesList = srcWS.Range(srcSheetNamesCol & srcSheet1stNameRow _
> & ":" & srcWS.Range(srcSheetNamesCol & Rows.Count).End(xlUp).Address)
>
> Set destWS = ThisWorkbook.Worksheets(destinationSheetName)
> Set destNamesList = destWS.Range(destSheetNamesCol & destSheet1stNameRow
> _
> & ":" & destWS.Range(destSheetNamesCol & Rows.Count).End(xlUp).Address)
>
> 'note that in VB, case is important: Bill does not = BILL
> For Each anyDestName In destNamesList
> For Each anySrcName In srcNamesList
> If anySrcName = anyDestName Then
> 'have a match
> 'NOTE: number of columns in each range must be same
> 'not their addresses, but total number of columns, as
> 'C#:G# = 5 columns
> Set srcCopyRange = srcWS.Range(srcSheet1stCopyCol _
> & anySrcName.Row _
> & ":" & srcSheetLastCopyCol & anySrcName.Row)
> Set destCopyRange = destWS.Range(destSheet1stCopyCol &
> anyDestName.Row _
> & ":" & destSheetLastCopyCol & anyDestName.Row)
> destCopyRange.Value = srcCopyRange.Value
> 'we can quit now that we found the match
> Exit For ' exit the anySrcName loop
> End If
> Next
> Next
> 'housekeeping
> Set destNamesList = Nothing
> Set srcNamesList = Nothing
> Set srcWS = Nothing
> Set destWS = Nothing
> 'close the other workbook, do not save changes
> Application.DisplayAlerts = False
> srcWB.Close False
> Application.DisplayAlerts = True
> Set srcWB = Nothing
> MsgBox "Copy from:" & vbCrLf & srcWBName & vbCrLf & "Completed", _
> vbOKOnly + vbInformation, "Task Finished"
> End Sub
>
>
>
> "Tom" wrote:
>
>> Wow! You are very methodical and it performs exactly what I hope it would
>> do. Thank you very much.
>> I have a slightly similar request. This time getting back some data. Much
>> obliged if you can help with this task as outlined below:
>>
>> Read a list of column names from two open workbooks - MyWorkbook1 in
>> Sheet1,
>> starting from Column2 Row 3, then find the same name in Myworkbook2. Copy
>> a
>> set of 5 row values next to its right, then paste it back in MyWorkbook1
>> next to the right and stop at the end of the list. Skip if a name is not
>> found.
>>
>> "JLatham" <(E-Mail Removed)> wrote in message
>> news:482D56F9-DCDC-45BA-963C-(E-Mail Removed)...
>> > Tom,
>> > In defense of JLGWhiz, you did say "...allows me to update the weekly
>> > wages
>> > for some of these members by..." And that's what his code does. Had
>> > you
>> > initially requested code to update them all, I'm certain he would have
>> > provided exactly that. I'm certain that he overlooked, as I did, the
>> > at-the-end of the post reference to identity numbers on sheet2.
>> >
>> > So, try this code in a copy of your workbook and see if it does what
>> > you
>> > want or not. You'll need to change the Const values at the beginning
>> > of
>> > it
>> > after you do the copy to match worksheet names and column IDs in your
>> > workbook before running it.
>> >
>> > Sub UpdateWages()
>> > 'alter Const values as needed for your workbook
>> > Const wageSheetName = "SheetWithWages" ' sheet1?
>> > Const firstWGIDRow = 2 ' first row w/employee id
>> > Const wsIDColumn = "A"
>> > Const wswagecolumn = "F"
>> > Const amtOfRaise = 0.045 ' 4.5%
>> > Const updateListSheetName = "RaiseListSheet" ' sheet2?
>> > Const lsIDColumn = "A"
>> >
>> > Dim wgWS As Worksheet
>> > Dim wgIdList As Range
>> > Dim anywgID As Range
>> > Dim lsWS As Worksheet
>> > Dim lsIDList As Range
>> > Dim anylsID As Range
>> >
>> > Set wgWS = ThisWorkbook.Worksheets(wageSheetName)
>> > Set wgIdList = wgWS.Range(wsIDColumn & firstWGIDRow & ":" _
>> > & wgWS.Range(wsIDColumn & Rows.Count).End(xlUp).Address)
>> > Set lsWS = ThisWorkbook.Worksheets(updateListSheetName)
>> > Set lsIDList = lsWS.Range(lsIDColumn & ":" & lsIDColumn)
>> > For Each anywgID In wgIdList
>> >
>> > Set anylsID = lsIDList.Find(What:=anywgID, _
>> > LookIn:=xlFormulas, _
>> > LookAt:=xlPart, SearchOrder:=xlByRows, _
>> > SearchDirection:=xlNext, MatchCase:=False, _
>> > SearchFormat:=False)
>> > If Not anylsID Is Nothing Then
>> > 'found a match
>> > wgWS.Range(wswagecolumn & anywgID.Row) = _
>> > wgWS.Range(wswagecolumn & anywgID.Row) * (1 + amtOfRaise)
>> > End If
>> >
>> > Next
>> > Set wgIdList = Nothing
>> > Set lsIDList = Nothing
>> > Set wgWS = Nothing
>> > Set lsWS = Nothing
>> > End Sub
>> >
>> >
>>
>>
>> .
>>