update one sheet with VBA from another sheet

  • Thread starter Thread starter René Kerstgens
  • Start date Start date
R

René Kerstgens

Does anyone know how to update (replace contents of )a range with the
content of another sheets. In fact:

A have a sheet with in column A range 20 till 30 a range of names. The
name of the sheet differs every week according to the weeknumber. In
another sheet i have the new names en while opening this recent sheet
i want it to lookup the most recent weeksheet and open it and replace
the contents with the most recent one.

Has anyone an idea how to do this.

Thanks in advance

René Kerstgens
 
As a general approach, this might give you some ideas. shSource will hold
the new list. Right now, it assumes both workbooks are open. You didn't
say where the new list is located, so assume Range("A20:A30"), same as the
weeknum sheets.

Dim shSource as Worksheet
Dim sh as Worksheet
Dim imax as Long
Dim sName as String

set shSource = Workbooks("NewData.xls").Worksheets(1)
With Workbooks("WeeknumBook.xls")
for each sh in .Worksheets
if isnumeric(sh.name) then
if clng(sh.name) > imax then
imax = clng(sh.name)
sName = sh.name
end if
end if
Next

..Worksheets(sName).Range("A20:A30").Value = _
shSource.Range("A20:A30").Value
 
Rene,

Make the range("A20:A30") a named range e.g "myRange" then in the VBE
editor window, in the projest explorer section(left hand side),
double-click on the worksheet that you wish to update automatically,
at the top of the right hand code window there are two small windows
with dropdowns - in the left hand select "worksheet"and in the right
hand one select "Activate"

Then enter the code below:

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Sheets("Sheet2").[A20:A30].Copy [myrange]
Application.ScreenUpdating = True
End Sub

This will rely upon the new sheet always being positioned as the 2nd
sheet in the book.If the latest sheet was always the last in the book
you could use:

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Sheets(Sheets.Count).[A20:A30].Copy [myrange]
Application.ScreenUpdating = True
End Sub

Not sure if this helps at all

Jason
 
This will rely upon the new sheet always being positioned as the 2nd
sheet in the book.

No, it will rely on the new sheet always being named sheet2 and also being
located in the workbook with the sheet being updated.

--
Regards,
Tom Ogilvy



jason said:
Rene,

Make the range("A20:A30") a named range e.g "myRange" then in the VBE
editor window, in the projest explorer section(left hand side),
double-click on the worksheet that you wish to update automatically,
at the top of the right hand code window there are two small windows
with dropdowns - in the left hand select "worksheet"and in the right
hand one select "Activate"

Then enter the code below:

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Sheets("Sheet2").[A20:A30].Copy [myrange]
Application.ScreenUpdating = True
End Sub

This will rely upon the new sheet always being positioned as the 2nd
sheet in the book.If the latest sheet was always the last in the book
you could use:

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Sheets(Sheets.Count).[A20:A30].Copy [myrange]
Application.ScreenUpdating = True
End Sub

Not sure if this helps at all

Jason


René Kerstgens <[email protected]> wrote in message
Does anyone know how to update (replace contents of )a range with the
content of another sheets. In fact:

A have a sheet with in column A range 20 till 30 a range of names. The
name of the sheet differs every week according to the weeknumber. In
another sheet i have the new names en while opening this recent sheet
i want it to lookup the most recent weeksheet and open it and replace
the contents with the most recent one.

Has anyone an idea how to do this.

Thanks in advance

René Kerstgens
Click to expand...
Click to expand...
 
Back
Top