Accessing Worksheets Via A Variable Name
Apr 21, 2008
i have a simple PO tracking system, i have a workbook with multiple sheets at any one time approximately 20, projects come and go and i need to add a sheet periodically, rename the tab to the project ID - 4x numeric.
All this i have managed to do, and sort the sheets numerically, i now need to copy the last (added) row on the Project specific sheet to the last clear row on the summary sheet.
I have also achieved this, but in order to do it properly i need to switch between the specific Project sheet i am adding the current PO to, (any one of 20) and the summary data sheet, the sheet i am copying to.
To achieve this i thought i could assign the TabName (of the Project) to a variable and then refer to it
Sheets(TabName).Select. using msgbox throughout appears to provide the correct results, however, this produces a {subscript out of range error no9} on this particular line. Any suggestions welcome. Same issue i believe i could address my problem if i could whilst in the Project specific sheet i could determine the codename of the sheet which i believe i could then use to switch between sheets programatically.
View 9 Replies
ADVERTISEMENT
Jun 27, 2008
I have read all the rules, and the guidelines for writting a thread title, and can only hope that my title meets those criteria (Dave, if they do not please do not ban me). I am fairly new to VBA, I have been able to edit other peoples code, and have created a few macros myself, and although I think I know how to use a loop and arrays, I sometimes lack some of the basics, this may be one such case. That said here is the problem I am facing.
I am creating a database, and as such I am sent several workbooks, with each workbook containning several worksheets. Within these workbooks, the naming convention of the worksheets is consistent, though each workbook may contain different number of worksheets. (the name of the worksheets represents a specific size/format).
What I have is a "Setup sheet" that I would like populated with all the data from specific worksheets in the various workbooks. So I would need to be able to get the macro to check all the workbooks, and if it finds a worksheet entitled "Sheet B" (for example) that it copies all that data into the "Setup sheet", and progresses onto the next workbook. I have the loop working and am fairly confident on this side of things, though where I am struggling is getting the Macro to know what worksheet to look at when going through the workbooks.
I need the macro to be fairly dynamic in which Worksheet it looks at so I don't want to simply hardcode the Worksheet name into the macro, rather I would like to be able to specify the worksheet name based on a cell value in the "Setup sheet". so going back to my earlier example if the cell value in the "Setup sheet" = "Sheet B" then the macro should get data from the worksheets entitled "SheetB" in the workbooks, if the cell value in the "Setup sheet" = "Sheet F" then the macro should get data from the worksheets entitled "Sheet F".
View 2 Replies
View Related
Mar 31, 2009
I am using Excel 2007 with an extension from Adapx, Inc. of Seattle for electronic data capture and input.
I am trying to automate a process of inspecting and recording inspection data that has been collected and entered manually to this point. The data currently comes in as a variable quantity of 8.5 X 11 sheets. Some reports are 1 sheet; some are 7 or more sheets. In any case, the first sheet has all the ID information with some details and details of other samples are on subsequent sheets. When done manually, they are stapled together.
When done electronically, the report comes in as a series of worksheets. I describe the code I need as an electronic “staple” of a variable quantity of worksheets in Excel.
That is to say, I need to identify a group of worksheets as a single inspection report.
In order to make the plan work, I have the following idea. This is just my starting point. I am open to any ideas you care to share.
I need to have the current worksheet look at a specific cell in the previous work sheet.
If the value matches the value of the co-responding cell in the current work sheet, then copy the values of several specific cells into the current worksheet. This will repeat until the trigger value changes. It will increment by one. When it changes, I need to look at the next work sheet. If the trigger value matches the new trigger it will again copy the values from a specific set of cells of the current 'previous' worksheet into the then current work sheet. This sequence will repeat until there are no additional worksheets to process.
View 10 Replies
View Related
Jun 27, 2006
I am usually able to find the answers to any questions I have by searching the forum or internet. Unfortunately I ran into one that I am not even sure is possible at this time. I have 7 macros like the following. One for each day up to 7. The macros create four pivot tables on the page. I wanted to find out if I could combine it into one macro using variables. Unfortunately every attempt I have made results in an error.
Sub Day1Table1()
Sheets.Add. Name = "Day 1 Tables"
LastRow = Sheets("All Data").Range("R65536").End(xlUp).Row
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Day 1'!R1C1:R" & LastRow & "C18").CreatePivotTable TableDestination:= _
"'Day 1 Tables'!R1C1", TableName:="Day 1 Service Alarms" _
, DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("Day 1 Service Alarms").AddFields RowFields:="Service"
With ActiveSheet.PivotTables("Day 1 Service Alarms").PivotFields("Service")
.Orientation = xlDataField
.Caption = "Number of Alarms"
End With..............................
View 3 Replies
View Related
Nov 17, 2007
I've been trying to bend my mind on how to accomplish this and haven't found a clear way to do this. I have to combine reports from a varying amount of one worksheet workbooks that are all formatted the same and have the same data format in the same cells. I was originally thinking about creating some quick code like
'for when I need to sum
activecell.formula = Workbook1.range(“b2”) + workbook2.range(“b2”) + workbook3.range(“b2”)
'and for when i need to create averages
activecell.formula = "=average(Workbook1.range(“b2”), workbook2.range(“b2”), workbook3.range(“b2”)
However, the amount of workbooks will change depending on which regional account I'm working on, so I have no 'set amounts' of sheets. it can be anywhere from 3 to 300 (figuratively speaking). Is there a way for me to create something that will be flexible enough to expand the amount of workbooks I need to consolidate into 1 final report that totals them together? or am I going about this totally wrong?
View 2 Replies
View Related
Aug 22, 2014
I want to make a range variable ("testrange") that I can use to define a range of non-continuous cells and then use that range variable to select that range of cells in different worksheets within the same workbook.
I tried the below code (simply coloring cells red), but get an error when I try to select "testrange" after moving to the next worksheet.
[Code] .....
The next worksheet is also supposed to have the same range of cells colored red, but I can only get it to work on the first active sheet.
I would like to avoid having to repeatedly rebuild the range I want selected each time I move to a different worksheet.
View 7 Replies
View Related
Mar 19, 2012
I have below code which I was thinking is very simple to select variable worksheets. However I get Debug error when running:
Code:
Dim rng As Range
Dim cl
Set rng = Sheets("Budgetted sku's").Range("A2:A24")
For Each cl In rng
Sheets(cl.Value).Select
Next cl
View 5 Replies
View Related
Oct 26, 2012
I am trying to return the SUM of a variable cell across multiple worksheets... This 3D formula works
=SUM('March 2012:December 2013'!E27)
I wanted the formula on the next row to ref F27 then G27 etc... (which I can't just drag down) so thought I'd try to "assemble" the formula I wanted using the INDIRECT function...
I tried this formula which doesn't seem to work...
=SUM(INDIRECT("'"&E6&":"&E27&"'!E27"))
NOTE: the script in the INDIRECT brackets returns the text string... 'March 2012:December 2013'!E27
Can INDIRECT not handle multisheet references?
View 9 Replies
View Related
Sep 5, 2006
This Sample is the code that I've adopted for my use.
My problem is this. My normal target .zip files are in the neighborhood of 75 - 80 Mb in size. Yes I know that they are huge..... (They are backup files for mainframe extracts ) Each zip file contains in the neighborhood of 2000 files. Currently, I have coded two ways of extracting data. Well really only 1.
1) I specify a location to extract all files. Which is time consuming ast it takes in the neighborhood of ( 5 min I thing for all files to get extracted ).
2) If I let the user specify a file filter parameter ( part of a file name ) while the WinZip shell is extracting files I'm searching all the file names in the dest directory and deleting the files that I don't want.
So what I want to know is it possible ( without buying software ) to be able to get the filenames of the files in a WinZip file.......
View 9 Replies
View Related
Mar 21, 2007
I have a ListBox in my worksheet "A", and I'm tryin to acces to it... I want to use that ListBox like a log to print the error i have found while running my code. The name of that ListBox is "IncongruenciesListBox".
now if that ListBox was in a userform, i can access to it easily, but if it is in the worksheet how can i change (add more rows) to it?
I tried:
Dim WS As WorkSheet
Set WS = ThisWorkBook.WorkSheets("A")
If added = False Then WS.IncongruenciesListBox.AddItem ("error")
And there is a message saying it cant find "IncongruenciesListBox".
View 6 Replies
View Related
Apr 6, 2007
Is the following valid code in Excel VBA?
If Application.WorksheetFunction.isblank( Cells(RowIndex, ColIndex)) Then
View 3 Replies
View Related
Apr 26, 2007
I would like to know if it's possible to access a worksheet in an Excel spreadsheet using a pre-prepared string or character-set? Usage is for years consecutive, so worksheets are '1990', '1991', '1992', etc.
In which case I'd like to be able access these with a prepared string '1990', instead of using the hard-coded method for hundreds of data accesses.
View 7 Replies
View Related
Jan 20, 2012
I've got some code which some one posted me a link to on here which set up a pivot table on more that 1 sheets (using excel 2002).
I inserted this code into a rountine I do daily which works fine.
The problem Im having is that Im getting more and more info which I need to pivot over more than one sheet. So I was going to amend the rountine so It picked what ever sheets are in the work book to pivot.
At the moment I have to go into the code and name the sheets to pivot. Is there some code which can work out how many sheets that need pivoting and just do it with out naming them? and also just add a new sheet with the pivot on rather than having to name the destination?
I've posted the code i got given below and have highlighted the bit where I have to name the sheets.
Code:
'---------------------------------------------------------------------------------------
' Procedure : CreateConnection
' Author : KL (Kirill Lapin)
' Date : 18/08/2009
[Code]....
View 8 Replies
View Related
Jan 10, 2010
I am currently accessing data from one worksheet to another using: =INDEX(AD!C:C, MATCH($B9,AD!$B:$B, 0)). Ie from my current worksheet ‘Claim’, I’m going specifically to sheet ‘AD’. My full workbook contains 18 sheets, one for each of 18 engineers and of which, the name of the worksheet is the engineers initials. What I would like to do, is to use a lookup table located on ‘Claim’ which will hold a list of all 18 Initials, so that when an engineer selects himself from a drop down list, his initials automatically populate cell K7.
My question therefore is, is there a way of substituting the ‘AD’ in the formula: =INDEX(AD!C:C, MATCH($B9,AD!$B:$B, 0)) with the contents of K7? Or, if you can see what Im trying to do here, is there any other way of accessing specific sheets by inserting the sheet name into a formula?
View 4 Replies
View Related
Apr 2, 2009
How does one access the properties of a shape? For instance getting the text on a button ( from the forms toolbar ) on a worksheet. This works
View 4 Replies
View Related
Nov 10, 2009
I've been teaching myself VBA for the purpose of processing some time resolved data from an instrument. I've come up against a problem and I just can't work out why the code is failing. (using excel 2007, VBA 6.5)
I declare some arrays as dynamic, then ReDim them to a user defined number (with Option Base 1) and input data from a spreadsheet into the array. I can then output the data into the spreadsheet again and the lbound and ubound functions return the correct values for the array. What I cannot do is actually access individual elements. If I specify array(i) I get the "Subscript is out of range" error. The code relevant to this problem is as below (simplified as there are more arrays):
View 5 Replies
View Related
Dec 14, 2006
I have designed a spreadsheet that updates automatically and shows the company's perfomance on several sheets.
To add to the use of the sheet I would like to offer the user a preference set up. So they choose the sheet it opens on for example.
I am happy with how to do all the above except for the fact that the sheet is required to be available to all staff! so only one employee can have it open in read/write everyone else will eb read only!
I was thinking the only way around this would be to have a second sheet that saves their environ user name and preferences.
That would require using the sheet closed though would it not?
View 9 Replies
View Related
May 25, 2007
i am trying to access buttons like ' Format - Column - Hide'
View 5 Replies
View Related
Jan 17, 2008
How can i access data from other workbook i have opened.?
I am new in VBA.So little code snippet would be good to show.
I want to get the cell data from workbook2 to workbook1
View 9 Replies
View Related
Jan 3, 2010
I have declared a number of string variables named "hlpYear1", "hlpYear2" etc in the 'Worksheet_SelectionChange' function. "hlpYear1" contains the appropriate text to display if the selected language is English, "hlpYear2" is for French and so on. I was hoping that I would be able to use VBA to display the appropriate text by way of a 'For..Next' statement along the following lines:
View 4 Replies
View Related
Feb 23, 2012
In VB when I select a worksheet, I can amend the (Name) of the worksheet in the properties window. There is also the .Name property which is the same as the sheet tag name.
I can use the (Name) in vb code to identify the sheet e.g.
ControlSheet.cells(1,1)
without having to define ControlSheet as a worksheet first.
How do I access the (Name) which appears at the top of the list of properties in the properties window.
View 1 Replies
View Related
Sep 4, 2013
Below is an example of how I prefer to work with a database. Results are fast; even across the network and I understand the syntax perfectly.
Code:
Sub DBPreferredWay()
folder = ThisWorkbook.Path & "mydatabase.mdb"
Set db = OpenDatabase(folder)
strSQL = "SELECT * FROM transactions WHERE [SimilarItems] = " & myFocus
Set tR = db.OpenRecordset(strSQL)
[Code] ........
I'm trying to access a different database; a database being hosted on my SQL server. Because of security and what not; it looks like I'm needing to use adodb but I can't stand it and the slowness (minutes to receive queries. IS there a way for me to access my sql server database similarly to how I'm accessing MDB tables across the network? Below is what I've been trying with the adodb
Code:
Sub SqltoACCPAC()
Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
phrase1 = "DRIVER=SQL Server;DATABASE=" & "VNODAT;SERVER=192.168.0.91,1433": conn.Open phrase1, "SA", "password"
strSQL = "SELECT * FROM ICITEM WHERE [SEGMENT1] >= 101 AND [SEGMENT1]
View 8 Replies
View Related
Mar 25, 2007
How is the data organised in frames in a web page accessed?
View 2 Replies
View Related
Nov 25, 2009
I have a custom data entry form which is working fine. The form completes customer data for reviewing at a later date. I have now been asked to change it so it will seperate the data in to customer's who require some documents sent out and some that dont.
Is it possible to have a combobox on the form and if the options on the combobox are Yes and No (original), when either option is selected the data will complete on to a seperate sheet for cases with documents required or documents not required?
View 4 Replies
View Related
Apr 2, 2014
Basically, i have a common workbook template that is used by multiple users across the business to request a cost for numerous new products.
Within the template, there is a common section at the top, where specific project information is entered. There is also a table beneath where 1 or many products can be entered, with specific information relating to that product in the same row.
All the submitted requests are uploaded via an email attachment, to a particular sharepoint directory.
What i would like to do in the master workbook is the following:-
1. Open in turn every uploaded workbook within the sharepoint directory and copy the following cells into the master workbook, each in it's own row (or next available), with the data in adjacent cells.... 1st cell to enter data is $B6.
Cells to copy from each sheet:
Common info contained within cells:
$DG$2,$N$11,$N$12,$N$19,$N$13,$AO$7,$AO$8,$AO$9,$AO$10,$AO$11,$AO$12,$AO$12,$AO$13,$AO$14,$BO$8,$BO$11,$BO$14
Product specific info: $U37, $AD37, $AH37, $DH37, $C37, $O37
Depending on the number of products requested, we need to repeat (loop?) until it finds the next blank row in the table. I have hidden a blank row in the table, so there will always be one!
All of the common information needs to be included for each product specific entry.
For each file, once the upload has been completed, i would like the file to be moved to another "archive" directory.
I have attached the template for information. The master workbook is still in development so can't share currently.
View 12 Replies
View Related
Jan 22, 2014
I followed the instruction in the TechRepublic article "10 steps to creating a scrolling Excel chart"; [URL].... The problem is that the scroll bar stops working at a certain point and will not bring up the last 8 months of year 4 of my data. I get error messages and the chart turns blank.
I also tried a different method (again for a scroll bar and a rolling chart) as outlined in [URL].... There are a number of similar articles with almost identical formulas. Here, the first 8 month of year one are inaccessible = error messages and chart turns blank.
I have 4 years worth of temperature data. The original data table from where the Index function draws the data is 1463 rows long (minus 2 rows for the header). The "magic" number where the scroll bar stops working is 995 for the current value. From a current value of 994 onwards, the entire matrix displays error messages "Ref#".
In the second case, the magic number is 1242 which equates to August of the first year. 1241 and lower for the current value will freeze the chart.
How the scroll bar control generates and gets hung up on either of these two magic numbers is a mystery to me.
I set the control properties at the beginning, i.e. Jan 1 of the first year to: Current value =1, Minimum =1 (I also tried 0), Maximum = 1500, Incremental =5, page =30, linked cell =$E$2. What is most puzzling to me is the fact that the scroll bar does work but only for part of the data set.
View 1 Replies
View Related
May 31, 2014
Is it possible to have my Excel cash flow sheet access my bank checking account and automatically update with my current balance?
View 1 Replies
View Related
Oct 5, 2013
First I have a excel with a selection of customers:
Column A = customer id
Column B = customer name
...
this excel contains about 500 premium customers
Secondly, on an external server I have a database with the details (adress, phonenumber, ...) of about 100.000 customers
Now I would like to display the details from the database into the existing excel. For that I was about to write a sql-query to copy the data I need to have into the excel (sheet2) to access it from sheet 1 with vlookup (based on the customer id).
But the large amount of data makes excel to work very slowly and it is impossible to keep on working like that. So now I was looking for a way to create a selective sql-statement.
Something like this:
select details from db
where customerid in (all the customer id's mentioned in sheet 1)
without writing all customer id's manually
View 2 Replies
View Related
Jan 10, 2008
I am trying to fill out my yahoo email's 'Compose email' Form using VBA. I have a workbook with a list of email addresses and text messages to be send. I am primarily using getElementsByTagName(), and able to open a new yahoo page, log in, create a new email, populate the addressfields,and subject field.
However, I am not able to write anything in the message editor ( the part where we write the actual message). The following is a part of the HTML code related to the message editor. Please suggest what I should do?
View 9 Replies
View Related
May 28, 2014
I am currently looking for a front end program to access multiple excel files at once. In a nutshell, I work for a company that uses subcontractors. Each subcontractor is graded in about 7 different performance categories. I receive daily excel files that give us the results for each category. I get separate files for each category.
What I do right now is use a master excel file. Every day I take each file I receive from our parent company and add it to the master file. I have a huge excel file that has a separate tab for each performance category. Then I have a "Report" tab where I use various formulas to summarize a sub's performance over a certain date range.
Essentially, the report tab is just a neat and concise way to display the stats of an individual sub, an office, a region or the whole company. I use tons of VLOOKUP and COUNTIF(S) formulas to look over the hundreds of lines of data and bring back the stats. It works but it is just not very efficient as I get hundreds of new lines of data every day.
View 4 Replies
View Related
