Macro To Record When File Has Been Printed
Nov 6, 2012
I have a document that I need to track all changes to. I have a macro running perfectly right now that tracks all of the content changes to the form. What I am wondering is if I can add something that would also record when a copy is printed and the user that makes the copy. Here is the code I have right now:
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = False
Sheets("Revisions").Unprotect Password:="Hm72K9"
If ActiveSheet.Name = "Revisions" Then Exit Sub
Application.EnableEvents = False
[Code] .....
View 3 Replies
ADVERTISEMENT
Mar 27, 2012
We are using Excel 2007.
I wanted to add the date of the last modification of the file to the printed pages, so I googled how to do it and found a useful answer on this from from 10 years ago. It said to press ALT+F11, then on the left side go under VBAProject, there go to ThisWorkbook and there paste the next code:
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each wk In Worksheets
With ActiveSheet.PageSetup
.LeftHeader = "Last Modified on " & ActiveWorkbook.BuiltinDocumentProperties.Item("Last Save Time")
.CenterHeader = ""
[Code]....
Then I saved it and closed. Like I said, it worked perfectly.
The problem is that when I open the file after closing it, it does not appear, and I have to paste the code again every time I open it. Not only is this not comfortable, it also counts as a modification, so even if I haven't changed the work sheets themselves, just wanted to open and print it, it shows today's date.
My question is, who to I save it permanently so it will remain always after the first time I created it?
While we're at it, if there's a way for that code to appear on every NEW Excel file I create automatically
View 5 Replies
View Related
Mar 20, 2009
I'm trying to find a macro that will run allowing the user to select a 'starting record number' and a 'finishing record number' when printing.
I have a spreadsheet that feeds from a master list in excel, from over 5000 records.
I need to print the s'sheet with any given indivdual record's information at any given time.
Individual prints are fine. However if I wanted to print from record number 1500 to record number 3000 it would take me all day.
Is there a way I can set up a macro so an option form pops up? allowing selection of "From record" and "to Record" ?
View 13 Replies
View Related
Mar 21, 2014
I have a template file that I import data in to, which is then saved with the current date. This works, but I want to automate the import of the data, but ensure I don't duplicate the load.
Therefore, I have a tab 'FileLog' that I record the filename (variable by date) in column A and Creation timestamp in column B.
How do I obtain the filename and creation timestamp of the import data so that they can be entered?
View 11 Replies
View Related
Dec 15, 2008
I have the following code to print a selection of data which is fine but i need to have in the code a different printer. So, for example my default printer needs to stay the same but when this spreadsheet is open i need the data to be printed to another printer.
View 3 Replies
View Related
Dec 26, 2011
I have a print macro that runs from a command button, when I use it the workbook goes to the sheet printed. Can I somehow make the workbook go back to a sheet of my choice? Here is the macro, The command button is on a sheet called Control Center, can I return to that sheet or stay at that sheet when I print?
Code:
Sub Button16_Click()
Sheets("OBS1").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub
View 4 Replies
View Related
Jan 6, 2007
Im trying to record a macro that will enter a formula in a cell each time I run the macro
Drop the Lowest2:
=IF(ISERROR(AVERAGE((SUM(C5:L5)-MIN(C5:L5)-SMALL(C5:L5,2))/(COUNT(C5:L5)-2))*Scale!$B$2*10),(IF(ISERROR(AVERAGE(C5:L5)*Scale!$B$2*10),"",AVERAGE(C5:L5)*Scale!$B$2*10)),AVERA GE((SUM(C5:L5)-MIN(C5:L5)-SMALL(C5:L5,2))/(COUNT(C5:L5)-2))*Scale!$B$2*10)
Drop Lowest1:
=IF(ISERROR(AVERAGE((SUM(C5:L5)-SMALL(C5:L5,1))/(COUNT(C5:L5)-1))*Scale!$B$2*10),(IF(ISERROR(AVERAGE(C5:L5)*Scale!$B$2*10),"",AVERAGE(C5:L5)*Scale!$B$2*10)),AVERA GE((SUM(C5:L5)-SMALL(C5:L5,1))/(COUNT(C5:L5)-1))*Scale!$B$2*10)
I know I can write the formula in a simpler way, but I was asked to make it like this.
The problem is that when I enter the formula in the cell while recording. I got a message: "Unable to record" so this part can't be recorded
View 14 Replies
View Related
Sep 1, 2009
This problem seemed to revolve around "digital signing" with further macro changes done from a different machine (without the proper certificate). Excel warns of the problem and then "unsigns" the project.
I've got a problem with spreadsheets that I've been working on. Now, when I tell it to record a macro, I get the macro name, shortcut assignment dialog box and then an error "Unable to record." After "Ok"ing that dialog box, the normal recording macro dialog comes up. Also, lo and behold, there will not be a macro recorded.
I've got no protected cells or sheets and it doesn't matter what security level is set. It is a "signed" macro but I can take off the signature and still have the problem. I checked for "Help - About" for deactivated modules and there was none.
View 2 Replies
View Related
Jan 10, 2013
I have a text file that contains two rows of data for a single record. when I open the file up in excel, it puts each row into column A. What I need to do is put the 2nd row and combine it with the data in the first row so that i can then run the text to columns wizard and put all the data into its own column.
Here is an example of the data:
TEWAC Dresser Rand SAB 21120-14 1350 1687 3300 80 .80 50 4 2.63 EE-7592 1 2250 46 30.5 14 6
10 12 1 4 .229 .102 2 2 .0303175V/1 HL2 .13 .50 .25 1.5 394 3.000
IP23 Andalas SAB 21000-28.5 1500 1875 415 80 .80 50 4 4.22 EE-7777 80/50 Rise 1 2250 43 30.5 28.5 10
2 13 4 8 .258 .102 0 0 3175V 2 .11 .46 .25 1.5 1.500
CACA Intergen CACA 21120-33 1720 2150 6600 70 .80 50 4 4.56 R971055 50C Amb EE-7467 1 46 30.5 33 14
8 14 1 2 .229 .144 2 2 .0303175V/1 HL2 .20 .65 .30 2.0 697 2.150
What it should look like is the following:
TEWAC Dresser Rand SAB 21120-14 1350 1687 3300 80 .80 50 4 2.63 EE-7592 1 2250 46 30.5 14 6 10 12 1 4 .229 .102 2 2 .0303175V/1 HL2 .13 .50 .25 1.5 394 3.000
IP23 Andalas SAB 21000-28.5 1500 1875 415 80 .80 50 4 4.22 EE-7777 80/50 Rise 1 2250 43 30.5 28.5 10 2 13 4 8 .258 .102 0 0 3175V 2 .11 .46 .25 1.5 1.500
CACA Intergen CACA 21120-33 1720 2150 6600 70 .80 50 4 4.56 R971055 50C Amb EE-7467 1 46 30.5 33 14 8 14 1 2 .229 .144 2 2 .0303175V/1 HL2 .20 .65 .30 2.0 697 2.150
leaving me with 3 rows and one column of data.
I have multiple files with about 600 rows in each that I need to process.
View 2 Replies
View Related
Oct 12, 2009
I have recorded (i.e. manually as opposed to writing VBA code) a number of macros to perform a routine, however they do not work when I change the filename.
Could someone please advise on how to edit these macros (which reference a specific filename) so that they work when the file name is changed. The macros copy and paste values from different worksheets and then run another set of macros. However all macros and worksheets are located within one excel file
View 13 Replies
View Related
Apr 24, 2013
I receive 24,000 text files once a month that need to be combined into one csv/txt file and/or spreadsheet(tab).
About a year ago I posted a thread on the same topic which received a fantastic response from jindon that worked great
Unfortunately, the format in which the text files are ouput has changed, as has the filename layout. The files are now output with filenames such as:
(lic#, company name, displaying # records found, date, type.txt)
40298827_Windham Professionals Inc _Displaying records 1 through 10 of 100_041813_AGENTS.txt
40298827_Windham Professionals Inc _Displaying records 11 through 20 of 100_041813_AGENTS.txt
40303726_HEARTLAND CREDIT RESTORATION INC _EANF_041913_AGENTS.txt
(files with EANF in the filename have no records inside them and can be skipped)
While the contents of each file look like this: (see attached text file reference)
I would like to combine the contents of the text files while appending the lic#, company name and date from the filenames to each record so the resulting file looks like this:
40305196 Audette , Anthony Sales Provider 40298827 Windham Professionals Inc 041813
40313800 Burritt , Kimberly Sales Provider 40298827 Windham Professionals Inc 041813
As far as I can tell jindon's code is fine except the regex expression needs to be modified to handle the new layout, however that is far beyond me.
View 9 Replies
View Related
Feb 18, 2007
I recently installed Excel 2007, and have shared others' joy in searching for things on the ribbon. I tried to record a macro to insert an autoshape. Excel creates the macro, but drawing the autoshape is not recorded. I'm sure I'm missing something obvious ...
View 7 Replies
View Related
Nov 29, 2008
I looked at threads realted to printing but wasn ot able to find something similar to what I need. So here is my problem:
I have a file with more than 100 worksheets (each sheet contains the invoice for one store). I would like to create a macro that would enable me to determine the order in which worksheets would be printed. How to do it?
Idea #1: the printing order would be based on the value in cell L1 that would contain the route number for each store. Stores belonging to the same delivery route will have the same value in L1. So, the macro should first print all sheets with 1 in cell L1, then print all sheets with 2 in cell L1 and so on...
Idea #2: Creating a separate data sheet with the list of all stores and their corresponding route number. Let's say info is contained in range A1:B150, where Column A contains the name of the stores and column B contains the route numbers. The macro then should look at that list to determine the printing order of the subsequent worksheets (the name of the store in column A would be the same as the name of the worksheet corresponding to that store).
Idea #3: sorting my 150 worksheets manually. It does not solve my problem fully, though, because stores do not always belong to the same route. So the manual sorting should be carried out daily and would not save time at all.
View 9 Replies
View Related
May 25, 2007
I seek advice on using the value of NOW() as a record ID in an address book program. Question #1: Do Excel developers often use a record ID? Question #2: What record ID schemes are fequently employed besides date/time? I have decided to create an Excel address book as an exercise to increase my knowledge of VBA, and also as a useful application for work.
I realize that a record ID is not essential in Excel in the way that it is essential in Access, but I feel the need to have some unique ID associated with each address, so that I may have different worksheets, with data related to a given Contact, sort and manipulate it, if necessary, but have the record ID as a way to restore the relationship of rows to a given Contact, and also, as a handy way to examine the data in the date/time sequence in which it was entered. I have experimented with the following code, to assure myself that I can access the number returned by the NOW() function, manipulate it as a string, and format in various ways if necessary.
Dim n As Double
n = Now()
sn = Str(n)
p = InStr(sn, ".")
first = Left(sn, (p - 1))
l = Len(sn)
d = l - p
S = Mid(sn, (p + 1), d)....................
View 2 Replies
View Related
Jun 25, 2014
l would like to record a macro that allows one finds a 'key word' in sheet 1 ,then the macro should copy the entire raw of the search results to worksheet 2 .The macro should enable the user to have as many searches as possible but pasting all the results on one worksheet.
View 2 Replies
View Related
Mar 16, 2014
I am creating an asset management sheet. For the formula I am trying to work out there uses 3 fields : ID, start date, and end date.
What I want to do is be able to show if the ID is duplicated within another record with an overlapping date. So an item is flagged if it is in the list within the same dates as another record. I tried a few countif formulas but with no success.. I may just be approaching the problem incorrectly though.
View 1 Replies
View Related
Jan 14, 2009
When I choose to record a macro, the window to name the macro opens and allows me to name it, but when I close it, I can see it is recording at the bottom of the sheet, but the stop button with the relative/absolute button has disappeared. I can't change relative/absolute. How can I get the button back on the page?
View 2 Replies
View Related
Oct 27, 2007
How do I enable macro recording in Excel2003? When I select Tools - Macro, the 'Record New Macro' selection is disabled. How do I enable it?
View 2 Replies
View Related
Dec 14, 2006
I'm trying to determine the speed of a macro. I searched and have had no luck. recently with some help I reduced my macro speed from minutes to seconds and I was wondering is there code out there that I can record the speed of an existing macro.
View 5 Replies
View Related
Oct 31, 2008
I have a table where I have dropdown menu for selecting data and a vlookup for filling other fields.However, I would like to create totals at the bottom of the sheet. But I dont know how many rows I will need because the data to this sheet are added from another sheet in the workbook. I would like to create something like this :
No. Name Weight Amount Total Weight Unit Price Total Price
1. Product 30 2 60 100 200
then I will not have no. 2 , but add new record button that will create a new row and will copy the dropdown menu and the Vlookup function so the format will be the same for product no.2 as it is for the product no.1 ... thus I will not have any unfilled rows in the table and after the last row there will be a totals row that will sum up the whole table... How to do that "add new record button" so that in the table will show up only those rows, that are actually filled with data?
View 13 Replies
View Related
Mar 16, 2009
I am trying to have a macro send the data from sheet 2 to sheet 3, record the data then next time shift down a row and enter the new data below and below every time new data is entered.
I have recorded a macro, but I am stuck with the recording the data and then returning to a new row.
View 9 Replies
View Related
Jul 1, 2009
I have already looked at many of the posts but I have not found the solution that I am looking for. I am familiar with VB and the NOW function.
I am trying to create a macro that will reference a cell and place a static date and time next to the cell. I want this date and time to only change when the checkbox is clicked.
I have a checkbox in E11 that is linked to F11, I would like to have G11 record the date and time that the checkbox is clicked. I have already tried the following.
View 14 Replies
View Related
Feb 25, 2010
I am trying to record a macro for conditional formatting but when I check the VB Editor after I finish recording it, it only has
Sub CFTest()
'
' CFTest Macro
'
'
Range("D1:D4").Select
End Sub
How to get the keystrokes to record or what I need to do?
View 9 Replies
View Related
Aug 21, 2009
Complete List of People in Column AA.
Partial lists of these same people in columns A, C, E, G, I, K.
Goal: Once I put that persons name in A, C, E, G, I, K, I would like it to be deleted from Column AA.
View 9 Replies
View Related
Feb 25, 2010
I am trying to record a macro for conditional formatting but when I check the VB Editor after I finish recording it, it only has ....
View 9 Replies
View Related
Jan 27, 2010
This recorded macro inserts a line below the cell that active when it was first recorded. It then copies some text and a formula to the line that was created.
Sub Macro11()
Rows("10:10").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A9:E9").Select
Selection.Copy
Range("A10").Select
ActiveSheet.Paste
Range("H9").Select
Application.CutCopyMode = False
Selection.Copy
Range("H10").Select
ActiveSheet.Paste
Range("F9").Select
End Sub
If you run this macro again at a different cell position, it goes back to the first starting position and repeats the same action. I see that the cell references from the first recording are fixed in the macro. I can't figure out how to make the macro use the new current cell position as the starting positiion when it runs again.
View 4 Replies
View Related
Apr 26, 2007
I have designed a project sheet that shows Project,tasts,start & end dates, with cells being highlighted via conditional formatting using the between format, What i would like is for the macro that i recorded to enter the data(which is c/f to a new row, At present when i run macro it reruns on the same row,the filldwn method dosn't work either, For design layout i have based it on microsoft project
View 9 Replies
View Related
Jun 19, 2008
I am using the following track changes code on a worksheet;
Track/Report User Changes on an Excel Worksheet/Workbook
in the this Workbook. It involves two different VBA solutions I had gotten form Ozgrid. The top part is VBA code to track changes in the workbook, THe instructions are to put the statement at the top of the module which I did. When it gets to the second VBA code {Starting with Option Explicit} below, I get an error message that "Only comments may appear after End Sub, End Function or End Property.
Also, I would like to get the VBA course offered on this website, any comments?
Dim vOldVal 'Must be at top of module
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim bBold As Boolean
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
View 4 Replies
View Related
Jan 19, 2009
I have a macro which changes a worksheet based on each record from another sheet, it then copies the worksheet. I.e. a new sheet is created for each record simpy with...
View 2 Replies
View Related
Sep 30, 2008
I would like to create a macro in my personal macro workbook that will uncheck specific "check boxes". I tried recording this process, but had no luck. Is there a trick to recording actions performed on objects, or some other trick to make this work that I'm not aware of?
View 9 Replies
View Related
