I want to be able to safeguard against a user changing the name of a worksheet, so I use the sheet's codename in all my macros.
I have the codename of a sheet: shOptions
From a different workbook, "ActiveWorkbook.shOptions" doesn't work. How can I use "shOptions" in the other workbook.
This is a simplified version of an earlier, long winded post that clouded my actual question.
Can I use a variable to refer to a worksheet by its CodeName? For example, let's assume I have several worksheets, with CodeNames of mySheet1, mySheet2, mySheet3, and so on. I understand that I can refer to them directly,
mySheet1.Range(myRangeName).value = someValue
mySheet2.Range(myRangeName).value = someValue
mySheet3.Range(myRangeName).value = someValue
.
but what if I want to do this using a loop? Is it possible to preload an array of CodeNames and do it that way? I'm thinking along the lines of something like...
Dim CodeNames
CodeNames = Array( mySheet1, mySheet2, mySheet3 ...)
For x = 1 to HoweverMany
CodeNames(x).Range(myRangeName).value = someValue
Next x
I can get a worksheet name and index, how do I get the worksheet (Name)?
Example:
I delete sheet1, copy sheet3 before sheet2, rename to sheet1 but the VBA name is Sheet4. The index number will be 1 but it is not Sheet1 in the VBA Project Explorer.
I'm using this code to open another workbook:
Dim FileToOpen
FileToOpen = Application.GetOpenFilename _
(Title:="Please locate BlankSAMSRegister.xlsm", _
FileFilter:="BlankSAMSRegister.xlsm (*.xlsm),")
If FileToOpen = False Then
MsgBox "No file specified.", vbExclamation, "Error"
Exit Sub
End If
Once this second workbook is open, how do I refer to each one within the subsequent code?
I really need the parent workbook to be named as wb1 and the child to be names wb2 so I can flick between the two in the next macro... I can get the parent using thisworkbook.name, but how do I get the child?
I have the following code that creates a new workbook and names it after the value of a cell in another workbook. Another part of the macro will switch back and forth between the original workbook and the newly created workbook.
What is the best way to refer to new workbook? I'm familiar with the "workbooks "VARIABLE NAME".Activate" style but, the name of the new workbook will change every time the macro runs and I'm not sure how to refer to it.
the code below.
HTML Code:
Sub MakeNewBook()
Dim wB As Workbook
Dim nPath As String
[Code]....
I have to excel files, both have the same data. I created a pivot table macro on the first file. I want to be able to open the second file and use the shortcut to create the pivot table. the problem is that it refers back to the original workbook I created the macro on. How do I change the code to make it refer to the current workbook?
Code:
Sub Pivot()
'
' Pivot Macro
[Code]....
At the moment, I have a project where everyone is putting work data on seperate workbooks. Each months work is placed onto another worksheet within that workbook.
The supervisor has his own workbook, that grabs data from each of the workers books. Getting the data is easy enough, however because the supervisor has barely any knowledge of computers, I would like to make it as easy to set up for future months.
Right now, data is collected in each cell using the following formula:-
='[otherusersworkbook.xls]December 2009'!$C$620
Is there a way of getting that phrase to substitute the part that says "December 2009" with whatever is put into column A on the same row?
I have multiple sheets as Name1 ; Name2 ; Name3 and each sheet have a value on cell A1 for any number let say between 0 to 100. On another sheet call SheetName, I have A1=Name1 ; A2=Name2 ; A3=Name3.
I have formula as:
[Code] .....
The INDIRECT formula work to refer each cell A1 in each sheet, however if the sheet name include a space then it won't work anymore. How to make it work with the sheet name with space?
I've got a workbook that increments in sheets for each new week. I have a cell showing the current week number for that sheet ( lets say cell C1) and any formulas that need to reference data from the previous sheet do in 'indirect' function which basically looks at the current week number ( lets say week 20)in cell C1 and minus's 1 to navigate to the previous week (week 19).
I was wondering if there was a way to reference the previous sheet purely by the order they're in.
So say i have 5 sheets named "1,4,5,8,9" and these represent week numbers so the sheet named '8' in cell C1 would have '8' standanding for the week number but cells that wanted to reference the previous sheet couldn't do the 'indirect' C1-1 as there is no 'week 7'.
The weeks used could vary alot so a formula to reference the directly previous sheet is needed.
ok, thought best if i show you the actual formula
=IF(ISBLANK(C12),"",IF(ISERROR(VLOOKUP(C12,INDIRECT("'"&($AG$2-1)&"'!$C:$AG"),28,FALSE)),G12,SUM(G12,VLOOKUP(C12,INDIRECT("'"&($AG$2-1)&"'!$C:$AG"),28,FALSE))))
the '$A$G2-1' part is the bit that redirects to the previous sheet based on it's name, what i think i really need is to replace this part with a 'PrevSheet' function but i'm unsure how.
When I look in VBA under the Microsoft Excel Objects at the sheet names in my workbook I see two names for the same sheet like so:
sOrdersFB(Orders From Bob)
how to select using the first name sOrdersFB?
Dim k as string
K = "sheet1"
Sheets(k).select
This is the code 'm using
I have 50 sheets (1 sheet per staff member) that will be linked to a Master Sheet that compiles the data. The staff members are based in 5 teams of 10. The names of the sheets are based on the person's initials plus the words Progress Day,
All formulas in the master sheet are based around linking to 10 sheets so I can view the performance of each team. My problem is that the composition of each team changes quite often and so the links have to be manually updated for each team. Is there any way, for example, of compiling a table of team initials like the one below:
AT
EF
FY
DE
CB
RO
PR
TA
NE
SK
HS
that could easily be edited so that the formulae containing the sheet names relate to the table rather than static sheet names?
Eg., If AT left the above team and was replaced by PG then I would update the list above and the current formula
='C:Documents and SettingsAll UsersShared DesktopProgress Day Daisychain[AT Progress Day.xls]Sheet1'!D32+'[EF Progress Day.xls]Sheet1'!D32
would update to:
='C:Documents and SettingsAll UsersShared DesktopProgress Day Daisychain[PG Progress Day.xls]Sheet1'!D32+'[EF Progress Day.xls]Sheet1'!D32
Sheet names in Col B
B2 : Sheet1
B3 : Sheet2
Etc
I tried
=INDIRECT(B3,$J$58)
at C3 where $J$58 is the cell I would like to reference on 50 different sheets
Formula returns #REF!
Col C ref's $J$58
Col D ref's $L$58
Col E ref's $N$58
Col F ref's $P$58
So as I copy the 4 formulas down, the ref to the correct sheet should update but the cell ref's should remain
I am looking for a way to delete all the range names in a worksheet, not the whole workbook. I have found several procedures that will delete all the names in an activeworkbook. For some reason it takes too long to run. Does anyone know how to handle a single sheet in VBA?
View 6 Replies View RelatedI've been referencing sheets in my code directly with things like
Sheet1. Range(NamedRange)
however in recreating a new workbook (to reduce bloat), the sheet-numbering has changed... to avoid having to mess around adjusting things again, I was wondering if it's possible to do some sort of indirection... something similar to
#define SHEET_SC_CONFIG Sheet1
#define SHEET_CUSTOMER Sheet2
'and then use these throughout the code....
SHEET_SC_CONFIG.Range(NamedRange)
(cannot reference by sheet name, as end user may change these)
I am trying to use a variable to refer to the worksheet code name and get excel to tell me the name of the worksheet.
Dim ShNum As Integer
Dim ShCodeName As Worksheet
ShNum = 5
ShCodeName = "Sheet" & ShNum
'what i need is the worksheet name
MsgBox ShCodeName.Name
I am writing a VBA macro to update a financial workbook. 10 of the sheets within the file are re-names depending on the start of production date. So, in one file they may be CY09, CY10, CY11...and then in another CY10, CY11, CY12. I need to update the Cell L14 on the first CY page to a value on the BOM page.
I want CYXX!F12 = BOM!L14
And CYXX's code name is Sheet3411
To do this, I want to use the code name - because it is the same in all the files I have (100+).
But I cannot find enough/right Codename reference information to make this work.
I tried:
Sheet3411.Range("F12").FormulaR1C1 = "=BOM!R[2]C[6]"
Sheets(Sheet3411).Range("F12").FormulaR1C1.....
I would appreciate if someone could help with this particular issue. (A little explanation of when I can and cannot use the codename would be appreciated as well. As this is something I will be maintaining for years.)
The below example creates a new worksheet and tries to get the new worksheets codename. The problem is when I run the code, I can't seem to be able to pull the codename from the newly created sheet (ie Cell A1 = "" instead of "sheetx").
Sub test()Set mysht = ThisWorkbook.Worksheets.Add x = mysht.CodeName mysht.Range("A1") = xEnd Sub
I'm tring to create a newsheet and rename the codename but it doesnt seem to be working. Here is my
Private Sub Update_Button_Click()
AddYear = YearBox.Value
WFName = "WF Tracker SITE " + AddYear
wf = "WF_Edin_" + AddYear
Sheets.Add After:=WF_Template
ActiveSheet. Name = WFName
Sheets(WFName).CodeName = wf 'This line wont work
End Sub
Reference: [url]
I delete sheet1, copy sheet3, rename to sheet1 and then attempt to rename the CodeName to Sheet1 as well.
This worked initially with out errors.
Now on the first run of the macro it generates an error:
Run-time error '32813':
Method 'name' of object '_VBComponent' failed
This fails to rename the CodeName of "Sheet1" (.Name)
Run it again and it renames the CodeName but generates yet another error:
Run-time error '-2147221080 (800401a8)':
automation error
I have a Userform that allows a user to select a year eg (2007) from a listbox called yearbox where Yearbox.Value = AddYear. When the user Presses 'OK' the following code runs: ...
View 9 Replies View RelatedI've created a chart in sheet 1 in a workbook. I want to copy and paste that chart into another sheet (lets take sheet 2) of the same workbook. I am using Excel 2010 version.
Whenever, I try to copy a graph and want to do "Paste Special as Link picture". The problem I am facing as "Paste Link" option is inactive.
I am attaching the Excel for your reference.
The two sheets are...
1. Sheet1.xlsx (regular excel sheet as the extension is .xlsx)
2. Consolidated.xlsm (macro enabled sheet as its extension is .xlsm)
1. Copy both the files on your system under a particular folder.
2. Now make 9 replica's of Sheet1.xlsx and name them as Sheet2, Sheet3, Sheet4, .......... Sheet10
3. Open the sheet Consolidated.xlsm and see the button i have added called "Pull Data".
Now write down the code by adding a Module in that excel sheet from the code window.
4. Write a code that will open each of these sheets (Sheet1, Sheet2, Sheet3, Sheet4, .......... so on) one at a time and will copy the data from these sheets to the Consolidated.xlsm sheet.
5. Make sure that the data is appended(and not overwritten) from the multiple sheets (Sheet1, Sheet2, Sheet3). that is once you paste the data from sheet1 to Consolidated.xlsm then the Sheet2 data will be pasted at the end and after that sheet3 data will be pasted and so on..
6. In this way at the end we will have all the data from Sheet1, Sheet2, Sheet3...in the consolidated sheet.
7. The Division column in the Consolidated sheet will have the value of first row in these multiple sheets. So after making the replicas of Sheet1, please change the value in first row (Range A1) to any other value to avoid the confusion.
8. I have highlighted the data for two sheets in yellow and grey color in the consolidated sheet.
I have been trying to edit a code which previously saved a copy in a new workbook to a specific folder/path. (Additionally it copies and clears some figures, but this is working as it should.)
However I would like the copy to be saved at the same location as the original workbook, regardless of the path the original workbook is saved.
I.e if I need to move the workbook containing the code to a new folder/location, when using the macro, the new copy should be stored in the same folder/location as the original one.
For now it is only saving the copy into "My Documents"
Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 16-02-2009 by ceng
'
Sheets("Bunker ROB").Select
Sheets("Bunker ROB").Copy
ActiveWorkbook.SaveAs Filename:= _
ActiveWorkbook.Path & Range("D3"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
[Code] ........
I have data in 2 different workbooks, and I need to transfer all data in 'Jx_ex1' to sheet1 in the 'template' workbook, and all data in 'Gr_ex1' to sheet2 in 'template' workbook. All data should be transferred to the same cell numbers, from A1 to Y5000. I actually have loads of workbooks to transfer to the template but if I have an example with the two then im sure I can work out how to modify it. All files will be in the same folder.
View 8 Replies View RelatedI'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.
Code:
Sub SaveSheetAsNewBook()
Dim wb As Workbook
Dim InitFileName As String
Dim fileSaveName As String
Dim wshape As Shape
InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")
[code]....
I'm trying to figure out a way to find a specific sheet in a workbook that does not contain the macro. Within the macro I have a cell which holds the name of the specific sheet I would like to find but I can't get it to work for some reason...
'Dim officen As Integer
'Dim thiswb As Workbook
officen = Range("A2").Value
Set thiswb = ActiveWorkbook
' Open the Active Info file
Workbooks.Open "C:My DcoumentsActive 20080616.xls", , , , "xxxxxx"
' Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open"Active 20080616.xls"
Sheets("officen").Select
RowCount = ActiveSheet.UsedRange.Rows.Count
Range("B2").Select.............................
My requirement is as follows......
I want to get a copy of worksheet from specific workbook to active work book in which i want a copy of sheet get moved.
I have workbook having around 25 sheets and i want macro to merge all sheets except first (summary) in to last sheet ( mergedata ) inpute data will be start from cell A17 in all sheet. also sheets may be add or delete as per requirement. after merge data from sheet name should add after each entry .
attached sample file for your reference.
[URL]
I have a Picture in a workbook kept open. ( workbook 1 )And i have some X workbook open....i want a copy of sheet from workbook 1 To X workbook which is currently active.
View 1 Replies View Related