Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Auto Renaming Of Sheets In A Workbook

I am fairly new to VB, and have come across a problem.

I need a way to rename a set of worksheets based on a list within the same workbook.

any help would be great.

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Renaming Excel Sheets

I have recorded a macro to create a new sheet, basically by copying an existing sheet.

I can then rename it, but what I can't work out how to do is to rename the sheet using a cell value as part of the name.


Current name - Sheet 2

New Name - Form (value of Cell A1)

I need to be able to do this as I will be creating lots of new sheets, and I don't want them to have the name Sheet 2 (2) etc.

Can anyone help?


Help With Macro In Excel (Renaming Sheets)
I have sheets in these formats (XXX-Alpha characters, 222-Number Characters)

2222 - 2 XXXXX XXX
2222 XXXX

I need to rename all the sheets in the workbook so that only the numeric characters are left. If a sheet does not have numeric characters to begin with, I should leave it alone.

2222 - 2

So far my macro doesn't seem to be working. Can anyone help?

Sub Rename()

For i = 1 To ActiveWorkbook.Worksheets.Count
x = 1
For x = 1 To Len(ActiveWorkbook.Worksheets.Item(i).Name)
temp = Mid$(ActiveWorkbook.Worksheets.Item(i).Name, x, 1)
s1 = InStr(1, alphabet, temp)
If s1 <> 0 Then
ActiveWorkbook.Worksheets.Item(i).Name = Mid$(ActiveWorkbook.Worksheets.Item(i).Name, 1, s1 - 1)
Exit For
End If
Next x
Next i

End Sub

Help For Duplicating And Renaming Excel Sheets
I wanted to write the code for copying a template sheet many times (variable number) and assign specific names to each sheet - the names must be the ones listed in a range in another sheet.

Somebody helped me with the following code, but when I have more than 24 rows in the range, error 1004 occurs (

At - they explain this tipe of error, but i did not manage to make new code based on both sources - i do not know how to work with arrays and loops>

What can I do?
Thousands Thanks!

Gilbert Ciobanu

Entry Cell In Workbook Will Auto Add To Another Workbook
I have 2 files (order confirmation & order summary). When I key-in data in order confirmation, there will add the data into order summary when click the confirm button. I don't know how to write the VBA code. Can you give me some suggestion ???

Sub addto()
Dim L As Long
For L = 1 To Range("A65536").End(xlUp).Row
If Cells(L, 1) = "" Then
Cells(L, 1).Value ='[order confirmation.xls]Order Confirmations (2)'!$AJ$3:$BD$3

End If
Next L
End Sub

Looping Through All Sheets In A Workbook

is there a better way to loop though all the sheets in a workbook apart from doing some thing like this

Sub Play()
Dim i As Integer
Dim x As Integer
Dim wk As Workbook
Set wk = ActiveWorkbook
i = wk.Sheets.Count
x = 0
x = x + 1
Loop Until x = i
End Sub

I am thinking along the lines of something like For Each then Next
like you can do with tables in Access..

Just wondering
As the above works fine


Stepping Through Sheets In A Workbook
Sub newonoe()
Dim x As Integer, y As Integer
x = 0
For y = 1 To ActiveWorkbook.Sheets.Count
While (ActiveWorkbook.Sheet(y).Cells(x, 3) <> "")
ActiveWorkbook.Sheets(y).Cells(x, 5) = "=c" & x & "*40"
x = x + 1
End Sub

That's my code. I get an object doesn't support this property or method error. I'm trying to change one column (column 5) on a bunch of sheets in a book.

Excel Workbook With No Sheets
I have a simple question about creating Workbooks in Visual Basic.

First of all is there any way to use the Excel.Application object to create a sheetless workbook? If not is there a way to delete sheets from the workbook without getting the message "You are about to delete a sheet...etc"

Dim excel_app As Excel.Application
Set excel_app = CreateObject("Excel.Application")

I sometimes only need the workbook to have one sheet and the previous code makes a workbook with 3 sheets. Even if I can make a 1 sheet Workbook, I can work around it.

P.S. the code i use to delete the sheets is this:

excel_app.Sheets("Sheet1").Delete ' These delete all 3 sheets but prompt the user asking them if they want to

Naming Sheets In A Workbook
If there an easy way to rename the Worksheets in a Workbook based on the contents of a cell value. I want to rename the Worksheets based on the Text that is contained in a cell. I.E. If Cell A2 = "Joe" then I want to name the sheet Joe. I will need to do this for all the Worksheets in the Workbook but the Value will always be in the same cell on each Worksheet. At times there can be 4 Worksheets or 10.

Please provide as much detail as you can.
Thanks for your help in advance.


Changing Between Workbook Sheets
Hi, I have a workbook with 10 sheets and I need to write to Cells (2, 2) of every sheet (1-10) before going back to sheet1 and write on Cell (2, 3) and so forth (it repeats).
How can I use a loop to increase the number of sheets? Right now sheets is like "Sheet1" "Sheet2"...How can I modify that.
Below is what I have so far

oXLApp.ActiveWorkbook.Sheets("Sheet1").Cells(2, 2).Value = Digi(2, 1)
oXLApp.ActiveWorkbook.Sheets("Sheet2").Cells(2, 2).Value = Digi(2, 1)
oXLApp.ActiveWorkbook.Sheets("Sheet3").Cells(2, 2).Value = Digi(2, 1)


Excel From Outlook -Sheets In A Workbook
Accessing an Excel file from Outlook. How can I say the following. I just need to save each worksheet by its self so I need a loop for each worksheet in the current workbook.

Dim ObExcel As Excel.Application
Dim ObWB As Excel.Workbook
Dim ObWS1 As Excel.Worksheet

For Each Worksheet In Workbook


Is It Possible?? {view Two Sheets In Workbook At The Same Time}

Has anyone tried to get two sheets from the same workbook on screen at the same time, sort of like a vertical split between them.


Copy Sheets To New Workbook W/o Code
I have seen and know of a few different ways to copy 2 sheets summary and quote to a new workbook e.g. Sheets(Array("Summary", "Quotation")).Copy

However with this example if I do not know the name how can I save it? There is another small problem her in that I do not want the macros being transfered either. However there is formatting done to these two sheets so the copy value option does not seem viable.

I had one idea where I save the current file as another and strip out all the VBA and Macros and unwanted sheets, is this possible?



Select Sheets In Workbook Using Combobox
i have used the combobox facility in excel before - but never really assigned any kind of code to it.

I want to have a combobox in a worksheet which will have the names of the sheets currenlty in the workbook. Therefore allowing the user to easily navigate between the sheets.

Could someone give me some direction - I have been pulling my hair out!!!!!!

Loading From Multiple Sheets On Another Workbook
I'm trying to extend an existing function I wrote to use different sheets on a different workbook, instead of the active workbook.

The function below did work when using 'local' sheets. But I want to load all my data into a another xls file and load/read from it as and when required. I use multiple sheets a lot and don't want to have to add the same sheets to each of my workbooks each time i want to use the data.

It had now stopped working. It works for a split-second if I manually load the test.xls file, but after a sec it refreshs and returns error values.

Also, as you can see, I'm trying to get it to return "n/a" if it can't find a matching set of values, instead of #VALUE!.

Any ideas anyone?

Public Function lookupref(ref As String, dateref As Date, season As String, reqdcol As String)

'Load and activate correct season sheet
Dim wbTest As Workbook
tmpfile = "c: est.xls"
Set wbTest = Workbooks.Open(Filename:=tmpfile)

'Get size of dataset
lastrow = ActiveWorkbook.Worksheets(season).Cells(2, "A").Value

'Loop data
lookupref = "n/a"
For a = 3 To (Int(lastrow) + 2)
tmpflt1 = UCase(ref)
tmpflt2 = UCase(ActiveWorkbook.Worksheets(season).Cells(a, "B").Value)
tmpdate1 = dateref
tmpdate2 = DateValue(ActiveWorkbook.Worksheets(season).Cells(a, "A").Value)
'MsgBox tmpflt1 & " " & tmpflt2 & " " & tmpdate1 & " " & tmpdate2
If (tmpflt1 = tmpflt2) Then
If (tmpdate1 = tmpdate2) Then
'MsgBox "Match"
lookupref = ActiveWorkbook.Worksheets(season).Cells(a, reqdcol).Value
Exit Function
lookupref = "n/a"
End If
lookupref = "n/a"
End If
Next a

End Function

Using Multiple Excel Sheets Within One Workbook
I am doing a program that gathers information from different cells in Excel, and inputs them as different variables.

Right now, I've got a scarce understanding of how to use excel with, and although I've got my program working properly, I havent been able to figure out how to tell it to get Cell x,y from sheet a, and then get cell x,y from sheet b.

If anyone could help me out here, by either an explanation of how this should work, or even a sample code that makes this work I can probably take it from there.

I'd appreciate any help I can get. Thanks everyone!

Export All Sheets In Workbook To Pdf Document Through Vb6 Or Vba

I am having 10 sheets in my workbook. Here the user convert each sheet in to a Pdf. (i.e) he save the First sheet to 1.pdf and in the header he will place "Page 1" and second to 2.pdf and in the header he put "Page 2" and so on upto 10.pdf. Finally he combine all the 10 pdf and make it in to a single pdf document which contains 10 pages and each page contains its page number as the heading.

In my machine i have Acrobat Reader 7.0 installed, kindly please tell me the way to do this job in VB6 or VBA coding.


Refer To Other Excel Workbook Sheets

I'm writting a vba function that searches for a specific value in a specific sheet within a certain excel file.

Currently I'm using this:

Application.Sheets("Data").Cells(1, 2).value = strSearchValue

Now this works fine, but I want that it searches for the value in another Excel file. So something like this:

Application("C: mpcolumns.xls").Sheets("Data").Cells(1, 2).value = strSearchValue

Off course this code is wrong but I have really no idea how I should do this. Do I have to make a new excel object or can I directly refer to sheets in other Excel workbooks? And how should my code look like. I hope somebody can help me. Thanks!

Selecting Between Sheets In A Excel Workbook With
Hi there again,

Can anyone help I need to select between 2 difrent sheets in a open excel workbook.

I open the spreadsheet from a location on my hdu and then need the second sheet to be active in this spread sheet, how do i do this



Edited by - kalahariferrari on 2/23/2005 11:55:41 PM

Export All Sheets In Workbook To Pdf Document Through Vb6 Or Vba

I am having 10 sheets in my workbook. Here the user convert each sheet in to a Pdf. (i.e) he save the First sheet to 1.pdf and in the header he will place "Page 1" and second to 2.pdf and in the header he put "Page 2" and so on upto 10.pdf. Finally he combine all the 10 pdf and make it in to a single pdf document which contains 10 pages and each page contains its page number as the heading.

In my machine i have Acrobat Reader 7.0 installed, kindly please tell me the way to do this job in VB6 or VBA coding.


Saving Or Exporting Some Sheets From My Workbook
I've got a workbook with many sheets and I'd like to create a button that when pressed, saves (or exports) some sheets of my workbook to a new workbook.

I need that because the user of the workbook needs to send only some sheets to the customer (there are some sheets that cannot be sent) and it would be nice if he had a button that did that for him. (he is not able to do it manually because the workbook is protected).

Receiving Names Of Sheets In Excel Workbook?
Hey everyone,
I'm trying to read in the names of the sheets in a excel workbook. I have no problem opening and reading the excel sheets once I know their name. I'm just wondering if their is a function that returns the name of a sheet or a list of the sheets names in an excel file. I assume it would be workbook.???

Named Ranges After Copying Sheets From Another Workbook
I may be going about this in a very non-conventional way, but I'm having problems doing some data input/output in Excel.

The overall goal of the program will be to function as a customer worksheet generator / viewer. The data from the users gets stored in a series of named cells on many different worksheets. When the user hits the save button, I have the program create a new workbook and copy all of the "raw data" sheets over to the new workbook.

When a user goes to open a customer's info, the program finds the appropriate "raw data" workbook and does two things:
1. It deletes the blank sheet from the application
2. It copies the populated ("raw data") sheet into the application's workbook so it has the same name as the original copy.

After step 2, however, none of my named ranges work. When I refer to one in my code (i.e. range("cellACRO").value ) I get a "Method Range of Object _global" error. If I select the sheet that contains the named range first, however, the program works fine.

Does anyone have any thoughts on this?


Looping Through Range Of Sheets In Excel Workbook
Is it possible to loop with VB through a range of sheets in an Excel workbook where there are numerous sheets but don't want to apply to all?

I've seen code to loop through all sheets in a workbook, but not a select number.

For example - loop through sheets 100-199 and copy data to another summary sheet, where there are also sheets 1-99 in workbook that should not be included.

Summarize Cells In A Workbook That Has Various Amount Of Sheets
Hello VB experts

I am managing projects and in order to succesfully manage the project costs I have made a worksheet that I send to all the project partners so i can keep up with the hours worked on the project. It kind of looks like the thingie below:

A..............B...................C...........D..........E........... F............G
1.....................................Task1....Task2......Task3.....Ta sk4..
2 Total hours spend on task:51..........45.........47.........41......... h/d
3 .....................................................................
4 1/11/2003.....................4@........8@........8@........5@........25
5 2/11/2003.....................4@........4@........5@........4@........25
6 3/11/2003.....................6@........6@........5@........5@........22
7 4/11/2003.....................9@........4@........4@........2@........19
8 5/11/2003.....................9@........5@........2@........5@........21
9 6/11/2003.....................5@........2@........8@........8@........23
10 7/11/2003.....................2@........4@........5@........8@........19
11 8/11/2003.....................4@........8@........4@........2@........18
12 9/11/2003.....................8@........4@........6@........2@........20
13 etc

The partners will receive an empty sheet and they select the month in a selection box and fill in the fields marked with an @. They will send me the sheets back and I will import the sheets in a new workbook with one sheet using a VB command.

Now the problem comes: I want to summarize the total hours in the first worksheet. The first worksheet has the same structure as the sended ones only with empty fields. Now I want to summarize the total of all the cells in worksheet 1. So cell C2 in worksheet 1 will summarize the values of cell C2 of all the remaining sheets. The same for C4, G10 etcetera. An extra difficulty is that the amount of sheets will differ from project to project so I can't make a formula with the exact sheet names in it.

I hope someone can help me with my problem

Kind regards


Exporting Sheets Into Text File Or New Workbook??
Hey guys... i have a user form setup and when the user is done inputing data there are two sheets which are left open in the workbook. How can I send sheet1 to be exported and saved as a txt file(sheet 1 only has info in column a so it would look fine in a text document), and I want sheet 2 to be saved as a new excel file. THANKS!!

Adding/Renaming/Deleting Worksheet, Saving Workbook.
OK, crying uncle.

I've never really done much more than play with controls before not and am I'm having touble with some of the basic peripheral Excel functions. I'm trying to write a little code that will do the following:

1)Open an existing workbook from an arbitray location using the CommonDialog .
2)Delete an existing worksheet
3)Add a worksheet (with a specific name) after another already existing worksheet.
4)Import some data from the other worksheets in the same workbook and do some number crunching (I'll come back in a future posting if I have problems with this when I get there)
5)Save the worksheet under a new name in the same directory I opened the original file from.

Right now I'm just trying to figure out the code for #'s 1,2,3 and 5. I figured out enough to accomplish #'s 1 and partially what I wanted for #'s 2, 3, and 5 but not really.

When deleting the sheet ("Updates") I don't want I'd like to get rid of the "Do you really know what you are doing?" warning message and just delete it. Is there a flag I can set to False or something?

With #3 I can add a sheet but I'm not able to take advantage of the "before", "after" placement options probably because of bad syntax and I haven't seen any way to also define the name of the worksheet being created in the same line of code. Assuming that's not possible, I also haven't been able to rename the worksheet in a seperate line of code either.

Here's the latest code I tried to use for all this:



wbkSummary.Sheets.Add(,"F3",,) 'add after existing sheet named F3
wbkSummary.Sheets("Sheet1").Name = "Cert_Summary" 'rename new sheet

Finally, on #5, I can get the workbook to save where I want it under the correct name but I'd also like to get rid of the "Do you really know what you are doing?" message in the event of an existing file under the same name and just automatically overwrite it. Right now all I have is:


Any help would be appreciated. Thanks.

Merge Data From Multiple Excel Sheets Into A Workbook
I need to create a utility in VB which picks up all the excel files from a specified directory. Assume each of these files have 3 sheets with data (text) in the same format. I need to create a new excel file with similar format containing the 3 sheets and merge the contents from all the source excel files into this new excel workbook.

Manipulating Other Sheets In A Workbook From Embedded Controls In A Different Sheet
can anyone please tell me why this code keeps crashing????...

i have used code like this about 332 thousand times and now i get a RT 1004...

i using excel with embedded controls from a sheet called command form and i'm trying to manipulate another sheet in the same book called expense tracker... when i move to the expense tracker sheet the code works perfectly...
however i'm trying to run it from the command form...

if i add a sheets("Expense Tracker").select
it of course works great... but i would rather not have to select the sheet to manipulate it...HELP!

Private Sub CommandButton3_Click()

With Sheets("Expense Tracker")
.[J201].End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=12
End With

End Sub

Loop Sheets In My Workbook For Value Then Paste Matching Rows Into New Sheet
I am trying to create a report made up of rows of data from a number of sheets in my excel workbook. I want to look up a value in all the sheets and add the rows that match into a new sheet. I have searched for ages for something to start me off and this is what I have found on this forum:

Private Sub FilterObjectives_Click()
Dim LastRow As Long
Dim i As Long
Dim SheetRange As Long

If WorksheetFunction.CountA(Cells) > 0 Then
'Search for a name - I need this to be a box where I can type a value

LastRow = Cells.Find(What:="Bob", After:=[a1], _
SearchOrder:=xlByRows, _
End If
SheetRange = 1

For i = LastRow To 2 Step -1

'where this code creates lots of sheets I just want mine to add the rows it finds to a single sheet
If Range("C" & i) = Range("C" & i + 1) Then
Sheets("Sheet1").Range(Cells(i, 3), Cells(i, 7)).Copy
Sheets("A" & SheetRange).Range("A" & Sheets("A" & SheetRange).Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)

SheetRange = SheetRange + 1
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = ("A" & SheetRange)
Range(Cells(i, 3), Cells(i, 7)).Copy
Sheets("A" & SheetRange).Range("A" & Sheets("A" & SheetRange).Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)

End If

Next i

End Sub

It doesn't do exaclty what I need, I am not sure if what I want to achieve can be done? Is there any way I can adapt this code to loop through all my sheets and save the matching rows to one sheet - it needs to overwite the sheet each time I run the code with the new values.

How do I make the code stand out? I have tried using [/] [/vb] but they dont work

Edit by Moderator:
Please post Excel questions, in the Excel forum.

Please use the [vb][/vb] tags when you post your code.

Thank you.

Help On Auto Renaming File1, File2, File3 Etc
Hi, its me again
last question for that day

I got this test program i wrote that generates random numbers at a 5 second interval and i got it to capture the number and save it to a file eg at c:file1.txt

the problem I got is that it overwrites the file testfile1.txt every 5 seconds. I want it to create and save each one into a different txt file name eg using a for loop that creates file1, file2, file3 etc
How you understand, thanks in advance

Auto Delete Tab In Workbook
I am writing a macro that will delete certain tabs in a workbook. Below is the script. Is there any way to make the macro bypass the popup that notifies of permanent deletion. See the attachment. Thanks

Sheets("Drop Down Values").Select

Auto Open A Specified Worksheet In A Workbook
My workfile contains a few worksheets and I name the Sheet 2 as 'Coupondate'. My VB code below (summary) will auto run once I open the particular file.

Private Sub Workbook_Open()
If Len(Sheets("Coupondate ").Range("M" & LRow).Value) > 0 Then ......

End Sub

My question is how to make the system auto bring me to the 'Coupondate' worksheet whenever I open the file. For example, although my last save of the file is on worksheet 5, but the system will still auto bring me back to worksheet 2 i.e. 'Coupondate' and run the above VB when I open the file.

Appreciate your kind assistance. Thank you.

Auto Run A Sub After Open Workbook Completely?

how can i run a sub automatically after open any workbook completely? it's not workbook_open or auto_open. i want to check if this opened workbook contain a sheet with a certain name. workbook_open or auto_open run automatically before the workbook open it's sheets. what's the sub name that runs automatically after opening a workbook with it's sheets?

thx in advance

Auto Opening && Resaving Excel Workbook
Hi everyone,

I was wondering if anyone had ever tried to write code to open a MS excel spreadsheet, resave it and then close at scheduled intervals?

I don't know if this is possible using windows task scheduler. There must be a way to do this using some sort of VB code.



A Search Function For Excel 2000 That Will Search All Cells On All Sheets Of A Workbook
I have been buiding a database in excel,...... I know access, but the people I work for refuse to use it so I am stuck with excel, unfortunatly in excel 2000 you can either highlight all your tabs to search the whole book, or you are stuck searching just one page. That and I need something that will also go to next. There have been a few ideas I have tried, none of which worked well at all.

Auto Execute Excel Macro When User Opens Workbook
I want to execute a formatting macro automatically when a user opens a workbook (assuming they click "Enable Macros"). Is this possible? and if so, how?


Creating An Auto-run Maco To Protect Workbook And Turn On VBA Protection *RESOLVED*
Hello all,
I need to write a macro that will run automatically when a workbook is opened and do the following:

1)    Protect the workbook.
2)    Turn on protection in the VBA Project Properties

The workbook starts life with each of its worksheets protected. Then, upon opening it should perform the above tasks and then be ready for use.

I cannot use workbook sharing and both of these tasks need to be performed automatically.

Can anybody help me out?


Edited by - M_B on 3/10/2004 11:30:07 PM

VB To Open Workbook With "n" Sheets?
Hello All,

Simple problem, but I can't find the solution.

How do I get VB to open a workbook with 3 sheets, instead of 1, if "1" is the selection in the options of Excel?



Compare 2 Sheets And Extract Common And Error Rows Into Another Sheets
Hope someone can help this poor newbie!

Allow me to explain.

I have a Excel Work Book with 2 Sheets named Caller(100 rows)and Subscriber(500 rows).
Caller contains 2 columns: one is audio file name(with .au extension)
and other is the content in that audio file.
Subscriber also contains 2 columns: one is audio file name(with .au extension)
and other is the content in that audio file.

Now what I wish to do is to compare Caller and Subscriber and:
To remove the common rows from Caller and Subscriber and place them in sheet3.
To remove the rows if it satisfies any of the following error conditions and place them in sheet4:
i)if any .au extension is missing in the column1 in both sheets
ii)if data in column1 is same and data in column2 is different
iii)if data in column2 is same and data in column1 is different.

I am herewith attaching the code I was able to prepare but not able to get the exact output.

Many thanks in advance.

With Regards,

Macro To Save Workbook Is Picking Up Previously Opened Workbook Filename Causing An Error.

I have written a macro to save a file in 3 different locations (one being an HTM file as well). I have several schedule files (created by doing File Save As from an original) that this macro will be applied to. I have inserted the macro into each file and created a Macro Menu item which has been mapped to the "This Workbook" macro item that i have written. (End Background, Begin Problem/Question) When I run this macro from one sheet, close that workbook, open a different workbook and run the macro, my macro looks for the previous file name. I am not "quite" sure if the problem is code related or tied to the menu assignment. I have included the code for reference and hope someone may have a better way of doing this.

Sub SaveHTML()

    Dim conNetPath As String
    Dim conHomePath As String
    Dim conBUPath As String
    Dim fName As String
    Dim HTMLFile As String
    conNetPath = "\atlnewsf04Sudhir KeepStuffskeds"
    conBUPath = "c:Schedules"
    conHomePath = ActiveWorkbook.FullName
    fName = ActiveWorkbook.Name
    HTMLFile = Replace(fName, "xls", "htm")

    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs Filename:= _
        conNetPath & HTMLFile, FileFormat:=xlHtml, _
        ReadOnlyRecommended:=False, CreateBackup:=False

    ActiveWorkbook.SaveAs Filename:= _
        conBUPath & fName, _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

'MsgBox conHomePath
    ActiveWorkbook.SaveAs Filename:= _
        conHomePath, _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

    Application.DisplayAlerts = True
End Sub

When you call this macro from the first file, it works fine. If you close that file and open another file with the same macro and try to run it, it looks for the previously opened file.

Not sure where I goofed. I have included two of the spreadsheets to possibly demonstrate.

Scott Medaugh

Opening A Hard Drive Stored Workbook Through A Workbook Embbed In SAP
Hi guys,

I am looking for alternative ways to open a workbook that might work. This code worked outside of SAP but once I put it into the integrated excel workbook, it fails to open the path it is given. I am pretty sure the cause is that it won't go after the file path inside the computer itself, which explains why it works fine when it is on the hard drive but not integrated.

Code: '*** Declare Variables
    Dim xWb As Workbook 'external workbook
    Dim xWks As Worksheet 'external worksheet
    Dim iWb As Workbook 'internal workbook
    Dim iWks As Worksheet 'internal worksheet
    Dim Crit1 As String, Crit2 As String 'PPG/CPG holders
    Dim Rng1 As Range, Rng2 As Range 'Range holders
    Dim Dat1 As String, Dat2 As String 'date range holders
    Dim xECol As Integer, xERow As Integer 'end col/row for ext worksheet
    Dim iECol As Integer, iERow As Integer 'end col/row for int worksheet
    Dim count1 As Integer, count2 As Integer 'Counters
    '**set internal values
    Set iWb = ActiveWorkbook 'Set to Active workbook
    Set iWks = ActiveWorkbook.Sheets("UISheet") 'Set to UISheet in active workbook
    If Range(importpathcell).Value = "" Then
        Exit Sub
    End If
    '**Open external workbook and sheet using importpathcell
    Set xWb = Workbooks.Open(Range(importpathcell).Value) 'Set to path cell ***THIS IS WHAT FAILS***
    Set xWks = xWb.Sheets("Upload") 'Set to "Upload" sheet in xWb

'**Set start/end variables
    xERow = xWks.UsedRange.Rows(xWks.UsedRange.Rows.Count).Row
    iERow = iWks.UsedRange.Rows(xWks.UsedRange.Rows.Count).Row
    xECol = xWks.UsedRange.Columns(xWks.UsedRange.Columns.Count).Column
    iECol = iWks.UsedRange.Columns(xWks.UsedRange.Columns.Count).Column

    count1 = constStartRow
    'Copy over PPGs
    Do While count1 <= iERow
        Crit1 = iWks.Cells(count1, 1).Value
        Set Rng1 = iWks.Cells(count1, 1)
        For count2 = 1 To xERow
            Crit2 = xWks.Cells(count2, 1).Value
            Set Rng2 = xWks.Range(Cells(count2, 1), Cells(count2, xECol))
            If Crit1 <> "" And Crit2 <> "" Then
                If Crit1 = Crit2 Then
                    Dat1 = iWks.Cells(count1, 3).Value
                    Dat2 = xWks.Cells(count2, 3).Value
                    If Dat1 = Dat2 Then
                        Rng1.PasteSpecial (xlPasteValues)
                        GoTo exitPPGFor
                    End If
                End If
            End If
        Next count2
        count1 = count1 + 1

    'count reset
    count1 = constStartRow
    count2 = 1

    'Copy over CPGs
    Do While count1 <= iERow
        Crit1 = iWks.Cells(count1, 2).Value
        Set Rng1 = iWks.Cells(count1, 2)
        For count2 = 1 To xERow
            Crit2 = xWks.Cells(count2, 2).Value
            Set Rng2 = xWks.Range(Cells(count2, 2), Cells(count2, xECol))
            If Crit1 <> "" And Crit2 <> "" Then
                If Crit1 = Crit2 Then
                    Dat1 = iWks.Cells(count1, 3).Value
                    Dat2 = xWks.Cells(count2, 3).Value
                    If Dat1 = Dat2 Then
                        Rng1.PasteSpecial (xlPasteValues)
                        GoTo exitCPGFor
                    End If
                End If
            End If
        Next count2
        count1 = count1 + 1
    xWb.Close savechanges:=False
    Application.Calculation = calc_mode
    Application.ScreenUpdating = update_mode
    Call SubRoutines.WorksheetProtection(iWks, True)
End Sub

I tried inputting a object file as so but it would fail when it gets to setting Rng2. It would get an error "Run-time error '1004': Method 'Range' of object '_Worksheet' failed". However this did manage to open excel in some form according to the processes on my computer using ctrl-alt-del.

    '**Open external workbook and sheet using importpathcell
    Set owExternalFile = CreateObject("Excel.Application")
    Set xWb = owExternalFile.Workbooks.Open(Range(importpathcell).Value) 'Set to path cell
    Set xWks = xWb.Sheets("Upload") 'Set to "Upload" sheet in xWb

This workbook does not need to actually open on the screen. Just to the degree that a .copy and .pastespecial can be used.

Thank you in advanced for any help and sorry in advanced for somewhat lacking comments. Haven't prettied it up yet.

Edited by - rodmanm on 7/19/2007 10:39:20 AM

Macro To Add Column To Workbook - Based On Data From Another Workbook???
I am trying to create a macro to do the following:

I have an excel file, called Warehouse.xls
This has two columns - Column A, which is ProductCode, and Column B which is WarehouseLocation
This file is a list of all my products, and where they are stored in the warehouse.

I then have a second workbook called Orders.xls
this is automatically generated from various vendors.
This has many columns, but the column relevant here is "ProductCode"

What i need to do, is to run a macro, and have it add a column to the end of my Orders.xls workbook, and populate this with the relevant WarehouseLocation (from Warehouse.xls) based on the value of the Orders.xls ProductCode.

Any ideas where i start?
Im familiar with both visual basic, and SQL (im a dba) but i've never done any vba work as such.

any help, or pointers in the right direction would be great!

i've attached the 2 workbooks.
data.xls is the orders file, and warehouse.xls is the "lookup" table... basically warehouse contains a list of product codes and warehouse locations.

what i want to be able to do, is load data.xls, and load the macro, so that it adds a column "warehouse location" and populates that column with data from the warehouse.xls file, based on the SKU from data.xls


Edit by Moderator:
Please post Excel questions, in the Excel forum.

Thank you.

Hide Sheets/objects, Save, Unhide Sheets/Objects
There was another post about this recently. I have pieced my code together with that and the help doc. here is what I got.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strCurrentSheet As String
strCurrentSheet = ActiveSheet.Name
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

If Hidden_Elements_Hidden = False Then Call Hidden_Elements_Hide
If SaveAsUI Then ThisWorkbook.SaveAs Application.GetSaveAsFilename Else ThisWorkbook.Save
If Hidden_Elements_Hidden = True Then Call Hidden_Elements_Show

Cancel = True

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Workbooks(ThisWorkbook.Name).Saved = True

End Sub
This works fine if I manually save the workbook and then manually close it.
Although, if I just goto close the following happens. I get the save changes prompt. After it saves, I get that same prompt again.

Any Ideas?

Is there a way to test if the document is closing in the beforesave event?

Activate A Workbook Based On A Value In A Cell In Another Workbook
I want to activate another active (open) workbook only if its name is the same as what I have recorded in a cell in my workbook. If it does return the same name then it is activated, otherwise a message box displays.


In my Reports workbook on a worksheet name “formula” in cell B33, I have the value “JulyData”. This represents the name of another workbook (i.e. JulyData.xls)

Before a routine is run to import data into Reports.xls I want to verify that the name of the source workbook is actually “JulyData.xls” (as verified to B33 on my “formula” worksheet) and not “AugData.xls”. If it is “JulyData.xls” then I want the procedure to make it the active workbook and start importing data from selected cells. If it is not, then a message box will display.

I know this code doesn’t work:

TW = Worksheets("formula").Range("B33").Value
If Windows.Activate = TW Then Windows.Activate

MsgBox ("Error - Data workbook name does not match." & vbCr & _
"Select correct month for data import."), vbOKOnly, "Data", a, a

I need some help please

Question On Protecting Workbook And Shared Workbook
Hi Guyz,

i have a few questions to ask..

1) How do i programatically protect and unprotect a range of cells, for
an example range("A1:G65500")..Means that user cant make modification
to these cells...

2) How do i write a program to share/unshare workbook?

if workbook is shared, unshare it..

Creating A New Workbook From An Open Activeworksheet In Another Workbook
I am trying to Create a new workbook from an open activeworksheet in workbook

Has anyone got any example code just to get me started



How Do I Copy From One Workbook And Paste To Another Workbook
Please can you help I am trying to copy from one workbook and paste to another


Controlling A Workbook With A Macro In A Different Workbook
I have an excel workbook that contains a key -- a set of placeholders in one column with their associated values in the next column -- and I have a macro that will open a word document and find & replace all those placeholders with the correct macro.

I would like to extend this macro so that it will work for another excel document, not just word documents. So far, when I try to modify the code from the original macro, the workbook that I'm working in (the one that contains the key) things that IT is the activeworkbook, not the other one it's just opened, so it ends up just replacing the placeholders in the key.

Does anyone know how to tell it that I want it to use the find/replace macro on the OTHER workbook it's just opened and not itself?


Run A Macro That Exists In A Workbook From Another Workbook
 I'm looking to run a macro/procedure/function that exists in another workbook. Can anyone point me in the right direction?

Copyright © 2005-08, All rights reserved