Hi All, This is my first post.I am a excel newbie and i need some help with this code.I have an excel workbook which has 10 worksheets and now i created a new workbook with just 1 worksheet from the original workbook and now i want the new workbook to be updated with data from original workbook whenever any updates or changes take place.I can't use links as i am not getting the structure reflected in the new workbook. Here is the code used but i am getting an error.anyone's help on this issue is truly appreciated or any other suggestion to tackle this scenario. I added the code VB: Macro1 Macro ' For IT ' ' Keyboard Shortcut: Ctrl+l ' Dim mdteScheduledTime As Date Sub RefreshData ThisWorkbook.UpdateLink Name:='Type:=xlExcelLinks mdteScheduledTime = Now + TimeSerial(0, 3, 0) Application.OnTime mdteScheduledTime, 'RefreshData' End Sub Sub StopRefresh Application.OnTime mdteScheduledTime, 'RefreshData', False End Sub If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines but it is giving me this error Runtime error 1004 Method 'UpdateLink' of object'Workbook' failed. So basically i included this code in the worksheet where it needs to get updated with data from original workbook whenever any changes happens in original workbook.So the location i gave was for original workbook. Is it possible to share a workbook with macros and have the macros work properly. I have one spreadsheet that needs to be shared by many users. We want to all be in the same document at the same time saving changes.

1. See More On Stackoverflow
2. Give More Feedback

The real catch is that the workbook has macros. I used the 'Share Workbook' function under tools. At the end of the setup, it told me that the macros would not work properly.

I am writing a script in Excel VBA where the Workbook is supposed to open with a UserForm. Specific workbook invisible Excel VBA. Hide Excel Application with.

1. I want WB2 hidden. Private Sub Workbook_Open(). Excel VBA ActiveWindow.Visible hiding the wrong workbook. Excel VBA import multiple matches into different.
2. Problem after Hiding Excel Workbook behind a VBA. - Means that user can click a link and it opens a. Problem after Hiding Excel Workbook behind.

The odd thing is that everytime I try to click on one of the macros I get an error, but yet the macro still performs the function. So essentially the macro works, it is just a pain because you have to click 'end' everytime on the error screen. Any suggestions? I have never really used VBA and so am completely stuck at this problem. I need to create a macro which auto-populates a master worksheet from the individual user sheets in a shared workbook. Sheet 1 is the master sheet 'Team Stats'. There will be an undetermined number of individual worksheets to accomodate new staff.

See More On Stackoverflow

Each worksheet will be identical, using columns A-I with row 1 having the headings: Date, Name, Reference, Value, Price, Age, Purchased?, Destination, Add. Products (the last 3 columns will have a drop-down list which will be used to enter data into the cell). There will be a varying number of rows in each of the individual sheets. If possible I would like the macro to run every time data is entered into one of the individual worksheets. If this is not then it would be fien to update every time the workbook is opened. If anyone can help it would really cut down the time I spend collating these stats every day!

Hi all, The following code is placed in workbook 'A' and is used to open workbook 'B'. These workbooks will now always be housed in the same directory and i want to change the code to use a relative path reference by determining the path of workbook 'A'. Here's what I had: Sub incomestatement Application.WindowState = xlMaximized Application.Workbooks.Open 'C: Documents and Settings. Workbook B.xls', UpdateLinks:=xlUpdateLinksAlways End Sub could you suggest how to change this to use a relative path reference? We have a workbook that does not allow us to use the Move or Copy command. When we right click on the worksheet and select Move or Copy, we are able to check the box to make a copy, but when we click OK nothing happens. We have checked to make sure that the workbook and worksheet: 1.

Are not protected 2. That there are no hidden worksheets 3. That there are not worksheets that exist with the same name 4. That not all the worksheets are selected There are only two worksheets in this workbook.

Any ideas of why we are unable to make a copy of this worksheet within the same workbook or to another workbook? Hi, I'm trying to get some macros to combine data from two open workbooks, and I can't figure out the commands to switch from the active workbook to the other open workbook and back.

I'm new to VBA and just learning the ropes. All I can figure out is how to switch to another workbook with an exact name. If I record the macro to go to a recent file, for example Window 1 (filename), the code that I get in VBA is Windows('filename').Activate. This doesn't translate to when I have two different workbooks with different filenames open.

I'm a bit confused myself. Maybe just a list of basic workbook-switching techniques or commands would be useful. I have a VBMacro Excel file loaded on a Server that numerous people access. A Macro in this file creates a Copy of a specific Sheet within the Active Workbook and I want to Save it to the individual's Desktop. How do I find out what the current User's desktop folder path is each time the Marco is run by a different User?

Example User's path: 'C: Documents and Settings jfarc Desktop' Where 'jfarc' is the name of the current User which, will of course change with every different User that runs the Macro. Also, is there a way to pull out of Excel what is the current User's 'Options General Default File Location' entry? Which may differ from the above directory. I am familiar with and use the following coding for Opening/Saving files to the current directory of the opened workbook, but it only gives the path of the existing Excel workbook and not the current User's Directory Path: Dim wbThis As Workbook Set wbThis = ThisWorkbook ChDir wbThis.Path. I have an Excel workbook that was created by a former coworker. It includes a macro that, among other things, displays a message box about the 2008 file. The macro runs as soon as the file is opened.

I'd like to access that macro to correct the date to 2011 and see what else, if anything, it's doing for me (it doesn't appear to do much). I can find references to creating macros to hide and unhide rows/columns and I found ways to delete all macros in a workbook, but I cannot find anything about unhiding a macro without knowing its name. Does anyone know of a way to unhide this macro? I have workbook in which I want to save a specific worksheet to a new file with only the values saved - all data in this worksheet are references to cells on another worksheet, which is using VLOOKUP to pull data from a database. Found the following code and it gets me close, but it copies the cell references, not the values.

It also allows me to specify the file name from a cell reference. I want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN).

What modifications do I make to this to get this to work per above requirements? Sub CopyMe Dim SaveMeAs As String SaveMeAs = Sheets('Sheet1').Range('B2').Text Sheets('Sheet3').Copy ActiveWorkbook.SaveAs Filename:='C: My Documents ' & SaveMeAs End Sub. I'm having a problem in a workbook with several ActiveX command buttons. I had been using the form control buttons to run macros, but the boss wanted each button to have it's own, different color. So I removed the form control buttons and created new ActiveX command buttons. I got into the button properties and set the background colors.

I added the Click code to run the macros when the user clicked the buttons. All of the buttons were working fine.

Then I saved and closed the workbook and went to lunch. Now when I open the workbook, the buttons don't work! When I click them nothing happens. They appear frozen. They don't even seem to click. No error message. If I right-click the button in Design Mode and select Properties, I get sheet properties not the button properties.

I can't seem to locate the command button properties any longer. I still see the button name 'cmdButtonGetInfo' and '=EMBED('Forms.CommandButton.1',') in the name box and formula bar. The odd thing is if I create a new button it works fine until I save and close the file. When I reopen the file none of the buttons work.

It's like the buttons are being disabled when I close or open the file. Any suggestions? Hi Excel experts, I am working on a worksheet, and would like to have an entire row transferred to another worksheet based upon a certain cell's value. Having searched through the many solved threads on this site, I attempted to customize some of the code to my worksheet. Not being proficient in Macro/VBA, I failed. I figured I would ask for help, and see if anyone can provide what I am looking for. Basically, I am attempting to construct a 'work in process' file, and would like all completed work to be deleted from the work in process sheet, and transferred to a sheet that contains a running list of all work completed.

Attached is an example of what I am working on. I would like Column Q (Status) to determine if a row can be moved to a sheet that contains the list of all work completed, and eliminated from the original sheet.

Once Status is updated to 'Complete', it would be helpful to be able to run a macro/click a button that would perform the transfer. Can anyone assist? Hello I'm quite experienced Excel user. I've never come across this problem but tinkering in every conceivable way within Excel settings and the solution has eluded me.

I have added a worksheet created elsewhere (it is a form I need printing, with the data coming from 2 sheets I have created from scratch) which has pre formatted cells for Date and Client Name etc. When I try to reference the cell in this added sheet from my 2 sheets, instead of the result, it always displays the formula, not the result. I have tried doing it from one of my sheets to reference to this new, and that displays the result and not formula.

I can't imagine why it's doing this and I've never seen it happen before. Formatting cells, giving cells names rather than the usually adequate of reference to the Cell Number doesn't change things. I wonder if I've picked up some legacy protection from the original form but can't see anywhere in the tools etc that's obvious.

Give More Feedback

There's about 50+ cells that need referencing and I got to get this done for work. Please help me.

This is my first need to post on a Excel Forum as I've always found help or answers from other peeps or internet but this one is making me scratch my head big time. I have a workbook that has compliance dates in columns 'F','G' and 'H' from row 7. What I need is when the date in either column comes within 30 days to auto send an email, address in column 'A', recipients name in column 'B'. And then place todays date in column 'P'. ALso need to send a follow up email when either date comes within 7days and then place todays date in column 'Q'. If there is a date in column 'P' then don't send email. If there is a date in column 'Q' then don't send follow up.

Can this be done without the users intervention and each time the workbook is opened. Thanks in advance for any assistance. I have a Workbook with 2 sheets, the first one is the data entry and the second one contains all the calculations and confidential info. I have one staff member that does the data entry but I don't want them to see the 2nd sheet.

I know I can hide the sheet then protect the entire workbook which does work, but is there an easier way so that the second person doesn't have to keep hiding/unhiding the 2nd sheet? It would be perfect if when you tried to click on Sheet2, it asked for a password. I have set up a workbook that is sent out to lots of different users.

They each keep and use their own copy. I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them. I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine. Here's the code that does it. Code: Sheets('WELCOME').Select Range('A1:N18').Select ActiveWindow.Zoom = True By repeating this code for each worksheet, I can make each one be zoomed just right. However, the file contains 8 sheets that are all identically laid out, except the number of rows is different. What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout.

I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I'd like them to have a consistent appearance. I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that's not ideal either, because some of the row heights vary from sheet to sheet, and again I'll end up with different font sizes. Anyone know how to do this?