Changing Sheet Names and dates

  • Thread starter Thread starter DAn
  • Start date Start date
D

DAn

I have a workbook which has several spreadsheets. All the
spreadsheets get a value from the main sheet ex.=main!A1.

Problem the main sheet name changes to coorespond with the
date.

1) Is there any way to use the =Main!A1 like a variable or
so it will adjust to the change?

2) You can autoupdate a cell with a =NOW() can you do
something like this for a sheetname? if so how?
 
Hi
if you use the sheetname in a cell reference don't worry as Excel will
change your formulas accordingly. Just try changing the sheetname.
 
I have a similar problem with worksheet names.
This works in 97:
Excel 97 Template with a hidden spreadsheet called
MyQueryData, and a visible spreadsheet called
ShowMyQueryData. (ShowMyQueryData spreadsheet displays
data on the MyQueryData spreadsheet by references/links
set in individual cells.)

Make a copy of template as a workbook in a new location.

From Access, use:
DoCmd.TransferSpreadsheet acExport, , _
"MyQueryData", <PathToMyExcelSpreadsheet>
to export the results from a query called MyQueryData
into the Excel workbook. Close Access.

Open the Excel spreadsheet and see that ShowMyQueryData
shows the data that was exported from Access.

(I don't know if the export replaces the existing
MyQueryData spreadsheet with a new MyQueryData
spreadsheet or if the export puts the data into the
existing MyQueryData spreadsheet.)

When I try this using Access XP and Excel XP, the
exported data (MyQueryData) shows up in a new spreadsheet
called MyQueryData1. I have tried the
DoCmd.TransferSpreadsheet using acSpreadsheetTypeExcel9
in the second parameter and with it blank. Does not seem
to make a difference in XP either way.

I am wondering if there is some way to refer to a
worksheet name through the data in a cell, so that I
could store the name of data spreadsheet that will be
exported and use that in the display sheet's cell's
references.

Any suggestions are appreciated.

JoAnn
 
Private Sub Worksheet_Calculate()
'or Private Sub Worksheet_Activate()
ActiveSheet.Name = Format(Now(), "dddd-mmm-yy")
End Sub

OR in the ThisWorkbook module so the sheet name updates when the workbook is
opened.

Private Sub Workbook_Open()
Worksheets(1) = Format(Now(), "dddd-mmm-yy")
End Sub

Worksheets(1) is the first sheet in the workbook.

Gord Dibben Excel MVP
 
Back
Top