Dim iStartBook As Long, iNewBook As Long
iStartBook = ActiveWindow.Index
ActiveWindow.NewWindow
iNewBook = ActiveWindow.Index
I'm trying to grab the "2" that .NewWindow generates. The above gives 1 both times, despite the obvious 2 in the caption after you run it. So what property works? What does .Index mean here?
I've built a macro to run a loop that opens excel files, prints certain sheets and closes them. I have the macro working - to some degree. here is my code (this is excel):
Sub RMPProducer()
OldPath = "S:RMBS_Performance_Analytics Analysis1 Staging Folder For Monthly Model Templates2007200704VVDeals"
Dim t As Workbook
Dim s As String
Dim a As Window
With Application.FileSearch
.NewSearch
.LookIn = OldPath
.SearchSubFolders = False
.Filename = "*.xls"
.MatchTextExactly = True
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
s = .FoundFiles(i)
I have 2 problems. First problem is that I am trying to add a function to make the window close once its finished printing. This actually works - but it limits my loop iterations from i = 1 to 2 (so it only runs 2 times and I'm not exactly sure why). If I don't include the statement - this can run for up to 7 - 8 times (these are very large files) then excel runs out of memory. Unfortunately, i can't set the loop to run from i = anything other than one.
So What I would prefer is to be able to make the "ActiveWindow.close" command work - because this way I can run the entire loop. But I'm not sure how to. I tried window referencing, I tried adding a new active Window so when the file opens, its opening in the active window already, but nothing works.
Preferably, I need to be able to reference both windows, as my next macro will involve referencing 2 windows (one static - always open) and the other the loop above.
I'm trying to enter this very simple macro that is supposed to switch from one active window to another. However, I'm getting an error, which I can't quite figure out. Here is the
View 2 Replies View RelatedI have multiple excel workbooks open at the same time. I need to run a macro on one of them every 15 min but I need to return to the active window when the macro is done. How do I read what window is active then return to it when the macro is done?
Sub AutoSave()
dTime = Now + TimeValue("00:15:00")
Application .OnTime dTime, "AutoSave"
Windows("data.xlsm").Activate
Sheets("Data").Copy
Application.DisplayAlerts = False
ActiveWorkbook. SaveAs Filename:="c:excel" & Format(Time, "hhmmss"), FileFormat:=xlCSV
ActiveWorkbook.Close
Application.DisplayAlerts = True
Windows(1).ActivatePrevious
End Sub
I'm trying find a way in VBA to make a particular cell the top left one in the active window.
View 9 Replies View RelatedI have a worksheet with frozen panes (Row and Column). I have hyperlinks that jump to the this worksheet. Is there anyway to have the window position itself so the active cell is in the upper left corner of my frozen panes?
View 9 Replies View RelatedIn an open excel file, in the top left-hand corner, the file name is always "Microsoft Excel - Your File Name".
does anyone know how to have the top-left hand corner show "Your File Name" only?
I compared data between excel worksheets all the time, well recently my computer was upgraded from Windows XP to Windows 7 and since then I have not been able to figure out how to have an Excel file to open in the Excel window selected(or last active window) when I have more than one workbook open. Is there a way to do this?
View 5 Replies View RelatedI was just wondering if it possible to create a macro that permanently increases the height of a workbook by a set increment
I have several workbooks that i have the code below assigned to
[Code] ......
So when they open they resize to that set size and position. and what ive been doing if i need to resize (which is often) is just adjust the values in the above code.
Not that the way ive been doing it is difficult but i was thinking how great it would be if i could have a macro i could run that permanently adds 9 to the .Height and subtracts 9 from the .Top
I've been a somewhat casual Excel user and now need to do some VBA with a workbook that has several worksheets in it. In particular, the workbook has sheets that have been added "out of order", that is, the leftmost sheet is not the first one added to the book.
I need to write a VBA routine that looks "backward" from the current worksheet to "previous" sheets, i.e., sheets to the left of the current worksheet, but not to the right, so I can't refer to them as Sheet1, Sheet2, etc. and they actual display names that are not of that form anyway. I am aware of the "Worksheets" collection and the fact that I can "subscript" that to refer to the sheets in a left-to-right form.
I know that I can refer to the current sheet as "ActiveSheet" and I can determine properties like its name with "ActiveSheet.Name", but is it possible to find its index in the Worksheets collection? I'm looking for a function or property such that "ActiveSheet.Index" will allow me to refer to it as "Worksheets(Index)" and particularly to allow me to refer to preceding sheets as, e.g., "Worksheets(Index-1)".
If worse comes to worst, I can always cycle through the worksheets and check the name of each worksheet like this:
Sub Button1_Click()
Dim index As Integer
Dim strASName As String
strASName = ActiveSheet.Name
index = 0
Do
index = index + 1
Loop Until Worksheets(index).Name = strASName
MsgBox "Index of current worksheet is " & index
End Sub
And while I'm at it, is it possible in Excel to rename a control like "Button1" to something more sensible and mnemonic, like "btnRecalculate" or whatever?
Im looking to have excel grab the sheet name from the cell rather than manually clicking on the sheet each time. I have attached an example. I would like the formula in cells B5:7 and C5:7 to reference A5:7 rather than the specific sheet as seen in cell B5 --> =b!A1. I assume it will look something along the lines of ='(A5)'!A1, but that doesn't seem to work?
View 2 Replies View RelatedI have two documents with very similar information. Some rows are duplicates. However, there is one column of information missing from the 1st spreadsheet. Let's call them Sheet1 and Sheet2.
How do I tell Excel:
In Sheet1, IF A2 matches any field in Sheet 2, column A
Then: copy the data from the same row, in a different column (Sheet1 A3) and put it into a specific corresponding cell (Sheet2 A3)
Basically I'm trying to grab a product ID from sheet2 and put it in the matching cell, for that specific product. I have all teh product IDs in column A in sheet 2. The product IDs field in Sheet 1 is blank. I need to populate it with teh correct information from Sheet 2. But there are many moreproducts in sheet 2 than in sheet 1, so I can't just sort and do a copy paste. It needs to think "if the product exists in sheet 2, grab the info from sheet 2 and put it into sheet 1 for that product id."
I do have one issue I need to work on with a VLOOKUP formula.
I have a list of cities and counties that the VLOOKUP goes and looks at to pull information from.
The problem is that when there is a city and county with the same name (Example: Boulder and Boulder County) VLOOKUP always seems to jsut grab the one that is listed first in the list.
When you use the city name of Boulder, VLOOKUP always retruns the information for Boulder County.
Here is my forumula:
=VLOOKUP(qtProjAddress3,'City Mileage and Tax Rates'!A2:C518,2)
Is there something I can add to this formula that will allow it to return the proper city or county when the names are similar?
I would like to automate this process that I'm doing currently through the "Text to Columns" function.
Basically I have various cells that contain many worlds such as:
FRESH LUNETTE SOLEIL REES 1'S
INFOKUS SOLEIL LECTURE LUNETTES 1'S
KENSIE LUNETTE SOLEIL ELEORA 1'S
KENSIE LUNETTE SOLEIL ELIZABETH 1'S
KENSIE LUNETTE SOLEIL JAMES 1'S
KENSIE LUNETTE SOLEIL VESPER 1'S
KIDS LUNETTE SOLEIL HOUSTEN 1'S
KIDS LUNETTE SOLEIL MINNIE 1'S
My goal is to separate the first world from the rest of the words in one cell.
Since all of the words are seperated by space, I use the the "Text to Columns" function and separate the words, so that I can get the first word into one column. The rest of the words have to be merged again through "Concatenate".
What I'm looking for is a VBA code or Excel function that can identify the first world (which vary in character lengths) and put it into one column.
Is it possible to have a spreadsheet that when opened grabs data from every file with "*invoice" in the title in folder 2009 and all its sub directories and puts them into a list in the opened file?
The data needed is to be from sheet1, cells B16, C18, G18 from all the files
and i would like B16 put into A2, C18 into B2 and G18 into C2
Then the next sheets data B16 to A3, C18 B3 and G18 to C3 etc etc until all files with invoice in the title have been added.
B16 is a name
C18 is an address and
G18 is a postcode
This means there can be no duplicates so if opened twice there will only ever be 1 of the same address and postcode.
where i can grab a number from a cell it would ALWAYS be the last string item..
for example if cell A1 has the following text:
04/02 Card Purchase 03/31 Google*1873060297 Cc@Google.Com CA Card 5036 $500.00
i want cell B1 to hold just the $500
this wold always be the case for everything im trying to do
I have created an image of what i want and hopefully this will explain better.
This is very hard but it is something really important for the company i work for and have an idea on how its done but not enough knowledge to implement yet.
I've set a range as DD14 to G014, then using a For Next Loop asked the program to cycle through each cell in the range. Upon findng a populated cell, it stores several bits of data from that cell (already done) and then move up one cell to record the cell value (time) and again move one cell up to record the date.
Now I did get this to work using a clunky bit of code that used activecell.offset to move the focus up the two columns and a While .... Wend loop - but using a set range and cycling through each cell it does not appear to be possible - or am I wrong?
Set MyRange(1) = Range("D14:ER14")
Set MyRange(2) = Range("D23:GN23")
For MyCounter = 1 To 2
For Each Cell In MyRange(MyCounter)
If Cell.Value = "" Then Goto Skip_Cell
MyCell = Cell.Address
MyPosition = InStr(1, Cell.Value, ",")
If MyPosition = 0 And Len(Cell.Value) >= 3 Then
Tool_Number = Left(Cell.Value, 3).....................
Included in the macro, I would like for the macro to open up Word, grab a label template, that I have stored (have to navigate there), and then merge the data from the excel file into the Word template.
View 4 Replies View RelatedI have one worksheet with four columns of data. Column A is a well name, RA-0001, column B is the measured depth of the well from 0 feet to however far down it goes, anywhere from 4000 to 15000 feet, column C is the inclination of the well, column D is the Azimuth.
I have 500 wells from RA-0001 to RA_0500 or so all in this one worksheet, all the wells have varying Measured Depths associated to their well name. I need to create a macro that can separate the wells and either put them in a new worksheet for each well, ie. a worksheet named RA-0001, RA-0002, ..... ect. OR, and this would be nicer, a macro that can actually save all these individual wells as (Formatted Text (Space Delimited)) files with the associated well name.
Here is an example of what it looks like. The columns do not have a subject line to state what information is in each column because I dont need it in that format.
RA-0001
0
0.00
0.00
RA-0001
100
0.91
5.56
[Code] .......
Even just knowing how to create a simple macro that would take all the data from each well so I could manually copy and save them as new files.
I need in XL for a macro to run, open my word file, copy the entire thing and then paste it back in XL.
So leaving it general, Word.doc and Excel.xlsx
It's all because my company's POS system will only export to Word but everything we do is in XL.
How to grab data from a table providing it fulfills two chained conditions without using a concatenated key. For example:
I have this table and I want to get the combination Name + Color. :
A
B
C
1
John
White
2
John
Red
[Code] ........
The database is something like:
A
B
C
D
1
John
Red
3254.30
[Code] ......
I ended up using VLOOKUP() and a concatenated key like Name&Color but it slow down the sheet significantly as I have many records and also does not seem the most elegan solution. I tryied using OFFSET() nested with MATCH()but couldn't get it. Also tried something with ARRAY FORMULAS but I am not very proficient at them.
I am creating a billing sheet that has a column where charges are coded (to determine what they are for) and I want a "Grand Total" for everything followed by a breakdown "Total" for things with the same code. Is there a way to set SUM code to grab all numbers next to codes?? (EX. Column A is Billing Code, column B is Cost in $$; I want a total at the bottom that takes the different amounts in column B based on the various information/codes from column A).
View 2 Replies View RelatedI have a range of amounts in Sheet 1 from F7:Q13 and im using the find method to search for the active and non active values in the cell. Which means that if there's a value in the cell it will transfer the value in Sheet 2, if nothing is found in the cell the cells in Sheet 2 will return as nothing or null.
I think the problem lies on the FindWhat variable. Im getting a compiled error which im not sure what is it.
I've attached the spreadsheet so you get a better idea of the problem that i encountered.
I currently have the following macro running to set a chart's data values:
Sub C3Quarter12013()
'
' C3Quarter32013 Macro
'
'
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = _
[Code] ......
When I copy the tab and change some of the data within the cells, I want the macro refer to the chart on the current tab and the values in the current tab - as currently it refers to only "Chart 2" and the values in the tab 'Figure 2 - WE OPH'.
I've tried changing the sheet name to ActiveSheet.name but that doesn't seem to work.
I'm trying to grab a number out a cell and use only the number for other formulas.
So in cell A1 I have 2 LK, in cell A2 4 LK, and in cell A3 is 1/2 LK. I want to just pull the 2, 4, and 1/2 out without the LK and put it in the B cells. I'm sure this is easy but I'm thinking slow tonight lol.
A
B
1
2 LK
2
4 LK
3
1/2 LK
I have a data table that repeats as follows:
CITI
SCB
SCB
SCB
SCB
SCB
SCB
SCB
RBC
RBC
RBC
RBC
From the data above I need to make a new unqie list that would grab all unique entries.
CITI
SCB
RBC
I am trying to use the following guide:
http://blogs.techrepublic.com.com/howdoi/?p=111
Get stuck on "Listing A:
I have a workbook that has multiple sheets in it (or tabs?) I want to grab a value from one sheet and use it in an equation on another sheet. In the attached file say I have a value in cell A2 on the sheet labeled Friday. This number will be a variable number that eventually will vary based on a formula I have yet to develop in cell A2. I want to use this value in a formula on the sheet labeled Monday. Say in cell S4 I would like to put the following formula:
sheet"friday",time(0,A2,)+time(0,b1,0)
How do I, or can I, tell Excel to take a value from one sheet and use it in a formula in another without first copying the value from the one sheet to the sheet it is to be used in? What I am trying to do in make it so that as the value on sheet Friday cell A2 changes the value in cell S4 also changes to reflect the change from A2.
I want to grab everything left of the last occurrence of "." in a string, and in the next cell everything right of the last occurrence of "."
so say the string is 111.111.1.222
column 1
111.111.1
column 2
222
my current code (which works, but its messy) for the first cell is
I have got stuck on one piece of my code and having trouble fixing... Overall I am trying to find variable station name in cell L2 of Sheet 2 in Sheet 1 and then select and copy the data from the data in "cell L2 of Sheet 2" to the last entry of that row. I have attached an example test spreadsheet of the data and a macro is within Sheet 1 called test1. Please note that cell L2 in Sheet 2 will always be different station name and the station list in Sheet 1 will change with differing station name.
The code I am using is:
[Code] .....
The code that is not working and bringing up an error is:
[Code] .....
Attached File : Copy of Testexample.xlsm