how to automate my excel sheet i have got.
It has code which when you click the "Save" button it saves the document and then converts a copy as CSV with the same name.
I have been trying to automate this to run every 2 minutes but everything i have tried does not work.
I have tried using OnTime events but again does not work.
the following workbook open event which repeats a macro called "Refresh" every 30 minutes. However, there is another macro "tame_blph" that i wanna call at 12 midnight everyday.
Private Sub Workbook_Open()
Call tame_blph
NextTick = Now + TimeValue("00:30:00")
Application .OnTime NextTick, "Refresh", , True
End Sub
Is it even possible to have multiple Workbook open events and/or multiple NextTick or something?
I have a real simple question about what command to enter in order to stop a macro. All the macro does is "press the F9 key" every second. I use a button to start the loop but I am unable to find a way to use another button to stop the process. Ive copied my macro below:
Sub Go()
Calculate
Application .OnTime Now + TimeValue("00:00:01"), "Go"
End Sub
Private Sub CommandButton1_Click() "Go Button"
Application.OnTime Now + TimeValue("00:00:01"), "Go"
End Sub
Private Sub CommandButton2_Click() "Stop Button"
'????????????????????????
End Sub
I have the office 2007 Suite.I have a small business, where i sell online mostly. I'm creating a simple spreadsheet using Excel 2007, a workbook that has simple financial statements.
I'm creating a list of customers along with what they order, what they paid, how they paid, (paypal, cheque) etc, The first column is the invoice number, and each is unique to each customer. I add as i get orders. so my list of invoices has the following fields as column headers
Quote:
Invoice Number Customer Name, Paid by Paypal, Paid by Cheque, Amount, Cusotmer email, Customer address, There are more and i won't list them all. Now each time i send an order, i have to fill in the excel worksheet. I then create the invoice separately in Word 2007 which i print to send to the customer with the order in the mail. The third step is sending an email to the customer upon despatch, using Outlook 2007.
I don't want to use accounting software as that is too complicated for my simple little business. What i do want to do however is combine all three tasks instead of having to open different programs and copying and pasting between them. I'd like to be able to add the data to Excel either using a form (ideally) or direct, then i'd like to be able to click on the "customer email" field to send a confirmation email to the customer, and also to be able to have an automated invoice, either in excel or Word, which draws the information automatically for each invoice, so that i only have to print it.
At the moment i'm doing all 3 tasks separately, when i know they can be automated.
1. I have about 20 columns of data for each invoice, ideally i'd like to be able to enter them from a form as it gets cumbersome going through them all in Excel. The form could be either in Word or Excel, which would be easier?
2. Using the invoice number as the unique field, how can i create an invoice that would draw out the required fields from the Excel data automatically, just by clicking a button? I dont' mind using either Excel or Word, whichever is easier, and i do have Access too, but i don't know if i should use that, or not. I don't need to draw any other data. I only want to print invoices and send confirmation emails.
3. After printing the invoice and posting the order to the customer i sent them an email confirming. Again i have to manually enter or copy and paste data in the email, which i know could be automated, if i create a template, and have some fields that are automaitcally populated using the fields in the date already entered.
These fields ususally include the total paid, currency paid, address, name and how many ordered. All of this could be automated, but i'm not sure how to do it.
Is there a way to write a Worksheet_SelectionChange (ByVal Target As Range) event in module after creating a sheet in VBA? I constantly delete a sheet, then repopulate it with a new one that is empty, but I need to add some code that happens if they should change a particular cell. It worked when I ran it on a worksheet without refreshing, but as soon as I cleared and repopulated the sheet, it was gone. Is there a way to preserve this?
View 9 Replies View RelatedI am using Excel2010.
My sheet displays values collected from a database via an AddIn. The values are updated by pressing CTRL+G (AddIn function).
I have an OnTime macro running SendKeys "^G" to update the values every minute. When the values exceed a certain limit, a sound is played from a custom Alarm function.
This all works as long as the excel window is active.
But if excel is minimized or another window is opened, the values stop updating until Excel is active again.
When the values dont update the sound is not played and that defeats the purpose of the sheet.
I create a schedule based on abbreviations which is given to staff. Then this is retyped up with actual times and put in a schedule book that is the final product. I am trying to automate this change from one sheet to another
n = night shift = 2330-0800
D = day off
etc, etc
I have alot more abbreviations, but I hope I just need some advice and can figure it out from there....................
I am trying to automate a formula sheet used by truck drivers to determine how many hours are available to work as they cannot work over 14 hours in a day or 70 hours in 8 days. This must be charted daily, including days off. Taking two consecutive days off resets the 70 hour rule. So...
Col B2:B8 Enter the number of hours worked each day.
Cell C8 Sum B2:B8 except when there are two consecutive zeros entered during the seven days being counted, then count only from after the second zero. (ie. b2=6, b3=0, b4=0, b5=8, b6=10, b7=7, b8=11 only sum(b5:b8)). So I am looking for the formula to to sum under this condition.
I have a spread sheet (sheet2) that runs an add-in and does some calculations and arrives at a result which is a number in cell P6. All I have to do is type in a Stock Symbol in cell A1, and the sheet runs the add-in, calculation automatically. If I change the symbol in A1, the sheet re-runs the add-in/calculation and gives me the result for the new stock in cell P6.
Sheet 1, has a list of stocks, indexed in Column A.
Rather than me changing the symbol manually in sheet 2 cell A1, how can I automate it such that it will run the functionality automatically for each symbol in Sheet1 column A sequentially and then tabulate the results for each stock/symbol in Sheet 1 column B.
I tried to attach the sheet/book herewith, but could not. It is less than 100KB in size
I have a stock sheet of about 1500 items that is printed every 3 months for stock take.
The changes are then entered manually. I have set up a stock order sheet using VLookup and manually enter the items that need to be ordered. I'm wanting to know if i could use a macro to do this automatically and check only for the stock items that have changed (ie. items have been sold ). Stock levels needed on hand are usually constant but is changed from time to time so will have to be taken into consideration. I have included an example worksheet which would better explain what i have been doing so far. Also if possible could the macro generate a new worksheet for the order sheet and name it the corresponding date and month of generation.
I want to copy certain columns from Sheet6 to Sheet1. For instance, Column G in Sheet6 would be copied to Column A in Sheet1. I have over 20 columns that need to be copied. There are over 5,000 rows in Sheet6. Thiomething that I have to do daily.
View 2 Replies View RelatedIs there any way to automate the calculation of a single sheet? IE to calculate when moving from cell to cell after entering data without calculating the whole workbook and any other open workbooks.
View 9 Replies View RelatedI have a PDF sheet with info laid out like this:
Code:
Peter Paul Mary
Text Corporation
One two three street
Mars, New York 90000 USA
phone: (111) 555-2222
email: tellmewhy@yahoo.com
There are 10 names and addresses like this on each PDF page and the entire PDF file has 50 pages so to manually copy and paste each bit of info into separate cells on the spreadsheet's appropriate cells "Name, Company, Address, State, Phone, Email" e.t.c is too difficult.
I am required to fill up a sheet and provide to our Work force management team every week and it takes a lot of time from my day. I know this can be done easily with a code but I don't have enough experience with vb so can't write a good one myself
So here it goes -
I have a roster for my team (e.g. - sheet 2 "Roster") team members are required to work for 8 hours each day in their designated shift.
The codes in roster correspond to specific shift start time (e.g. - sheet 1 "codes")
Sheet - [URL]
I am required to fill up the sheet 3 "Staffing" each week for each half hour interval of a day that an advisor would be present for. So that means, I have to fill up 20 "P" for each member who is working on day from the time he would be starting his shift.
how to automate data from excel into word. This vba code takes every sheet from excel and puts it into a word document:
Code:
Sub CopyWorksheetsToWord()
' requires a reference to the Word Object library:
' in the VBE select Tools, References and check the Microsoft Word X.X object library
[Code]....
My problem is that it doesn't style the data into a table, is there any way to do this with the code I have posted? perhaps with a .Style code or something of that sort?
I am running Excel 2003.I have a spreadsheet with two worksheets, each worksheet has a Refresh button that runs a Data Extract Macro.The Data Extract Macro imports Data from another spreadsheet.I want to have the spreadsheet automatically run my Data Extract Macros for both worksheets at a specific time each week. (Scheduled task) Regardless of whether or not the excel file is open.
View 4 Replies View RelatedI'm looking to automate the process of repeating an excel worksheet (which is set up with various tables and formulas) for various countries.
I have a referenced list of countries set up in worksheet 1. So for example: in worksheet 2 I have a sheet set up for Albania - and I want to repeat this for another 100 countries. . Can I use the reference list of countries to auto-repeat worksheet 2 for worksheets 3 - 102 and auto-title the worksheet tabs? (E.g. Brazil, Canada, Chile..............)
I am trying to reference a date in a formula that points to a sheet name.
See Below.
A B
November 11, 2005 ='11-11-05'!$v$35
How can I automate the date to pull from column A and place that value in the date for column B? Column B is the name of a different sheet in the workbook.
A typical Design Matrix is shown in the attached Workbook. There are two domains of Merged Cells that make up the Headings of the Matrix; FRs (Functional Requirements) and DPs (Design Parameters). Given a Hierarchical List of FRs specified by the User, the User would like Excel to bulild the Matrix Hierarchy of FRs automatically (going down the Worksheet). The DP Hierarchy is the same hierarchy, except transposed and reflected across the Worksheet. The attached Workbook has up to seven (7) levels, but the ability to go create up to 10 levels is desired.
View 8 Replies View RelatedI am trying to automate a simulation with the crystal ball add-in. Specifically the simulation will go through a set of workbooks in a directory - run the simulation (monte carlo x1000) and extra the "trial values" data in another spreadsheet.
I have tried to find crystal ball documentation but I'm not having any luck.
I have two spreadsheets. one contains a master list of computer names. the other contains a list of computer names to be removed from the master list (exception list).
I need to go through the master list and remove any computer names that are on the exception list- for example, if 'computer1' is in the exception list, i have to find and remove 'computer1' from the master list.
The exception list is quite long, and I want to automate this process if possible. not sure how to achieve this.
I'm using Excel 2007 Standard.
Code + Button to automate certain row heights.
1. Starting in row 5 the row height is to "30"...every 17 rows after row5 to have a height "30"...next row 22 is "30" , then row 39 is "30" and so on...
2. All rows in between row height "30" will be with a row height of "11"
3. Can this be associated with a button....
4. where would i place this code.....
Version:Excel 2010
I have a macro which makes an renames a new sheet for me using a button, how can I copy macros currently found in only one sheet to this new sheet? Mainly used in formating and formula display.
View 5 Replies View RelatedI need to declare an event that happens when a worksheet is renamed
the code that would trigger the event is
I have a worksheet called "Roster" with a list of names from D7:D17 each person on the list has a separate sheet that is called by their name. the sheets are not linked up. the names were just manually typed into the list and into each sheet name.
If any name is changed on the list on "Roster!D7:D17" i would like the sheet that has that name to change as well.
This is the code that i was sent, but i do not know enough about VBA code to understand exactly what is happening, thus i cannot diagnose the problem. I placed the following code into the page "Roster" (by right-clicking and then pasting the code) then i closed, but nothing happens. I can mess with the code and get an error message. but that is about it. error messages usually say something about the debugger, or runtime error or something.
there is the
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OldVal(0) = OldVal(1)
OldVal(1) = Target(1).Value
End Sub
I wonder how to capture de event when one choose to delete a sheet. Similar to vbyes or vbno?? Now it inmediatley goes to my sheet_deactivate_event, but then I don't know if it is beiing deleted? I mean capture if you do righclick sheettab
View 2 Replies View RelatedI have a Combo Box on a Work Sheet that the user selects a subject from. When the Combo Box content changes I need it to run a macro. Ive tried putting this in the WorkSheet file for the sheet
View 3 Replies View RelatedI'm making a macro to show or hide rows depending on whether or not a checkbox is checked. I'm using Wingdings font checkboxes, not actual form controls. The question: My macro can't find the last row of the used range on the sheet. This is in the sheet's code, set to fire when the selection changes.
Normally the line in blue works just fine, but it's not working here. In the attached workbook, the macro thinks the last used row is row 19, when it's actually row 21. What's going on?
One of the things it does throughout the course of the run is unprotect the worksheet, do what it needs to and then reprotects the sheet (using a password). On the second run, the sheet protection input box is shown and I am required to input the password before the code will execute.
The code that I have written uses an Add-in that pulls data from a database. As part of that retrieval, the other add-in protects the sheet without a password. Would that be causing my error?
why this code does not work when the worksheet is changed between range "B1:F5"?
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1:F5")) Is Nothing Then
With Range("B1:F5")
Cells(Target.Row, 7) = Cells(Target.Row, 6).Value + Cells(Target.Row, 5).Value
End With
End If
End Sub