Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Renaming Worksheets Via VBA


I am probably just having a stupid moment, however I have a problem

Whats the best way to rename a worksheet using VBA?

I was going to use a count function to count the number of worksheets, then change worksheet (eg) 10, 11 & 12


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Renaming Tabs On The WorkSheets
I mean renaming tabs on the sheets, the tabs at the moment are called Output 1 (*****) instead of Sheet 1 etc.. and i would like that changed to contents in cell A9. If possible only the Output 1 will be removed and it will change to something similar "X-Ray (5E4TT)"

i have a workbook with worksheets named Output 1 (*****)

(the stars being a five digit/letter code - the only thing that changes on the workbook)

now the problem is, i pull of reports and sometimes worksheets can be up to one hundred.. now in cell a9 is the name of the report. I have found vba code to rename sheets to cell contents **extract below**

but any chance of renaming contents of cell a9 to a worksheet named Output 1 (*****) etc.. and if the contents of cell a9 can be trimmed so only certain part of a lengthy title


Sub RenameTabs()

For i = 1 To Sheets.Count
If Worksheets(i).Range("A1").Value <> "" Then
Sheets(i).Name = Worksheets(i).Range("A1").Value
End If
Next

End Sub

View Replies!   View Related
Renaming Worksheets In Files In A Directory
Hope i can get some help here as my vba experience is extremely limited. I'm trying to run a macro from a spreadsheet that will go down a list of file names that i have entered in a worksheet where the macro resides and open those spreadsheets and rename the worksheets in each file according to a list of names that i have entered in the 10 columns next to the file name. It's easier to explain with the layout of my macro spreadsheet: ....

View Replies!   View Related
Adding And Renaming Worksheets Using VBE
I've undertaken a project for work with my very very limited knowledge of Excel VB.

What I am trying to do is automate the process of creating a new worksheet in a "Master" Workbook, rename and save that worksheet with the date it was created.

Each worksheet will hold a list of dates for staff members who have attended or attempted courses for that week and the information will come in via email in separate pre-created worksheet templates. The consolidated weekly data will ultimately end up in an MS Access db (but needs to be sorted and validated before upload).

Here is the code I have so far:

Private Sub AddSheets()

Application. ScreenUpdating = False

Worksheets.Add.Move Before:=Sheets(1)
Worksheets.Visible = True

Dim myDate
myDate = Date

Sheets. Name = Date

End Sub

Using VB6.3 in Excel 2000

View Replies!   View Related
VBA - Renaming Tab
I have a macro that copys a worksheet into a new workbook - can anyone tell me the VBA to rename the new tab with a cell reference?

View Replies!   View Related
Renaming Files Using VBA
I have forty files in a folder that are generated by a system every day.
The files all have something in common, they all include "V9TEST" in them.

for example:
123asdfV9test.20090915123456
160asdfV9test.20090915124455

I would like to run a script that would rename these files in the folder, taking out the V9test part.

result:
123asdf.20090915123456
160asdf.20090915124455

View Replies!   View Related
VBA: Inserting And Renaming A Worksheet
I'm relatively new to VBA and require programming help with the following:

I have created a button to add a specific worksheet template ("TE - Template") after another worksheet ("CO - Cockpit") and then name it:

Sub Add_worksheet()
Sheets("TE - Template").Select
Sheets("TE - Template").Copy After:=Sheets("CO - Cockpit")
ActiveSheet.Name = "AL - Class 1"
End Sub

However, I would like to let Excel check (via VBA) if the "AL - Class 1" worksheet already exists. If it does, the same template sheet should be added but named differently: "AL - Class 2". This should be possible for X worksheets (i.e., "AL - Class (X + 1)" everytime I add a new template worksheet. Thus, I would like to keep the same name (i.e., "AL - Class"), but with an increasing number (i.e., 1, 2, 3, X).

how I should amend the above code or supply me with a better (and efficient) way of programming this query?

View Replies!   View Related
VBA Shortcut For Renaming Multiple TextBoxes
Is there a shortcut to rename multiple TextBoxes?

So far I've been renaming each individual TextBox manually. This takes forever...

I have a lot of TextBoxes & hope there is a faster/easier solution.

Example:
TextBox1 --> OtherName1
TextBox2 --> OtherName2
TextBox3 --> OtherName3
TextBox4 --> OtherName4
TextBox5 --> OtherName5
TextBox6 --> OtherName6
TextBox7 --> OtherName7
TextBox8 --> OtherName8
TextBox9 --> OtherName9
TextBox10 --> OtherName10

View Replies!   View Related
Renaming Workheet As Date Format (cell Reference) Using VBA
I download & import CSV files/ sheets from bank, visa accts etc & I am trying to automate the processes I use including ensuring I (& others) will use the same naming conventions eg visa "yymm" = V0701, Direct Cedits = DC0701 etc. I am having trouble renaming or ensuring the active sheet is named according to the date in A3 eg in the example I want the sheet to be renamed V0702 as the date in the cell is 02/02/07 "dd/mm/yy" format ( imperial system). In BOLD is what I have & the renaming is messy becuase I searched the forum, Walkenbach's bible & help menu.

Sub VisaMonthlyAcct()
Dim shtName As String
Dim newName As String
ActiveSheet.Activate
'ActiveSheet.Name = shtName
newName = "V" & Format(DateValue("A3"), "yymm")
'Old name for future reference = Visa0701 (2)
ActiveSheet.Name = newName
Worksheets(shtName).Name = "V" & Format(Range("A3"), yymm)
'Worksheets("Visa0701 (2)").Name = "V" & (FormatDateTime(Range("A3"), yymm))
'To select Date column & format
Range("A3:A66").Selection.NumberFormat = "dd".............

View Replies!   View Related
Renaming Tabs ...
Is there a way to rename tabs from a seperate list. Or link the Tab name to a list?

View Replies!   View Related
Renaming A Cell ...
I have renamed a cell to "final"

Now im trying this:
Range("A3:final-1").Select

But that doesnt work, I want the cell just above final.

And I cant refer to the cell above directly due to some factors.

So how could say final - 1?

View Replies!   View Related
Renaming Files ...
I have a folder containing pictures.

I have an excel sheet containing data.

The pictures' filenames are #s which are located in Column A of the spreadsheet. I would like to be able to have excel take the picture name, lookup which Row it is and then add the information from Column B, C, D and E into the filename. It would need to do it for all the pictures located in the folder.

View Replies!   View Related
Renaming Sheets
Is there a way you could have a sheet named after the date like the formula of NOW

View Replies!   View Related
Getting Data Out Of Worksheets By Using Vba
My project gets gradually final form, but I have to deal with the following problem.

I have to make a report out of 9 worksheets (+/-1Mb), those are placed in 9 directory's on a server. The filenames of the worksheets are variabel. Something like 20_10-02-2010A.xls. Data to be copied is always in the same cells and sheetname is also the same in all worksheets.

So far no problem, before I used the INDIRECT function but with a macro i have to open all the worksheets to run this function.

I was wondering if it is possible to do this easier in vba.

I put here a little test setup, and hope someone can put some code in worksheet rapport to gets the data in the worksheets.

View Replies!   View Related
VBA Combining Worksheets ...
Four worksheets (A,B,C,D) all begin with row2 and end with the last non-blank cell.

What VBA code would merge the information from the four worksheets to Worksheet E?

View Replies!   View Related
Looping Through Worksheets In VBA
I'm trying to loop through all visible worksheets and then when im those worksheets, I want to run a simple macro.

Here's the code I have:


Sub formatting()

Module10.Part5
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

Module12.Part6

Next ws

End Sub
The problem I'm encountering is that the macro(Module12.Part6) keeps running in the ActiveSheet (the sheet i have open) and isn't actually looping through the worksheets.

Is there any easier or better way to apply a macro to all visible worksheets?


View Replies!   View Related
Vba Sumif Over Several Worksheets
i've seen variations on a theme of this.. but have tried adapting some codes with no success.

But i'm convinced this is a common need / request.

i want a summary sheet with used reference numbers in column A.

I'd like cumulative costs in column B.

The costs to build up the cumulative value will come from any number of worksheets in the workbook... they will have the same reference protocol in column A with the costs per period in column B.

I'd like the summary sheet to run a cumulative sumif over the "other" worksheets and provide the cumulative costs..

and also.. in a nirvana type world.. to add any new references on any of the "other" worksheets to the bottom of references in the summary list and to run the cumulative sub against those too.


View Replies!   View Related
VBA Inactive Worksheets
I am having trouble referencing a range in an inactive worksheet to feed an vba array. this is the following vba code that does not work:

View Replies!   View Related
VBA: Compare Between Two Worksheets
I m running compare between two worksheets.

1.1st sheet is my new data.
2.2nd sheet is my old data.
3.3rd sheet is where I want to show the differences.

I’m looking to do the following:
1.Compare Column ‘A’ from the old data (sheet 2) to the new data (sheet 1). If a new value for Column ‘A’ appears in sheet 1 and is not on sheet 2, then I want the row copied from sheet 1 to sheet 3 and be appended by ‘ADD’ in Column ‘M’.
2.Compare Column ‘A’ from the new data (sheet 1) to the old data (sheet 2). If an old value for Column ‘A’ appears in sheet 2 and is not on sheet 1, then I want the row copied from sheet 2 to sheet 3 and be appended by ‘DELETE’ in Column ‘M’.
3.Compare Column ‘A’ from the old data (sheet 2) to the new data (sheet 1). If the value for Column ‘A’ is the same but the value for either Column ‘B’ or Column ‘L’ is different then I want the row copied from sheet 2 to sheet 3 and be appended by ‘FROM’ in Column ‘M’ and I want the row copied from sheet 1 to sheet 3 and be appended by ‘TO’ in Column ‘M’.

View Replies!   View Related
Renaming A Tab In A Workbook
I have several tabs in a workbook that need different names depending on what is selected from a drop down menu on one of the sheets - "Staff Details" sheet. Any clever persons out there able to help?

View Replies!   View Related
Renaming Tabs Macro
is there any way that i can adjust this code so that if there are not enough worksheets in my workbook to support my list that more worksheets will be made and renamed? Specifically I would like to copy the first worksheet and then rename it based on my list. also... If I have more worksheets than my list requires it can delete unused worksheets?

Sub NameSheets()
Dim i As Long
Dim ws As Worksheet
i = 1
For Each ws In ActiveWorkbook.Worksheets
If ws.Name "Sheet Names" Then ws.Name = Sheets("Sheet Names").Cells(i, 1)
i = i + 1
Next ws
End Sub

I have a list on "Sheet Names" that changes the tab names on each sheet when i run the macro

View Replies!   View Related
Renaming Tabs With Dates
I´d like to have a single workbook for each calendar year, with a tab for each date Monday through Friday (like 11-Jun, 12-Jun, etc.). I could then type the patients` names and times of arrival, among the other information I track. Alternatively, I could have a workbook for each month of each year (titled for example 2007 June or 2008 August), with the tabs titled by the date of the month (like 1, 4, 10, etc).

Is there an easy way to do this without manually renaming each tab for each day of the year?

If it´s too hard to limit the macro to create tabs for only the days of the workweek, it wouldn´t bother me if the workbook had to include every day of the week (Sunday-Saturday). I can always go back and delete the unnecessary ones.

View Replies!   View Related
Auto Renaming A Tab
Im sure ive seen this somewhere before but i cant find it after searching.

I would like the tab to auto rename with whatever is input into cell A1.


View Replies!   View Related
Renaming Files In A Folder
Every week I get a folder full of files and I have to rename them with a week ending date...Below is what I hacked together, but it's not working...

Sub Rename()

Dim OldName, NewName, npath As String
weekendingdate = InputBox("What is W/E Date??")
npath = "J:Test*.pdf"
While Smith ""
OldName = Smith: NewName = Smith & weekendingdate
Name OldName As NewName
Wend

End Sub

View Replies!   View Related
Renaming Files From A Spreadsheet
to take a spreadsheet in excel and rename files with it. I will get all the columns together and

named properly by using the concatenate function. So when i go to rename i would have a

list of say 5,000 things to rename. In column A will be the full path. In column B will be the

new name i want it to replace the old one with extension and all. The reason i want to do this

is because that way i can work with the 5,000 files column A, B, C, D, get everything the

way i want it and then concatenate them. Then rename the files after copying and repasting

them so there is no formula. This would just be much easier than having to go to each

individual file and rename. also a lot easier to be able to compare and see what is going on

in spreadsheet i can sort compare. just a lot easier. i have messed around with excel some

but not macros much. what i would like to do is for a macro to look in A1 for path then

rename with B1. then A2 for path then rename with B2. loop through all rows until there is

nothing in A? that way there does not have to be a certain number of files. i do not know if

example
A1
C: estSGB04SGB04-08 - Frank Sinatra - Wives And Lovers.zip

rename to B1

SGB04-08 - Sinatra, Frank - Wives And Lovers.zip

running excel 2000 windows xp

View Replies!   View Related
Replicating Sheet And Renaming
I got a workbook containing a worksheet called APheb1.

I would like to create a macro that would replicate this worksheet and rename it APheb2, APheb3 etc... till a number i specify. For example if i specify 90, then it would replicate APheb1 90 times till APheb90.

I have recorded a macro that replicates the file but i cannot find a solution for the renaming part.

View Replies!   View Related
Renaming Charts In 2007
In the old Excel it is possible to rename a chart by pressing SHIFT+Mouse Click and then write a new name in the upper left corner name box. You can apparently do the same thing in Excel 2007, but the new name doesn't stick.

View Replies!   View Related
Sequential Tab Renaming
Is it possible in Excel to automatically rename all the tabs of a workbook in one move in a sequential format - eg renamimg 52 weekly tabs Week 1, Week 2, Week 3 etc.

View Replies!   View Related
VBA To Compare Two Worksheets In Same Book
I have a workbook that contains two worksheets. The columns are the same between the two, column A is the Employee ID in both worksheets. The other column headings are: Name, address1, address2, city, state, zip, EmgerName, EmgerType, Cell, Address1, Address2, City, State, Zip.

What I'm looking to do is compare WS1 with WS2 and in WS2, what ever is different from WS1, the font color changes to RED. For example let say for employee number 1234, everything is the same expect for EmgerName and Cell, then these fonts are changed to RED in WS2 so I can identify them quickly.

View Replies!   View Related
VBA To Move Data To Other Worksheets
I have a file which contains multiple worksheets. The applicable worksheets for my question are: "NJSS", "NJSS2","NJSS3 and SHORT FORM." NJSS is pictured below. NJSS2 and NJSS3 look identical.

NJSS

******** ******************** ************************************************************************>Microsoft Excel - Trial5.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutK15L15M15N15O15P15Q15R15S15=
KLMNOPQRS11Luminaire*Description#*of*LuminairesWatts*per*LuminaireConnected*Watts[K*J]Composite*Connected*Watts/Square*Foot[SL/SB]Incentive*margin*[F-M]*(If*less*than*zero,*enter*0;*no*Incentive)*Lighting*Level*Incentive[SB*N*$1]**Fixture*Maximum*Incentive{SJ*$30]*Program*Incentive[Enter*lesser*of*O*or*P]121314*********1500#N/A#N/A#N/A#VALUE!#VALUE!*$********-***#VALUE!NJSS*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

View Replies!   View Related
Vba Fill In From Cell Above Over 2 Worksheets
I'm looking at filling in a cell value from the cell above in column A but over a range of worksheets. It only needs to fill down until the last used row.

The last used row in the worksheet will vary for each worksheet .. but the longest column will be column T or U.

this code "works" but only for the active sheet... i'm sure its something easy to adjust.

Sub CopyDataDown()
' ---
Dim ws As Worksheet
Dim Lr As Long

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets
If ws.Name "Notes" _
And ws.Name "FrontSheet" Then
Lr = WorksheetFunction.Max(Range("T" & Rows.Count).End(xlUp).Row, Range("U" & Rows.Count).End(xlUp).Row)
With Range("A2:A" & Lr)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With

End If
Next ws
Application.ScreenUpdating = True
End Sub

View Replies!   View Related
VBA UDF To Add Worksheets
I want a UDF to copy a data range, add a new worksheet and paste the range into it as part of a function calculation not a user interface macro.

I simply cannot add a worksheet from a Sub that is called by a Function, only by a Sub launched as a macro. As shown below - the Function quits at Sheets(SheetName).Select as there is no such Sheet.

It seems that VBA cannot add worksheets from UDFs. Does VB .Net do this? How can I add sheets within a UDF without user interface objects?

View Replies!   View Related
VBA: Cannot Delete Worksheets Anymore
i've been programming a bit in excel and quite new to the whole thing but managing nonetheless... however, after having built a couple of macros in excel, i can now no longer delete all the worksheets listed at the bottom of the workbook.

View Replies!   View Related
To Open Worksheets Without Macros From VBA
Does anyone know if it is possible to open an excel worksheet from excel VBA, without importing the macros from the worksheet being opened?

I have tried the following approach:

View Replies!   View Related
VBA - Select Same Range On All Worksheets
how to select the same range of cells on all worksheets via VBA?

I tried google several times but could not find anything that helps. I need to select a range of cells on all worksheets and then clear their content. However, google only tells me to select all sheets manually, which is not good since new ones are added every now and then.

Is there maybe a possiblity to select a range on all un-selected sheets?

View Replies!   View Related
Inserting + Renaming Tabs Via A Macro
Just a quick on i hope. Im trying to create a macro that adds a worksheet/tab and Rename it to the month after the tab already showing.

e.g

if i already have two tabs one dated january and the other named february i require the macro to say march then the next run would be april if you get my drift.

View Replies!   View Related
Renaming Multiple Tabs By Month
I would like to rename multiple tabs (12 in all) on a spreadsheet by month only. I highlighted all tabs and then performed a cut and past from the previous year spreadsheet, but when the paste was complete the tab names were missing. I need January through December on the 12 tabs. Does anyone know of a shorter process than renaming each tab individually? I have called several people and asked the same question and they are curious if there is a way to do this also and asked that I let all of the know what I find out, so you would be helping quite a few people in several different companies (If that gives you happy thought, then good for all of us ).

View Replies!   View Related
Renaming Objects In TXT File
I manually Copied and Pasted contains in TXT File.
The TXT file is as below.
It has only 1 Column

7AB/9AB
8AB/10AB
7CA/10CA
7BB/10BB
7CB/10CB
7AA/9AA

What I want is to run some VB Script so that anything after / is ignored
and the Output shold be reformatted as below
7A1
8A1
7C0
7B1
7C1
7A0

The Above O/p is anything aftyer / is ignore and the last character of the 1st field is A is replace with 0 and B is replace with 1.


View Replies!   View Related
Renaming Worksheet Tabs Macro
I would like to create a macro that would bring up an input box or preferably a list box that will allow me to input information for a sheet/tab name where where "TBL NPL NGRPL" appears in the code at the end of this message. The macro needs to be available to any new file created

The only worksheet names needed are below.

TBL NPL NGRPL
TBL NPL NIAU7
TBL NPL NIAU8
TBL NPL NIA10
TBL NPL NNDU4

This is the extent of my ability:

Sub Macro1()
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "TBL NPL NGRPL"
Range("A1").Select
End Sub

I created six macros, but there must be an easier way.

View Replies!   View Related
Copying Worksheet To Same Workbook And Renaming It
i would like to use a macro which copies a worksheet from a workbook to the same workbook and paste it at the end and then renaming it... everything using a macro...

View Replies!   View Related
Event Triggered When Renaming Sheet
I need to declare an event that happens when a worksheet is renamed

the code that would trigger the event is

View Replies!   View Related
Parsing/renaming Data In A Cell
Sheet4

ABCDE1Source DataDesired Result
Include these extensions: Exclude these extensions:2OLD.FILENAME.rar.htmNEW.FILENAME.rar
.part*.rar.htm3OLD.FILENAME.part01.rarNEW.FILENAME.part01.rar
.rar.html4OLD.FILENAME.part02.rar.html.htmNEW.FILENAME.part02.rar
.avi
5OLD.FILENAME.txtNEW.FILENAME.txt
.zip
6OLD.FILENAME.zip.htmNEW.FILENAME.zip
.txt
7OLD.FILENAME.aviNEW.FILENAME.avi
.7z.*
8OLD.FILENAME.7z.01.htmNEW.FILENAME.7z.01

9OLD.FILENAME.7z.002NEW.FILENAME.7z.002

I'm looking for a formula which can take my original data (column A) and "transform" it into what you see in column B.

I am transferring large amounts of files from various servers to my ftp and back out again. My original data are url filenames which of course, are named differently and not how you see it in my example.

The filenames themselves are preceeded by website names and varoius folder/path names. I edit the links to include various Usernames and Passwords and at the same time, rename the original filenames. Then I transfer the files to my ftp.

Most of the files have a ".part*.rar" extension at the end, so my formula has only included this scenario. Rather than manually editing my formula everytime a different extension is present (about 20-30% of the time), I would like to find a way to automatically look at a table/list of possible extensions (D2:D7) which are to be recognized for the sake of finding the end of the filename itself, so that I can rename (SUBSTITUTE) the original filename to that of my choice (cell D13) and of course, also include the original extension(s) of the source data (keeping this is important, especially in the cases where ".part*.rar's" are present). The list of extensions in cells E2:E7 are to be excluded from the new filename.

My present formula works fine for the editing of the username and passwords for various servers and for finding the beginning of the filename in the original url. It also works fine for finding the end of the filename (for the sake of renaming it), but only in cases where a .part*.rar" is present.

View Replies!   View Related
Move/copy And Renaming Sheet
I have the following code to move/copy worksheet and then copy and paste
special values. Is it possible during this process to get the user to change the 'moved' worksheet name ?

View Replies!   View Related
Renaming Of Worksheet Tabs From List
I have a workbook with 37 sheets in it. 36 of them are three per month (e.g JanCash, JanWeekly, JanMCR, etc) Since we make updates/ changes to the file throughout the year I cannot reuse the files. Also since we have accounts on different fiscal years, I need a way to rename the sheet tabs from a list. Does anyone know how I can do this with VBA code?

View Replies!   View Related
Viewing And Printing Selected Worksheets - VBA
I have a workbook containing a number of spreadsheets. Some of the spreadsheets are user inputs. The results of the user inputs drive a number of final reports. The final reports (i.e. spreadsheets) are hidden from the user (I don't want the user to be overwhelmed with so many tabs when they open the excel spreadsheet).

I created on the main input tab spreadsheet the following:

1. Check boxes - so that user can select after making his/her inputs the reports that he/she wants to view or print.

Say there are 4 reports (call them Sheet1, Sheet2, Sheet3, Sheet4 - therefore, 4 check boxes. Through the Format Control, the checkboxes have cell links that yield TRUE (if selected) or FALSE if not selected - linked to cells A1, A2, A3, A4 respectively.

2. Option buttons - one for view and another one for print. Through the Format Control, the View and Print option buttons have cell links to cell A5 yielding 1 for View and 2 for Print.

3. Command button - that will clear the check boxes

Issue
I would like to know if there is a way to code in VBA to:

1. Unhide the spreadsheets corresponding to the check boxes if selected;

2. Print the spreadsheets corresponding to the check boxes if selected for printing; and

3. Clear the checked boxes to unchecked if the Command button is clicked.

I'm struggling with coding to perform the above tasks.

View Replies!   View Related
VBA Code To SUM Range On Specified Worksheets
I want to try and do is as follows:

- go through all the worksheets in the workbook
- if the sheets begin with A, C or P
- and if monthly index = 0
- sum the range of cells - B33:L51
- output the value into cell L52
- etc

So far this is what I got

For i = 1 To Sheets.Count
If Left(Sheets(i).Name, 1) = "A" Or Left(Sheets(i).Name, 1) = "C" Or Left(Sheets(i).Name, 1) = "P" Then

If (MonIndex = 0) Then
SUM(B33:L51)
ElseIf (Index = 2) Then
SUM(B34:L51)
ElseIf (Index = 3) Then
SUM(B35:L51)
End If

My questions really are - how do u write the code for it to generically know to sum the range B33-L51 for like all sheets required. - and is it possible to output that value to a specific cell in each sheet?

View Replies!   View Related
Activechart.export Not Renaming Temp File
I am trying to export a chart using the code below, the loops change the data that is used to produce the chart:

Dim strFilename As String
Dim strExt As String
Dim lngProfile As Long
Dim lngOption As Long
For lngOption = 1 To 3
Range("nmOption") = Choose(lngOption, "Existing", "Option 4", "Option 5")
For lngProfile = 1 To 4
Range("nmProfile") = "Profile " & lngProfile
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveChart.ChartArea.Select
strExt = "gif"
strFilename = ThisWorkbook.Path & "Images" & _
Range("nmOption") & "_" & Range("nmProfile") & "." & strExt
ActiveChart.Export strFilename, strExt
Next lngProfile
Next lngOption

Each export command creates a file with the correct name but no data (0kb in size) and a temporary file that is the correct file type (I have tried both jpg and gif).

I could try and copy the temporary file, but why does Excel not finish the job? I have tried to put a loop in and also added DoEvents after the export but no joy.

View Replies!   View Related
Function Works In VBA But Does Not Work Inside Worksheets
i have written a code in VBA to interpolate the value of Y0 corresponding to X0 using a set of (X,Y) points. (I have written this in module1) . This function works when i call it through a Sub or another Function in VBA. but it does not work when I try to use it as a function in my excel worksheets (when I type : =interpolate(A1:A10,B1:B10,30) .... 30 is an arbitrary value). in this case I get #value! error

The function is known in within the worksheets because when I start typing its name, the Auto Name Complete feature of excel, finds this function.

I am new to VAB for excel. Please give me a hint to see my mistakes of if something is missing inside my code.

PHP
Public Function Interpolate(ByRef X() As Double, ByRef Y() As Double, ByRef X0 As Double) As Double    Dim I As Integer, Slope As Double, NData As Integer        NData = UBound(X)    For I = 1 To UBound(X) - 1        If (X(I) = X0) Then            Interpolate = Y(I)            Exit Function        ElseIf (X0 < ListMax(X(I), X(I + 1)) And X0 > ListMin(X(I), X(I + 1))) Then            Slope = (Y(I) - Y(I + 1)) / (X(I) - X(I + 1))            Interpolate = Y(I + 1) + Slope * (X0 - X(I + 1))            Exit Function        End If    Next I        End FunctionPublic Function ListMax(ParamArray ListItems() As Variant)        Dim I As Integer        ListMax = ListItems(0)        For I = 0 To UBound(ListItems())            If ListItems(I) > ListMax Then ListMax = ListItems(I)        Next IEnd FunctionPublic Function ListMin(ParamArray ListItems() As Variant)        Dim I As Integer        ListMin = ListItems(0)        For I = 0 To UBound(ListItems())            If ListItems(I) < ListMin Then ListMin = ListItems(I)        Next IEnd Function 

View Replies!   View Related
VBA Code- To Pull All The Names Of The Worksheets In A Workbook
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 Replies!   View Related
VBA Code To Unmerge Cells On Specific Worksheets
I have a workbook with multiple worksheets. On some of those worksheets a have merged cells, so in order to paste new data I need to unmerge them first. Below is the code that I wrote for it. However it doesn’t work and returns “Run-time error '1004': Application-defined or object-defined error”. I looked at this post List Maximum Value From Each Worksheet and tried adding With – End With, but it failed too. As far as I understood the problem is that the Worksheets/Sheets object does not support UnMerge property, but I may be wrong. If I add ws.Activate line (test2) everything works fine.

However I was wondering if there is a way to do it WITHOUT activating the worksheets.

Sub test1()
Dim i As Integer, ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
Case "5 - Top Network Facilities", _
"5b - Top Arb Facilities"
For i = 0 To 9
ws.Range(Cells(2 + i * 5, 1), _
Cells(6 + i * 5, 1)).UnMerge
Next i
Case Else
End Select
Next ws
End Sub

View Replies!   View Related
VBA CODE On Pasting On Last Row Of Data For Various Worksheets
I have got a workbook with 20 or more worksheets. Every quarter I need to paste/append new data on these worksheets (which has already got existing data from previous quarters). All worksheets have different rows of data, for eg One worksheet may have 10rows, the other may have 50. Hence when I am writing my code, I cannot specify the destination cell as for eg A11, or A51.

How do I write up a VBA code that looks for the last row of data on these various worksheets and then pastes the new information on the next row(new row).

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved