Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
update one sheet with VBA from another sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
update one sheet with VBA from another sheet
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 -- Regards, Tom Ogilvy "René Kerstgens" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
update one sheet with VBA from another sheet
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
update one sheet with VBA from another sheet
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" wrote in message om... 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to update an excel sheet dynamically from another excel sheet? | Excel Discussion (Misc queries) | |||
Update a autofilter sheet from another sheet | Excel Discussion (Misc queries) | |||
Update from different sheet | Excel Discussion (Misc queries) | |||
insert query into excell sheet to update excell sheet and pivot table | Excel Discussion (Misc queries) | |||
update sheet on the web | Excel Discussion (Misc queries) |