Macro Knowing Currently Opened Sheet

Mar 19, 2007

below you will see my macro. I have a slight problem, everything works perfect, except when I change file names. Basically I keep an electronic backup every day of the file I create. So today's file will be named DOH031907, tomorrow DOH032007, Wednesday DOH 032107, etc. etc. I use the same sheet, including the same Macro every day so it does the same thing. Basically everywhere you see "DOH031907Test.xls" I want it to put the current workbook file name there. I'm new to this kind of stuff but it seems like there is an easy solution.

Sub DOHComplete()
' DOHComplete Macro
' Keyboard Shortcut: Ctrl+Shift+T
ChDir "K:PICDailyMetrics"
Workbooks.Open Filename:="K:PICDailyMetricsGMDEMAND2.XLS"
Sheets("EDI Demand").Select
Sheets("EDI Demand").Select
Sheets("EDI Demand").Copy Before:=Workbooks("DOH031907Test.xls").Sheets(3)

Added Code tags - Moderator

View 9 Replies


Macro To Format The Auto-Sum Row Without Knowing What Row# It Will Be

Mar 20, 2009

I currently have a macro setup that puts Auto-Sum data in the row below the bottom-most row of data. What code would I need to format this Auto-Sum row with the following parameters automatically...

1. Merge & center F & G in the Auto-Sum row with the text "Totals".
2. Change the row height of the Auto-Sum row to 44 pixels.
3. Center the vertical alignment of the Auto-Sum row.
4. Put the thickest border around the Auto-Sum row in cells A-K.

View 14 Replies View Related

Copy Down Formula Macro Without Knowing How Many Rows To Copy Down

Feb 18, 2008

I have a macro that inserts a formula on the first row and then copies it down. The problem is the row count is variable but I know it won't exceed 5000 so I just copy it down 5000. For the blank rows I get the ugly #NAME, #VALUE, etc errors.

I know there are ways to supress those error messages, but how about a way so that the macro checks to see how many rows are populated so it doesn't copy down on the blank rows.

View 9 Replies View Related

Auto Run Macro When File Is Opened But Macro Stored In Personal XLSB

Mar 4, 2014

I am looking for a macro that i can store in my personal.xlsb. what i need is pretty much is something like this

private sub workbook_open
if "inventorysummary.csv"
then "personal.xlsb!capacity"
end sub

I only need it to run just for this file and i cannot place it in the file due to it gets replaced every day. Which if it didn't get replaced. I know how to do auto opens when the file stays the same I am just unsure for this.

View 1 Replies View Related

List Sheet Names From Opened Workbooks

Dec 2, 2006

This code loops through all columns in all sheets in all workbooks of a specified directory.

My copy paste to column C in "Loop Folder" works well thanks to this forum especially wigi

I want to paste the file name once in Column A and have it repeat for each new file opened. My code only does this for the first one as I simply can not fathom the coding

In column B I'd like to paste the sheet name once as they get opened.
I am struggling here as well on Sheets.Name coding ....

View 9 Replies View Related

Error Selecting Sheet In Opened Workbook

Apr 4, 2007

I have a main workbook that uses a macro to open a workbook provided to me from another department. This workbook contains a sheet that is basically a copy of the sheet in the main document and has the same sheetname and same codename.

The macro should copy the sheet data from the site workbook to the sheet in the main workbook. However, when i tell the workbook to select the sheet in the site file using its codename it errors....

View 6 Replies View Related

Run Macro Once Workbook Is Opened

Oct 2, 2012

I want my macro on the IR Tracker sheet to run once workbook is opened. Is this possible?

I have attached the workbook for better understanding.

View 9 Replies View Related

Run Macro In Opened Workbook

Oct 31, 2006

An external program opens and closes Excel Workbook (OLE). What is the VBA code so when the WB is opened, Macro1 will run before OLE closes that Workbook?

View 6 Replies View Related

Launching A Macro That's Stored In A To Be Opened

Nov 15, 2007

Sub test12()

Set wbABC = Workbooks.Open("C:ABCwithMacro.xls")
Call abcMacro 'this macro is stored in ABCwithMacro.xls
'error - sub or function not defined on 'call abcMacro'

End Sub

View 9 Replies View Related

Auto Run Macro When File Is Opened

Jun 12, 2007

I am using the following code and putting it into a module.

Private Sub Workbook_Open()
MsgBox "Hi, thanks for opening me", vbInformation, ""
End Sub

when i open the file and click on allow macros it does not run.

View 2 Replies View Related

Copy/paste From File Opened By Macro

Oct 16, 2008

I have a macro which opens two different hyperlinks in excel. Opened workbooks have are named like 1.asp and 2.asp. Both contains only one spreedsheet. I need to copy these spreedsheets to the Workbook, from which they are opened.

View 4 Replies View Related

Stop Macro: Messageboxes May Only Appear When The Workbook Is Opened

Feb 6, 2007

I´m using this code to create a messagebox±

Private Sub Workbook_Open()
Dim c As Range
For Each c In Range("E2:E126")
If c > -15 And c < 0 Then MsgBox c.Offset(, -3), vbOKOnly + vbInformation, "LET OP! Einddatum contract nadert!"
End Sub

The messageboxes may only appear when the workbook is opened. The problem is that they appear when I change something in the workbook.

View 4 Replies View Related

Naming New Tab Without Knowing Its Name

Feb 16, 2012

Is there a way to name a new tab without knowing its name. For instance, when I create a new tab, it could be named "Sheet2" or "Sheet3", but there is sometimes no way to know. Is there a way to edit the name of that new tab without refering to it by name?

View 1 Replies View Related

Knowing The Exact Datas

Feb 19, 2010

My date of birth is 09/08/1970.I want to know that as on today,which years,months and days I have passed?

View 3 Replies View Related

Reference Workbook Without Knowing Name

Jun 26, 2007

I have a master workbook, meeting1.xls which is used on a daily basis. All of the macros saved in this workbook naturally refer to Windows("Meeting1.xls").Activate.

However, I want to be able to open Meeting1.xls save it as a new name (for today's details) and then populate well as have the Macros pick up that new file name (rather than always looking for Meeting1.xls.)

I've tried saving Meeting1 as a template file and that didn't seem to work either. When I save it as a new name, the macro always searches for the original name.

View 9 Replies View Related

Knowing The Worksheet Number Or Letter

Apr 5, 2007

Without using VBA code, is there a way to display or find the worksheet number of the active worksheet you are viewing? All my sheets have names, and I have a lot of them.

When I want to loop through a set of them with code, I want to know what numbers they are beforehand.

View 9 Replies View Related

Deleting Sheets Without Knowing The Names

Jan 19, 2009

Is it possible for a macro to delete any sheet that within a workbook that does not match a specific list of sheets. For example a workbook will always have Sheet1, Sheet2, Sheet3, Sheet4... but it can have many other sheets added to it. The names of the sheets can be completely different however the sheets that I want to keep will always have the same name.

View 9 Replies View Related

Serialize A Variable Without Knowing How Many Instances Are Required?

Sep 29, 2009

I'm sure there's a way to do this, I just haven't put my finger on HOW just yet. I have a list of stock tickers which can vary from 1 to say, 10. I need to use each of those 10 tickers in the subject line of a message. I thought I could do something with SPLIT, but that's not working for me. Here's what I have so far: u = the count of tickers. Column O contains the list of unique tickers

View 4 Replies View Related

Formula Rectified And I Find Myself Not Knowing How It Works

Mar 20, 2007

I have a nifty formula brought to me by one of the excellent members on this board. It works perfectly for what I want, but I must admit I cheated, I have a problem that was similar to the problem this formula rectified and I find myself not knowing how it works.

=IF('IIC Transfer Status'!O139="x",MIN('IIC Transfer Status'!K139+10-WEEKDAY('IIC Transfer Status'!K139-{1,3})), "" )

What Im wondering is if someone could enlighten me as to what is happening in this function, so Im not as blind to assume it is magic.

Eventually I need it to be customed tailored to work in this way: I have a column of dates, I want it to look at column A and if there is a date in it ADD 4 WEEKDAYS (business days) and return the resulting date.

i.e date in column a is march 19 plus 4 business days. Result in column B march 23.

If someone can explain how this string works that would be great, or if someone could just post a suitable function, that would work for now.

View 9 Replies View Related

File Download Without Knowing The Full Url (only Indirect Url Available)

Aug 13, 2008

have a link such as When opening this link in a browser the DownloadFile window popups and the file can be downloaded.

I would like to be able to download the file without involving the DownloadFile pop up (since some users have auto open enabled thus the downloadfile pop-up is not always shown)

I have tried URLDownloadToCacheFile and similiar methods but they fail as they dont have the direct link to the file.

So my question now is how can I extract the direct url?

There should be a way to capture it since it will be used in the downloadfile dialog.

View 9 Replies View Related

Lookup Data Without Knowing Return Column

Aug 25, 2006

Got a bit of a stinker here. (Well, it is for me anyway. I haven't used Excel in months)!

I want to use a VLOOKUP function (I think) to retrieve data from a list of Exchange Rates on a separate sheet.

The user enters a 'Period' number, selects an 'exchange' variable (the blue text cells). The hope is to lookup the data on the 'FX RATES 06-07' sheet and return the result in the green cells.

I've tried using VLOOKUP but one of the arguements specifies that you nominate a column to return the information - that's the problem, we don't know that information when asking Excel.

View 4 Replies View Related

Automatically Open File In Specific File Path When Another Sheet Is Opened

Mar 20, 2014

I want to open a specific sheet and refresh only said sheet when i open another sheet for example x.xls

So opening x.xls will automatically open y.xls

I've tried this in the workbook code area but it doesn't do anything.

[Code] .....

View 1 Replies View Related

Formula To Calculate Speed Knowing Distance, Destination Time And Checkpoint Times

Dec 2, 2008

On my spreadsheet i have a bunch of variables:

A1 = Arrival time at Checkpoint 1 - 5:53:08
A2 = Time to destination from Checkpoint 1 - 1:10:18

B1 = Arrival time at Checkpoint 2 - 6:00:56
B2 = Time to destination from Checkpoint - 1:02:30

C1 = Total distance from Start - 2.83
C2 = Arrival Time at Destination - =SUM(A1,A2)

D1 = Speed determined from the other variables
D1 is SUPPOSED to equal 25.00

It also is where I am stumped!!

Not only can I not figure out the math but also how to format the function to get the right answer.

ps: Arrival Time at Destination is only included in case it would be useful to find the answer!

View 7 Replies View Related

Created A Form That Pops Up Using A Macro When The Form Is Opened

Apr 10, 2009

I have created a form that pops up using a macro when the form is opened. It is a simple form that uses optionbuttons in a group to select Intl or Dom.


1) How do you get the form to close once a button is selected?

2) The user can close the form without selecting a button (the X at the top).

3) How do you get the data selected onto the excel spreadsheet?

View 9 Replies View Related

Log Of Who Has Opened File

Jan 7, 2009

Is there a way to create an outside log of who has opened an excel file and when?
I am looking to add some accountability to a project.

View 3 Replies View Related

AutoSave Only When Opened

Jun 21, 2013

I have macro enabled workbooks that the contract number increases by one everytime they are opened. Is there a way to make it auto save though when its opened. Right now I have to click save as soon as I open it. If I do not do this when I open it, when I open it again the contract number will stay the same as it was, because I save it at the end after I put all customer info in.

View 8 Replies View Related

Identify The Opened .xls

Mar 30, 2007

I am using ADO connections in my program to insert the datas in the excel. I want to find if the excel is locked by another user. How do i do this in ADO commands.

View 6 Replies View Related

Macro To Copy Current Sheet, Create, & Rename New Sheet From Current Open Sheet

Oct 27, 2008

EXAMPLE: Complete Sheet called "Day1". When day1 is complete you click on button and it then copies itself and creates and renames new sheet to "Day2", then when "Day2" is complete you click on button and it then copies itself and creates and renames new sheet to "Day3", and so on and so forth to "Day30".

View 9 Replies View Related

Use Opened File In Formula?

Aug 18, 2014

Is it possible to insert the filename automatically in formula after the input from the user?

I want to ask the user to input a filename and afterwards, call that filename in an Index-Match

I have the below formula with the sample filename:

ActiveCell.FormulaR1C1 = _

View 1 Replies View Related

Copying From One Spreadsheet To Another When Both Are Opened?

Nov 15, 2013

How do I get one sheet to update from the other when I open both sheets. Somethign like an auto update. If I had two tabs on the same spreadsheet I would just use = but I can't use that with two different sheets.

View 3 Replies View Related

Copyrights 2005-15, All rights reserved