Trying To Modify Code To Track Changes In Two Workbooks
Aug 28, 2009
I was trying to see if this code would work for my application where I want to compare two forms. Basically the master is filled out and then sent to a vendor. When it comes back from them rather than going through then entire thing and trying to determine if they changed or added anything manually I was hoping to use something like this code to do it.. I tried this code using a similar setup, basically a master file and an update file but it wouldn't actually populate the changes in the changes sheet on the master form..
View 3 Replies
ADVERTISEMENT
Aug 13, 2009
I have about 50 spreadsheets which should all have protected worksheets and workbooks. I have to go into each spreadsheet occasionally to make changes, and sometimes forget to reprotect them! I have written a macro to create a checklist of whether or not they are protected - ie. I have a list of the filepaths/filenames and the macros will open each file and use the .protectcontents and .protectstructure properties to put a yes next to the filename.
This works well and is helping me, but I want more! When a user completes their spreadsheet I will password protect it, so only I can open it to modify with a password (they can go in read-only). I want to do the same as I have done above to check if this is the case. I have tried a few different properties - I thought activeworkbook.writereserved would do it, but it doesn't work.
View 4 Replies
View Related
Feb 3, 2014
User will save as Job # or Job name to their computer This job # or Job name will be also typed in location ..lets say cell a2 of every workbook the user creates from the master workbook. After the user is finished inputting all data into workbook they save it when they save it to send or have another workbook pull from it certain information...lets say total hours and total $$ respectively in cells s30 and p18
this workbook will save those numbers every time the user opens the one he is working on and saves it(could the workbook be closed and still store these data figures from other workbooks)
workbook called "DATA STORAGE" will compile this information from every workbook created from the original master workbook.
The point is so that the user can instead of opening up a possible 50 to 60 workbooks to check on $$$$ and hours they can see that information from all 50 or 60 workbooks at once on one page.
View 2 Replies
View Related
Jun 12, 2008
I have a code here, which thanks to Jindon helps on yesterday, But now, I want to change the code to count by column instead of rows, to continuous copy & paste my date,
How can it to modify:
Sub test()
Dim home As Worksheet
Dim Filename As String, myDir As String, fn As String
Set home = ThisWorkbook.ActiveSheet
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
If .Show = -1 Then
Filename = .SelectedItems(1)
myDir = Left$(Filename, InStrRev(Filename, ""))
fn = Mid$(Filename, InStrRev(Filename, "") + 1)
With home.Cells(Rows.Count, "E").End(xlUp)(2).Resize(2)
.Formula = "='" & myDir & "[" & fn & "]MAN_SUM'!k6"
.Value = .Value
End With
End If
End With
End Sub
View 9 Replies
View Related
Feb 9, 2010
Find below a vba code that i attached to a button that converts my print area in excel to PDF via Cute PDF writer.
Sub PDF_Sheet()
Dim Filename As String
With ActiveSheet
Filename = .Range("A1")
.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True
SendKeys Filename & "{ENTER}", False
End With
End Sub
I need a VBA code for the Excel sheet that i am cuttently working in that will do the following:
i have text and pictures in range O86:W97 that must be automaticaly romoved & replaced by another range after the above mentioned code hase complete
the range that must be replaced with the following,
O101:w112 Pdf code must run then
O113:w124 pdf code must run then
O125:w136 pdf code must run then
O137:w148 pdf code must run then
O149:w160 pdf code must run then
note that the ranges run in a sequence 101to112 then 113to124 then 125to136 and so on
Now this needs to be repeated 190 time starting from range O101:w112. The range O86:w97 forms part of my print area of A1:W97, this means i will have 190 PDF saved pdf sheets when i the vba code is complete.
View 9 Replies
View Related
Sep 28, 2009
modify this code to save as .csv? I tried just putting it after the file name and that didn't work.
View 2 Replies
View Related
Jan 30, 2010
Is it possible to modify this code so that it will give the lowest values the opposite ranking from which it is now. (Please take a look at the example sheet)....
View 5 Replies
View Related
Jun 20, 2008
It's a linked post from:
[url]
I managed to do the combine the row if column B matches. However column D(quantity, number value), i want the quantity to add-up if column B matches. Any idea how do I modify the code below to do that?
for example:
TDG-**002 Tuna Cheese Pizza Bar (KG) KG 30
TDG-**002 Tuna Cheese Pizza Bar (MG) MG 30
TDG-**002 Tuna Cheese Pizza Bar (KG) KG 30
will combine to become
TDG-**002 Tuna Cheese Pizza Bar (KG) KG 60
View 9 Replies
View Related
Dec 3, 2008
I got this code from someone who is not currently available,
I modified it a little so that when I pick a picture it always lands on j96, but I need it to line up exactly on top of the picture underneath it
I have also attached a sample.
The pictures will always be exactly the same size
View 7 Replies
View Related
Apr 7, 2009
Thanks everyone that has helped me with this so far. I am very new to VB so I definitely appreciate all the help.
I have the following code to select rows that meet 2 different criteria and copy them to a new sheet.
The problem is I only want to copy the values not all the formulas - the formulas don't work in the new location as they are referencing cells that are no longer there.
View 6 Replies
View Related
Apr 29, 2009
how to add in a text box prompt that will modify code?
View 4 Replies
View Related
Jan 22, 2008
I am using the following code, slightly modified, written by Tom Urtis.
I would like to enhance the code such that it will filter on two criteria to enable more specific results to obtained e.g. from the possibilities below I would like to get "Jones Manchester".
Data list:-
Jones Blackburn
Jones Liverpool
Jones York
Jones Manchester
Jones London EC1
Jones London N12
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address "$D$1" Or .Cells.Count > 1 Then Exit Sub
ActiveSheet.AutoFilterMode = False
If Len(.Text) > 0 Then
Dim myVal$, myVal2$
myVal = .Value
myVal = "*" & myVal & "*"
If WorksheetFunction.CountIf(Columns(1), myVal) > 0 Then
View 9 Replies
View Related
Dec 12, 2008
I have this code that will create 3 worksheets with the name "New Name" and the # ( 1/2/3 ) ..
How would I change the code if I want 3 worksheets with different name?
Like Sheet1 then Sheet 2 then Sheet3 or any other name?
'To add worksheets and change name with one code
For i = 1 To 3 'Creates 3 worksheets
Set ws = Worksheets.Add
ws.Name = "New Name" & i
Next i
View 9 Replies
View Related
Jun 12, 2009
I wanted to change a column text to call intial Caps. I found this macro code on the microsoft web site
Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In Range("C1:C5")
' There is not a Proper function in Visual Basic for Applications.
' So, you must use the worksheet function in the following form:
x.Value = Application.Proper(x.Value)
Next
End Sub
I need to change the macro to have a input box (asking what column to convert text to initial CAPS)
View 9 Replies
View Related
Jul 3, 2006
Private Sub CommandButton1_Click()
Dim strFirstAddress As String
With Sheet1.UsedRange
Set rngFind = . Find(ComboBox1.Text, LookIn:=xlValues)
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
Do
rngFind.EntireRow.Copy sheet3. Range("a" & sheet3.Rows.Count).End(xlUp).Offset(1, 0)
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
End If
End With
Unload UserForm2
UserForm4.Show
End Sub
The problem is it searches the whole sheet, what I want it to do is only search one colomn (colomn A). I know its prob an esay thing to change but I can't for the life of me do it!
View 3 Replies
View Related
Jun 28, 2009
I have a workbook with a sheet for each day of the month where guest information is entered. There is a column for each item. There is a summary sheet that summarizes the number of members, guests etc. One thing I would like to track is the number of people from ech zip code.
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
Dec 11, 2009
I have quite a few Excel templates that need to be modified by changing/adding information or sheets to them. Instead of working through the lot manually, it would be good if I could do the same through code. With my little knowledge I can do this for workbooks. Need to typical code snippets or suggestions? e.g. to open a template file, save it with the original name, etc. The usual Dim srcWB as workbook, Set srcWB= ABC.xls and Workbooks(ABC.xls). open do not seem to work with template files (ABC.xlt)
View 5 Replies
View Related
May 24, 2008
I am trying to design a tool which dynamically results in different cells depending on users inputs. Yet, I am willing to protect most of the cells to avoid any user involuntarily (or voluntarily!) modifying the formulas/contents of the calculated (and locked) cells. Is there any way to protect my sheets, allowing only a certain macro to modify the locked and protected cells?
View 4 Replies
View Related
Nov 11, 2008
I have the code below set to change the background color based on certain conditions. What I need to do is change it so that if none of the conditions are met, it does not change the color at all. As it is written now, if anything is entered in a cell that has a background already set, but does not meet these conditions, the cell turns white.
View 4 Replies
View Related
Dec 14, 2011
I am using the following code to copy the contents of a DDE feed.
Code:
Private Sub Worksheet_Calculate()
Worksheets("Sheet2").Range("A1").EntireRow.Insert Shift:=xlDown
Worksheets("Sheet1").Range("A3:F3").Copy
Worksheets("Sheet2").Range("A1:F1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub
What I want to do is only copy the row when the columns contain data, as it stands at the moment it's copying blank rows to sheet 2. Is there a way that I can do this?
View 3 Replies
View Related
Apr 2, 2014
I found the code below and it works perfectly if I want to copy all the other sheets to a master sheet. But, I need to specify specific sheets. Basically I have a workbook consisting of multiple sheets and multiple "master" sheets so I need to specify in the code which sheets it should be copying.
[Code] ........
View 5 Replies
View Related
Aug 22, 2012
I searched many sites and used codes which delete Rows based on criteria. In my case those codes works, BUT it took so much time about 30 min since there is about 75 thsd rows, and that solution in not time saver. I recorded code below and it is done in seconds. make it dynamic, VBA, since number of rows is every month larger. Basicly, I need code which delete rows based on Column B, where walue is "R"
Code:
Sub DelRowsBasedOnOneCriteria()
'
' DelRowsBasedOnOneCriteria Macro
[Code]....
View 4 Replies
View Related
Feb 22, 2009
I'm working on a spreadsheet to compile and print checks. All the check information except the date and first check number is contained in a Wins sheet. I also have a Checks sheet which contains a master check. I used text boxes on the master check to contain individual check information. Each text box is filled in from the Wins sheet by means of formulas. The problem I'm having is how to modify the formula entries in the following code to cycle or loop through all checks required. A sample spreadsheet is attached.
View 2 Replies
View Related
Oct 24, 2011
Code below. I need it to NOT run if the sheet week2 doesn't exist. Currently it gives a runtime error '9' out of range. This is due tot he sheet not being present because sometimes it is not generated.
Code:
Sub RemoveColWeek2sheet()
Dim ColNo As Integer
Dim rng As Range
Set rng = ThisWorkbook.Sheets("Week2").UsedRange
[Code] .........
View 6 Replies
View Related
Jun 4, 2008
i am trying to write a macro that will allow me to, while two workbooks are open and one is defined as active i.e
in workbook 1 i want the cell A1 to be defined as the sum of cell A2 and A3 in workbook 2.
i have a code that lets me do it but the problem is that this code (that i recorded) only gives me specific workbook names to work from..
i dont know if i make much sense but i would like the macro to work on any 2 workbook that are open and perhaps having flexibilty to choose would be great here is my attempt
View 14 Replies
View Related
Oct 5, 2009
I'm trying to use VBA to write from one workbook to another. I'm trying to populate other workbooks from a single 'control' workbook with a template.
I've managed to figure out how to open up Excel workbooks within a directory (this needs to be done multiple times) supplied by the user, then retrieve information from it to be collated, but I can't for the life of me figure out how to write to the file that I've opened.
I've got the code below, and it's very scrappy as I've been trying lots of different things but nothing's worked. I'll try and highlight as best as I can where I'm having difficulties, as I get the "object not defined" or some such error.
View 12 Replies
View Related
Apr 18, 2003
I have written VBA code to open a separate file and then copy back and forth between the two files. However, when I recorded, I got commands like the following:
Windows("DestinationFile.XLS").Activate
then the copy/paste stuff then
Windows("SourceFile.XLS").Activate
The problem is that my "SourceFile" may have a different filename. I need the code to be robust enough to handle the possibility of a different filename. I tried the following, but it did not work:
Dim MarketProfileName As String
MarketProfileName = ThisWorkbook.FullName
Windows("DestinationFile.XLS").Activate
then the copy/paste stuff then
Windows(MarketProfileName).Activate
View 6 Replies
View Related
Jan 31, 2012
For example, I have 50 clients. In 1 workbook, I have a sheet for each client. When I'm finished with a particular client, I need their one sheet to place in their file and be done with it.
The current process is to open that 1 global workbook, copy and paste the sheet I need into a new workbook and go from there.
I was wondering if there is a simpler way to achieve this with VBA coding?
Ideally, I would like to click an object button and then be prompted to select a worksheet from a list that contains all current non-hidden worksheets.
Once I select a worksheet, I would then be prompted to save as and select a file path. That would save that specific client worksheet in its own file that I selected.
View 7 Replies
View Related
Apr 8, 2008
Workbooks.Open "E:Prep2008" & "" & Format(ActiveSheet.Range("C6"), "ddd dd-mm-yy") & ".xls"
is code I use to open a workbook (workbook b) based on the date found at cell c6 of workbook A.
Question ... how do I divert activity to to the newly opened WB B from WB A?
Workbooks?.Select?
View 9 Replies
View Related
