Macro To Automate Updating Of Worksheet
Oct 1, 2008
Here is my scenario:
4 step process or more?
1)I have a text file (with delimiters) that I want to be copied over to an existing workbook (because of the header)
2)So now I have a temporary workbook that includes the header and the data from the text file. I want this temporary data to be transferred or copied to a sheet in another workbook(s), wipe out previous data and store the new one. Save the workbook(s) and close.
3)Close temporary file. Don't save.
4)I want this process to be automated -- run it daily everyday at say, 6:30 in the morning.
View 9 Replies
ADVERTISEMENT
Apr 15, 2007
I have work sheet where certain cells are updating constantly
the cells are D7, D10, D13, D16, D19, D22....... all the way down to D60
Now the values in these cells are changing every few seconds..... what I'd like to do is track the direction the cells are moving in a seperate sheet.
Lets say the cells displayed the following values for example:
D7=2.5
D10=3.4
D13=5.9
D16=8.2
D19=11.6
D22=13.8
D25=16.1
View 9 Replies
View Related
Feb 3, 2009
when I manually delete a row or column in my worksheet - the macro target addresses do not update.
For example, I have a Target.Address of B5
If I delete column A from the worksheet, I want the macro Target.Address to update to A5. Currently the Target.Address remains B5 even though the cell I am interested in has now moved to A5...
Here is my worksheet
Sub Macro1()
If Range("B5") = "Yes" Then
Rows("6:10").EntireRow.Hidden = False
Else
Rows("6:10").EntireRow.Hidden = True
Range("C6:C10").ClearContents
End If
End Sub
Here is my workbook
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B5" Then Call Macro1
End Sub
View 9 Replies
View Related
Oct 15, 2009
I have developed an application (all written in Excel) that can do comparisons between up to 5 scenarios (A.... E, with A being the Master that the others are compared to. Any one can be designated “A”.
All scenarios are the same format, and all have an identical Transfer Worksheet, to enable the transfer of data from the others to A for comparison to A. So, for A the transfer worksheet becomes an Importer, wherein the user pastes-special – link into array, always B21:E35, one column at a time, for each of up to 4 scenarios B…E.
For the others, Transfer worksheet becomes an Exporter, available for the user to copy the data from each of scenarios B….E, always cells B41:B54....
View 14 Replies
View Related
Mar 10, 2009
OK, two files attached in the zipfile, pricelist-half.xls and pricelist-full.xls
The background is that i exported a file from our warehouse system so we could update prices and re-import it. That is the pricelist-half.xls
After our staff had spent a few days working on it, it came to light that (as the name suggests!) its only about half the products that should have been exported that are on the list.
Hence the second file, pricelist-full.xls which as its name suggests is the full
product list.
What I need to happen, to make this as painless as possible, is somehow for the items/rows that are on the full list, but not on the half list to be inserted to the half list but also have a yellow background for their rows so they stand out clearly.
View 10 Replies
View Related
Jul 13, 2008
I can set up one query to take the parameters from say cell E1 and F1 and post the results of the query in cell A1.
The next query I want to take parameters from say cell E20 and F20 and post results in cell A20.
I need to run this several hundred times and am limited by the webite URL to 2 parameters.
I can't work out how to automaticaly change the URL via a macro so it adapts it with the changing parameters in my worksheet.
View 12 Replies
View Related
Aug 6, 2014
I'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..............)
View 2 Replies
View Related
Jan 21, 2007
I've been trying for a while now but I can't do it so I was wondering if anyone could do it:
1) I am trying on the "customer detail page", in the " date due" column automatically update with the book/DVD.
The database has been shortened drastically to be able to be able upload
View 9 Replies
View Related
Sep 11, 2013
I have worked out how to get my macro behaving the way I want however now I would like to automate the whole process. Basically I would like the macro to work its way down a column (in this example column F) until it has completed and there is no more data in the row before the last.
Here is a code snippet of my macro
Code:
Keyboard Shortcut: Ctrl+a'
If ActiveCell = 2013 Then
ActiveCell.Offset(, 2).Range("A1:E1").Select
Selection.Cut
ActiveWindow.SmallScroll ToRight:=24
ActiveCell.Offset(0, 24).Range("A1").Select
ActiveSheet.Paste
End If
[code]......
View 1 Replies
View Related
Apr 18, 2006
I have several tabs (worksheets) representing purchases for a specific year.
There is one tab (worksheet) that is a Summary of all purchases for all
years. How do I get Excel to automatically update the Summary worksheet
anytime one of the other worksheets are modified?
View 9 Replies
View Related
Dec 14, 2011
I keep track of returns for my company and I am pulling a CSV file with all of the information on it.
I want to be able to format the info and create new columns for info I enter in to the sheet, and be able to import the updated CSV file into my already formatted sheet.
The part that I am wondering about is, after I import my first CSV file(sheet1) do I need to create another sheet(sheet2) and format sheet2 and have it pull the info from sheet1, then when I update workbook1 the data will update in sheet2?
Or, is there a way to just import the new data a single formatted sheet that I created from the old data and have it just add the new data without messing up any of my formatting?
View 2 Replies
View Related
Apr 24, 2007
I have one work sheet (worksheet #1) that everyone in the company uses. With this worksheet they submit orders to my department.
When I receive it (worksheet #1) I have a macro built in so that all i have to do is click a button and the sheet get's logged onto a certain workbook (workbook #1) on the appropriated tabbed sheet.
On my log workbook (workbook #1) I have two tabed sheets "2005" and "2007", and all of the worksheet#1's go to the next line on "2007"
On occasion a sales person will open up an old worksheet and the macro will log it onto "2005".
I want to create a macro for (workbook #1) that goes something like this:
If any files attempt to write to sheet "2005"
do not allow or (False)
move line item to sheet "2007" to the next open line.
View 9 Replies
View Related
Jul 10, 2014
I have got the general principles of macros and VBA downpath.
Excel Example.xlsx
The document I'm working with is confidential, so this is an example. Basically, I'd like to string together the content of all cells from the second column associated with each company in the first column (separated by comma + space). In the original document, the companies are located in B2 to B8486, and the key words are located in D2 to D8486. My example uses ampersands, but I seem to understand Concatenate would yield the same results.
View 9 Replies
View Related
Feb 15, 2014
I am trying to write a small macro involving Len and Left formulas. This will take input from two cells and and give the value on a third cell. I know how to do this on excel. This goes as follows:
Value on A5 = HB_AM_1
Value on B5 = 1
My formula to extract the specific text =LEFT(A5,LEN(A5)-LEN(B5)-1).
My result on D5= HB_AM
Here, my inputs are clearly defined and I can write this formula on cell D5 to get the result. I can copy this down to the end of my data and I can get my result.
My challenges: I need to do this for various reports where the data extends to various rows. I think looping will solve the problem, but not sure how to do it. The value on cell B5 will always show the last number at the cell A5 (1 in the eg above). So, if the number in A5 is 10, value in B5 will also be 10. This is already there in my data, so I do not worry about this part.
View 5 Replies
View Related
May 20, 2008
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.
View 11 Replies
View Related
Oct 6, 2008
I have a macro in Module1 of workbook "BAU" called "ExtractToSheets" which exports data depending on the contents of a cell (Thanks royUK et al)
At the moment however, you must run the macro manually to export the data.
Now, i have put some simple validation in column K of worksheet "BAU Data" within the "BAU" workbook. Whenever a cell within a predefined range with column K is changed to "Yes" i want the macro to run AND i want the row the cell is in to be locked so it cannot be edited further by the user. I have had too many users mess up previous sheets i have made to collect my data, and i wish to make this one somewhat more idiot-proof!
Any pearls of wisdom folks?
I would have attached a representation of my sheet, unfortunately because there is now a macro in my spreadsheet, our trigger-happy security software won't let me post it!
View 14 Replies
View Related
Oct 27, 2011
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 Related
Oct 4, 2007
I've recorded a macro which selects "1" in a filter drop-down box then prints some pages, then comes back and selects "2" in the filter then prints some pages, etc etc etc.
I've looked at the code for this and it treats the "1", and the "2" etc as text each time - originally I had the filter on names, but the names change each time I need to do this whilst the process doesn't, so I filtered on numbers instead.
However, I would have to enter the repeated blocks of code down to 1000 to get it to select down to "1000" in the filter. About 3/4 lines each time but with me manually typing in 1, 2, 3, 4 .... 999, 1000.
Is there a way to say in VBA 'repeat filter selection until you've run out of numbers then come back to "All" and stop ?
View 9 Replies
View Related
May 29, 2007
I need to use the Advanced Filter tool to allow the user to filter (in-place) the Guests worksheet while providing the following summary stats: total revenue, ave. revenue, max/min revenue and total number of tours ie the user should be able to enter any criteria below the database to to show only those records (and summary stats) that satisfy the criteria.
To automate the operation of the advanced filter tool, I need three macros. "SelectRoom" and "SelectGuest" macros should prompt the user to enter a value through an input box to filter the data according to a client's name or language tour. The third macro, called "ShowAllGuests" should clear the criteria row and dispay all clients in the database.
The first two macros should include an error message to prompt the user to rerun a macro if no clients satisfied the criteria while the "ShowAllGuests" macro should include a message box statement at the end to display a short message giving credit to the macro author. These macros also require a button each (three in total) in the Guests worksheet!
View 3 Replies
View Related
Feb 6, 2007
all i need to write is a code which syncronizes with my update button, which i don't know how to write....whenever i click the update button, all my files will be updates, and at the top elft of the screen would show the time and date of my last update.
View 6 Replies
View Related
May 3, 2014
I've got a problem with copying cells from table A to B.
A short insight of the situation:
Table A shows: ITEM_1 = 3, consequently ITEM_1 from table A is copied to table B for 3 times.
I've got a few huge information blocks to handle (up to 5000 items) obviously it's unreasonable to do this manually.
I've added an xlsx file with more detailed description.
Example_WHS.xlsx‎
View 8 Replies
View Related
Nov 5, 2008
I have a spreadsheet tool that I use to calculate values on a set of variables, based on values on an initial set of other variables, for a large number of cases.
The calculations require a number of sequential steps to be repeated to get the calculated values for each case, and I am hoping there is a straightforward way to use a VB macro to automate this process.
Essentially, these steps are required to calculate values for a single case:
1. A data input sheet holds the values on the initial/input variables for each case. The first step involves copying the string of values for the case on the input variables into a sheet where the calculations are done.
2. As soon as the input string of values are pasted into the calculation sheet, the calculation formulas generate values for the case on the new, output variables.
3. The output variable string is then copied from the calculation sheet into a “results output” sheet. This copy/paste procedure needs to offset from the first row on the output sheet, so that the row number where the values on the output variables are stored correspond to the case ID. For example, case ID 10 is stored 10 rows down from the first row (i.e., on row 11). Case ID 100 is stored 100 rows down from the first row (i.e., on row 101).
As there are a very large number of cases, I need to have a “control panel” in place, where I can indicate the range of case IDs that will be processed. This takes the form of two cells – one indicates “From ID” and the other “To ID” (for example, “From ID” 1 “To ID” 100). The macro then uses this information to loop through the calculation steps outlined above for these case IDs.
View 4 Replies
View Related
May 17, 2007
Is there a way i can dictate to Word from an Excel macro that i want it to open, Start a mail merge, use a pre-made file as a template, use the excel file the macro is in as the source, get it through the whole setup of the Mail merge all the way to the print function?
View 9 Replies
View Related
Sep 16, 2006
I have 5 Excel files. The first one is Main, and the other 4 are subfiles, namely A1, A2, A3 & A4. I have a need to open Main, then click a button to start a Macro (in Main) which will do the following, sequentially:
1. open A1
2. run a Macro in A1
3. close and save A1.
4. open A2
5. run a Macro in A2
...
12 close and save A4.
The files A1..A4 are a file server and being shared. If they're being used by another user, it cannot be saved - so I would have to skip it and continue with the next A. It would be great if start Excel, open Main, and run the Macro in Main fully automatically.
View 3 Replies
View Related
Jul 24, 2009
I have a form that displays when i open a workbook. I enter data into the form's fields (listbox & textbox). When I click the form's "OK" button, the focus doesn't return to the current worksheet, and in fact, focus doesn't return to any of the open windows / applications I have open.
View 9 Replies
View Related
May 8, 2007
I'm looking for information on building a macro that will automate making copies of a spreadsheet into another workbook that contains very particular information. My master sheet has lists of my customers and the rep's that service them. What I need to do is have macro buttons for each rep. that will pull all the shops that they service and all information for these shops into another spreadsheet.
View 9 Replies
View Related
Jan 23, 2013
I have recorded a macro to sort a range of cell based on two values that is dependent on time in another cell. I now want the macro to run automatically when refreshing the workbook with F9, so as the time changes so will the sorting. Everything works fine except the sorting doesn't refresh when F9 is refreshed.
My recorded macro is:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, FillCT As Long
For X = 1 To 4
If Cells(Rows.count, 1).End(xlUp).Offset(0, X - 1) <> "" Then FillCT = FillCT + 1
[Code] .....
View 4 Replies
View Related
Dec 4, 2009
I have a workbook that has a sheet for each month. Everytime something gets worked on in the shop, a line of information gets filled out in that months sheet. On a separate sheet in the workbook, I need to show what the top 5 things(according to how many entries there were for that item) that got worked on that month are. The info I need is all input into the same column, just different rows.
View 5 Replies
View Related
Apr 14, 2014
I just started learning VBA and I can't figure out how to create a macro that will update my date range. I need it to automatically add the next date in the next blank cell so it would be like this:
1/1/13 1/2/13 1/3/13 1/4/13
Here is an outline of my work book: GANTT4-1.xlsm
Id like a date to be added if the maximum date in row 6 is exceeded by a date in column k.
View 3 Replies
View Related
Aug 21, 2013
I had a spreadsheet with an insert row macro which worked fine and dandy, unfortunatly I decided to split the worksheet across two worksheets and the macro requires updating, The macro currently runs fine for the 'To be State' worksheet but gets an error on the 'As is State' worksheet. The code breaks down on this line:
ActiveCell.EntireRow.Insert Shift:=xlDown
The only difference between the two worksheets that I need is that in the 'To be State worksheet' columns C and O need to be copied down from Row 3 while in the 'As is State' worksheet columns C and U need to be coped down from Row 3.
The worksheet names are:
As is State
To be State
My Code is:
Sub InsertARow()
Dim CurCell As Range
Set CurCell = ActiveCell
Dim CurCellInA As Range
Set CurCellInA = Columns("A").Cells(CurCell.Row)
[Code]...
View 4 Replies
View Related
