Get Names Of All Worksheets In One Worksheet Without VBScript

Jun 18, 2012

Basically I want to be able to get the names of all sheets in a workbook. I know how to get Excel to print the name of the sheet in a cell once the file is saved, and I know how to hide sheets to make sure that all sheets are referenced in a range. If the name of the worksheet is in the same cell across all sheets (except the ones I don't want included on the totals page), is there a way to call that range and have Excel print off all the worksheet names in different rows or columns via a fill function?

Difficulties: the user may add/delete, rename, and have any number of, worksheets in the workbook. Because these variables are unpredictable, I am having difficulty figuring out the way to do this. Also, the formula should be able to ignore the sheets which have no value in the expected cell.

View 5 Replies


Excel 2013 :: Can't Unprotect Worksheet Created With VBScript

Aug 2, 2014

I've written a little VBScript that generates several hundred Excel 2013 worksheets. In order to protect the users from themselves I protected certain cells. Everything works as expected, except when I discovered that the password I used to protect the worksheet doesn't work to unprotect it. If it matters, I'm not trying to unprotect programmatically, just opening Excel and going that route.

Here's a snippet of my code:

objWS.protect Password="abc123"

View 2 Replies View Related

Excel 2010 :: Create Master List Worksheet From Lists Of Names On Several Worksheets

Jun 6, 2013

I'm using Excel 2010. I have a workbook that has six or seven worksheets in it. Each worksheet has a header row, and then lists of clients at the clinic where I work. The columns are last name, first name, medicaid #, social security #, DOB. We use these lists every month to verify that each client has Medicaid for the month. Therefore, there are also columns with names like April, May, June where we mark yes or no for each client. Some of the worksheets also have information about the clients' guardian, phone number, etc., but not every worksheet has those. (I can't show you the worksheets b/c of federal privacy laws, of course.)

I really, really want a worksheet that's a master list of the data from each of the existing worksheets, and I'd like to keep it synced to the source worksheets. I'd love it if I could also add a column telling me which of the source worksheets the data originally came from.

I've tried a few things to make it happen, but nothing has worked.

I've tried using the Consolidate function built into Excel as well, but that only works with numbers, not text, and it wants to manipulate the numbers instead of just copying them over.

I've found several macros online that should have worked (including one from this site) but that I wasn't able to install to my worksheet. I followed all the steps for installation, and throught it worked, but when I went back to the Excel file to run the macro, the list of available macros remained emptpy.

Is it possible that corporate IT has disabled the ability to install macros? Is there somewhere in Options were I need to go to turn Macros on?

View 5 Replies View Related

Exporting Data To CSV VBScript

May 15, 2012

I'm having some trouble with a little VBScript I have put together to export data into a CSV file.

The VBScript goes through each individual Row in excel and basically exports it to a CSV file in the format I need.


Dim JobHeader As JobHeader
Dim JobDetails As JobDetails
Dim FileNameStr As String
Dim RowStart As Integer
Dim MsgResponse As Integer
Option Explicit



Close #1
End Sub
Sub ProcessJobs()
JobDetails.JDShipName = Cells(RowStart, 3).Value 'Col 3
JobDetails.JDStreet = Cells(RowStart, 5).Value 'Col 5
JobDetails.JDBlock = Cells(RowStart, 6).Value 'Col 6


This is working perfectly however there can be multiple lines of different product for the same Order Number and I need to consolidate this into one single line where the JDItemDescription, JDQuantity and JDWeight are updated with the combined data.

I have attached a copy of the Spreadsheet so hopefully you can understand what I am after. [URL]...

View 3 Replies View Related

Excel 2007 :: Automatically Change The Worksheet Tab Names With Cell Value In Each Worksheet?

Feb 14, 2012

I am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?

View 3 Replies View Related

VBSCRIPT To Read / Update And Save?

Apr 2, 2012

All I am trying to do, is read an Excel file looking for the next cell to update, then update it and ultimately save it.

I cannot get it to work - all the checking says it should and yet it doesn't. I have included my simple code but not being a programmer, I just need this to work. The whole Excel VBSCRIPT is a mystery to me as to when methods etc can be used.

here comes the code

Set objXL = CreateObject("excel.application")
Set wbDest = objXL.Workbooks.Open(pathName & xlFile)
Set wksDest = wbDest.Worksheets("SpeedLog")
xlFindColCount = 1
Do Until wksDest.Cells(1, xlFindColCount).value = ""
xlFindColCount = xlFindColCount + 1


View 6 Replies View Related

SaveAs VBscript File Path

May 10, 2008

I'm using a VBscript that will copy a worksheet and it works perfectly except that it saves the copy to the same directory as the original workbook. I need it to save to a different directory.

What I believe is the pertinent part of the code that needs adjusting is:

"Input box to name new file
NewName = InputBox("Please Specify the name of your new workbook", "New Copy")

' Save it with the NewName and in the same directory as original
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "" & NewName & ".xls"
ActiveWorkbook.Close SaveChanges:=False "

How would this be changed to specify that "NewName.xls" be saved to: D:/newdirectory/copies

View 9 Replies View Related

VBScript Error Run-Time 1004

Jun 30, 2009

I have been working on the following worksheet, which has A3-Z27 cells right now and will grow/shrink with time. The spreadsheet shown below is just a portion of the entire worksheet. My vbscript is below the worksheet. I have two worksheets PL Dbase and Waiting List. I am copying columns B thru G from Waiting List if Column J is = to "Yes". To the next available row in worksheet PL Dbase columns B thru G...These columns have the same headings on both worksheets. I am getting the following error, when I click the button I assigned the macro to: Run-Time Error 1004 Copy Method of Range Class Failed.

Waiting List .....

View 11 Replies View Related

Vbscript To Creates A Workbook In An Existing Spreadsheet

Dec 28, 2007

I have a vbscript that creates a workbook in an existing spreadsheet. I would like to have the script create the spreadsheet if it does not exist. That part is easy enough. The part I don't know how to do is to have the macro that has to run on the sheet created by the script.

View 9 Replies View Related

VBScript That Pulls SNMP Data From Some Devices

Nov 21, 2008

I have a VBScript that pulls SNMP data from some devices. It uses two external EXE's called "SNMPGET.EXE" & "SNMPWALK.EXE". When the script runs, it pulls specific information from our SNMP devices (in this case, I am pulling temperature values from Netboz Devices). The script looks like this :

View 2 Replies View Related

Combining Names From Two Different Worksheets?

Jan 21, 2014

I have two worksheets...let's call them worksheet A and worksheet B. How can I get a list of people who are on worksheet A AND also on worksheet B? I would actually like those names on a separate worksheet.

View 6 Replies View Related

Run Code On Worksheets With Certain Tab Names

Aug 11, 2007

My Thread title should have read covert code TO run on all worksheets with same word in Name. I have had a look at some other pieces of code in this forum but they are quite the same as I want to do....I have tried a few things but each time I get an error for sure I am not modifying the code quite right, I ahve been missing something for what I now want it to do. So I wonder if someone could Please review these two pieces of code...they of course are both for running on just one worksheet in a wookbook.

I would like them both be able to run, still as tow separete pieces of code as they are, on all and only worksheets that have in their worksheet name a key word in this case the word "Region" and that those worksheets all in a single workbook but the workbook does have other worksheets in it I dont want the code to run one on - but those worksheets do not have in their worksheet name the word "Region".

Sub test()
Dim r As Range, txt As String, ws1 As Worksheet, i As Long
Set ws1 = Sheets(1)
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For i = 16 To 21
For Each r In ws1.Range(ws1.Cells(6, i), ws1.Cells(Rows.Count, i).End(xlUp))
If r.Value <> "" Then
If Not .exists(r.Value) Then
.Add r.Value, Nothing
txt = txt & "," & r.Address(0, 0)
If Len(txt) > 245 Then
ws1.Range(Mid$(txt, 2)).EntireRow.Delete
txt = "": .RemoveAll: Goto Again
End If .............

View 9 Replies View Related

VBScript To Call Excel Macro - Loss Of Tab Functionality?

Aug 29, 2013

I am trying to open an Excel Workbook which in turn will automatically run a macro on loading which is a Userform. The Userform will contain 3 boxes, just some simple text boxes.

I don't want the Excel Workbook to show in the background, so I have opted to go for a VBScript to call the Excel Application and open the spreadsheet...

I have a current .vbs script which is as follows...

Option Explicit
On Error Resume Next


This works fine, there is no workbook showing and the userform opens as expected. The problem is, I lose the functionality to tab between the boxes. When opening the Userform directly in Excel,

View 1 Replies View Related

Loop Through Some Of Worksheets Without Using Customized Names

Nov 20, 2012

I have a piece of code from a form command button. its supposed to colect data and put it to appropriate cells.

FoundColumn = 0

For Each c In Sheet19.Range("A5:A33").Cells
If c.Value = student1.Value Then
'check if date allready exists
For Each cc In Sheet19.Range("c4:nc4").Cells
If Calendar1.Value = cc.Value Then

[Code] .....

I need this for Sheet19 and to 10 more worksheets like Sheet20, Sheet21 etc. I can manually copy paste the code and change the Sheet19 to whatever but it just does not seem the right thing to do. I tried:

For i = 1 To 6
naming = "Sheet" & i
MsgBox naming.Cells(1, 2).Value
Next i

but that gives an object required error.

View 2 Replies View Related

Consolitate Names And Address From Many Worksheets In To One

Apr 19, 2009

I need to create a macro that will create a mailing list from about 300 spread sheets in the same workbook. (I am proud of myself, I found a macro that helped me bring all 300 worksheets in to one workbook).

All worksheets are template style if B3 is company name in one worksheet it it the same on all worksheets. I have 15 cells I need to bring from each spread sheet into a master mailing list.

The big problem is, I only have the next 18 hours to do this and it is taking way too long to do it one at a time.

View 14 Replies View Related

Changing File Names Within Worksheets

Mar 25, 2009

my formula is as follows: =(VLOOKUP(A7,'[Monthly AR Analysis - Feb09.xls]AR - Current'!$A$1:$I$153,8,FALSE)). I will be using this for formula on about 15 different worksheets ... my issue is the file name changes every month and currently I have to go in and do a find and replace, I would like to have one main page where I could change the file name month and it will change all the rest.

ie: Monthly AR Analysis - Feb09.xls next month changes to Monthly AR Analysis - Mar09.xls .... So basically each month I want it to pick up the new file that has been saved with the new name. So what I would want is to be able to have a cell that I could change the Feb09 to Mar09 and then it would do the same to all the worksheets. I attempted to use CONCATENATE to change file name and it worked but will not work within the Vlookup.

View 4 Replies View Related

Cross Referencing Names On Different Worksheets

Apr 23, 2009

I have three different sheets, each with a column of names, titled "list1, list2, list3." I also have a fourth list of names on a sheet titled "masterlist." I am wondering if there is any way I could cross check the three lists against the master list to see if any names appear on the master list but do not appear on any of the three lists. Is there any way I can cross reference the master list with each of these lists?

View 2 Replies View Related

Adding Names To Worksheets During Merging?

Oct 31, 2013

I have an output in the form of multiple worksheets. I have found a solution to merge them all toghether into one workbook. But I would like the sheets to be named after the original file name.

Lets say my file is called 9252400.xlsx, i would then like the corresponding sheet in the workbook to be called 9252400

The code I am using for merging is:

Sub GetSheets()
Path = "C:Documents and SettingsDAJOMy DocumentsMD-CPHPI_Database-908315-3PlantInfo_Excel"


View 1 Replies View Related

Creating New Worksheets From A List Of Names

Oct 11, 2006

debugging issues without changing the code dramatically. I think I've basically "got it" but there are a few lines that I think are problematic. I've written notes to highlight my thinking.

Let me give a basic discription of what I'm trying to do: This file lists where a company has offices. I'm want my subroutine to 1) Sort the cities in alphabetical order (this occurs at the end of the code), 2) for each of the cities listed in "AllCities" worksheet, check whether there is a additional corresponding worksheet of the same city name, and if there isn't one, the subroutine would automatically add it, and 3) delete any worksheet of a city name that is NOT found in the listings found in the "AllCities" worksheet.

View 8 Replies View Related

Match Names In Two Worksheets And Get Data

Jun 21, 2007

I have two sheets of same names with different data in each sheet. I need to refer the name in the first sheet and match the same name in the second sheet and retrieve the start date. I have attached the work sheet sample for better understanding. I know I can use vlookup. But am not getting the results I need.

View 9 Replies View Related

Renaming Worksheet Names From Data In Worksheet

Mar 26, 2007

I use a web query to import into an excel workbook. As data is changed on the web that the query runs against, I would like the name of the sheet that is being imported into to change to one of the cells that is being imported.

View 9 Replies View Related

Matching Names On Different Worksheets With Function To Add Abbreviations

Jan 12, 2010

writing a macro script. I want to find matching names on two diffferent worksheets. More specifically I need the ability to match if I apply a abbreviation(s) to worksheet one in order to get matches in worksheet two.

e.g Company = Co. or
Insurance = Ins

Please see my attached excel document for an example.

I have over 27000 lines so doing this manually will take forever!

Ideally I would like this to be a macro script that I can edit to add as many different abbreviations as required. The attached document will make my task a lot clear.

Unfortunately I don't have any starting code as I am not an excel/visual basic programmer, But i hope somebody will be able to help me out here.

The reason I need to do it this way is because I have been given a file from a partner company that uses different abbreviations to the ones we use in my company. I want to see if the companies in the list I have been given already exist in out company database, therefore we need a suitable matching procedure.

View 10 Replies View Related

VBA Code- To Pull All The Names Of The Worksheets In A Workbook

Apr 5, 2007

I just started playing with creating my own macros. I've written a macro to pull all the names of the worksheets in a workbook, now what I want to do is write a piece of code that will pull data from a cell in those worksheets.

So for instance I have all my worksheet names in cell A1 to A10, I want to pull the data from cell E1 from every worksheet in A1 to A10. Can someone help me with a sample code to work with. I'm still reading Power Progamming haven't gotten so far yet.

View 9 Replies View Related

Create & Name Worksheets Based On Names From Table

Mar 28, 2008

I'm trying to create a macro which will run through every salesperson in a list, and copy and paste their product and client data (from separate sheets) to a template sheet. The template sheet will then by copied to the Salesgroup workbook, and renamed to the salesperson's name. Then the macro should go back to the original workbook and do it all again for the next salesperson, putting them into the same workbook.

The problem I'm having is that the references to the Template worksheet seem to be being messed up by copying the template sheet to the new workbook, and it then ends up creating all the worksheets, but it stops taking the data from the data sheets, and ends up copying the previous worksheet and pasting that into the new copied template, which means that I get the template multiple times across the same sheet.

Sub SalesSheets()
Dim Salesbook As Workbook
Dim NewBook As Workbook
Dim SalesDataSheet As Worksheet
Dim ClientDataSheet As Worksheet
Dim SalespersonListSheet As Worksheet
Dim Template As Worksheet
Dim rRange As Range
Dim rCell As Range
Dim strText As String
Dim SalesGroup As String
Application.DisplayAlerts = False
Application. ScreenUpdating = False
Set Salesbook = Application.Workbooks("2008 Salesperson TEST.xls") 'change filename here
Set Template = Salesbook.Worksheets("Salesperson Template")
Set SalesDataSheet = Salesbook.Worksheets("SP product YoY")
Set ClientDataSheet = Salesbook.Worksheets("Client YoY")
Set SalespersonListSheet = Salesbook.Worksheets("Salesperson List")..........................

View 3 Replies View Related

Automatically Create New Worksheets Based Upon List Of Names

Aug 10, 2014

I have a list of names in column A, and want to automatically create worksheets within this same workbook based on the list.

List of values:
Dairy NI
Dairy SI
Sheep and Beef
Sports Turf
Water Industry
Horticulture Services
Production Horticulture
Other Agriculture

Therefore, a macro or coding to automatically create 9 spreadsheets named as per this list.... My list is approximately 50+ hence inquiring if there is an easier way!

View 1 Replies View Related

Simple UserForm - Take Names Of Worksheets And Populate Listbox

Nov 28, 2011

So I am supposed to take the names of worksheets and populate a listbox with them, simple enough as I had it running, but now it isn't working. Here is my code:

Private Sub UserForm1_Initialize()
Dim ws As Worksheet
i = 1
For Each ws In Worksheets
ListBox.AddItem (ActiveSheet.Name)
i = i + 1
Next ws

[Code] .......

Why it isn't populating the listbox anymore? There arent any errors just a blank box.

View 9 Replies View Related

List Names In Alphabetical Order From Multiple Worksheets

Mar 3, 2007

I'm trying to get this formula to work across multiple worksheets. It suppose to list names in alphabetical order. I'm using these define names to get it to work. Also may I have another formula that list unique names in alphabetical order?


=IF(ROWS($E$2:E2)<= COUNTIF(Col_A,"?*"),INDEX(Col_A,MATCH(LARGE(COUNTIF(Col_A,">"&Col_A),ROWS($E$2:E2))=S+1,COUNTIF(Col_A,">"&Col_A),0)),"")

View 9 Replies View Related

Macro To Batch Copy And Save Worksheets With File Names Listed In Cells?

Oct 23, 2013

I have a workbook that contains worksheets. They are listed as follows:


In cells A1 - A49 I have text. What I would like to do is to have a macro that I can run that will basically copy and save new workbooks with sheets A - G copied over and have the new workbook saved with the file name that I have denoted in cells A1 - A49 on Sheet 1. Also, the macro would ask me where I want to save the new Workbooks.

For example, if this were Sheet 1, Column A then the cells below would be the saved name of the new workbooks and the new workbooks would have Sheets A - G in themRed










One more piece of information, the file that is being copied and saved is large (~80MB). If there is a macro that would allow me to simply "save as' the workbook and the Saved Workbooks would be named using the data in Sheet 1, that would work as well in case copying, pasting, then saving may take more time

View 1 Replies View Related

Worksheet Tab Names

Dec 23, 2008

Is there a way of linking the worksheet tab names to a cell in the spreadsheet

View 9 Replies View Related

Using Names On The Worksheet In Cell A1?

May 7, 2013

there is a named range on the worksheet in cell A1- "GiGS"

I want to type "GIGS" in 1st cell and then in 2nd one to write formula "=A1*5". So excel need to understand that in A1 there is a name and not just a string

View 3 Replies View Related

Copyrights 2005-15, All rights reserved