VBA Macro (once Copied The Cell Value The Documents Then Close)
Oct 6, 2008
I have a large macro where it looks into 50 documents and pulls out a value from a specific cell.
I dont have any problem with the coding for this but was wondering if it is possible to add another line of coding so that once it has copied the cell value the documents then close.
Currently when I run the macro, it leaves all 50 documents open once it has taken all the cell values and pasted them into my "Master Document".
View 3 Replies
ADVERTISEMENT
Jun 18, 2008
I'm trying to create a macro that will save backup copies of documents that have changed when a user closes them. I created an add-in and put a Workbook_BeforeClose sub in "ThisWorkbook" that saves a copy of the document in a temp location on the hard drive.
If I have three Excel documents opened and each one has changes and I close Excel (with the "big red X") it only seems to call the Workbook_BeforeClose once backing up one of the files. If I use the "Close Window" (the "little X") it doesn't call that event at all.
Is there a better event to use or a better way to backup these files when closing them or Excel?
View 9 Replies
View Related
Apr 10, 2014
I have a macro that copies time cells from another sheet and puts them in cells in another sheet, I also have another macro that takes 30 mins away from that time if a certain condition is reached but it wont work unless i retype over the copied cell.
View 14 Replies
View Related
May 16, 2014
I have 4 rows that are on a "Resource" sheet need to be inserted into about 150 different variable locations across 20 different sheets. Here is the code I have so far the will copy it to one specific location (see below). I need making the macro insert these copied cells to which ever cell I currently have active. I'm still really new at VB programming so this probably something really simple to solve.
View 4 Replies
View Related
Jul 11, 2013
Some of my Excel files work perfectly in terms of undo/redo, while others simply cannot use that feature. I took a stroll through the internet looking for the answer, but the issues that I found seemed to point to the application losing its ability, which was resolved with a registry edit/update/change. I don't think that's the case here because I CAN undo is SOME documents.
After some investigation with my problem documents I have found that THIS Macro is the one thing all my undo-free documents have in common: [URL]
View 14 Replies
View Related
Jun 20, 2013
Basically I have manually generated word documents based on a spreadsheet. Now, I need to countercheck the contents of the word documents with another spreadsheet i.e. make sure the courses in the word document for a particular student is same as that reflected on the spread sheet. Is it possible to write a macro for this? I'm a beginner and I'm waaay out of my depth. I have attached sample documents and spreadsheets.
The link to the documents are as follows: Macro to check data from Word documents against an excel spread sheet
View 2 Replies
View Related
Oct 20, 2013
with a macro. I am looking to copy row 2 to the last row and past the copied rows directly below the copied contents.
View 9 Replies
View Related
Oct 11, 2006
I need to know the command to close a workbook. I have used the information found here: http://www.ozgrid.com/VBA/userform-close.htm to disable closing the workbook via the upper right "X", but I need a button that, when clicked, simply closes the workbook instead.
View 2 Replies
View Related
Feb 7, 2014
I have several excel documents all linked to one unique excel document which I will call document A.
Document A is a performance spreadsheet where I have percentages, sales figures and times, for my sales team.
Each sales person has their own unique dashboard (another excel document) that I am currently trying to finalize.
Their personal dashboards have pie charts with text boxes. The text boxes within the charts link up to their relevant cells in document A.
When Document A is open and I open 1 of the sales person's dashboards. Everything looks good and works, however.
When I close document A the dashboard information changes. For example, the dashboard information could be showing 25% but when I close document A it reverts to 0.25
If I close down the dashboard and open document A back up, change it to 30% then close it. The dashboard recognizes the change BUT reads as 0.30. This is the same with them all.
The only fix I have figured out is creating another tab in Document A then duplicate of all the information required for the dashboards then save the cells as TEXT (as opposed to currency, percentage etc).
This kind of defeats the object because I have formulas set up in Document A and don't want to be doing twice the work.
View 4 Replies
View Related
Jun 11, 2014
I was just wondering if its possible to create a Hyperlink to open a location with the link ref in another cell?
For example, If I created a shape (Insert > Shape) then assigned a hyperlink to lets say Sheet 2 cell A1 & in that cell the link to open My Documents?
Sheet 1: Shape (as a button)
Sheet 2: Cell A1 - C:UsersUSERNAMEDocuments
Then when I click the 'button' it doesn't go to Sheet 2 cell A1 it actually opens the documents?
View 2 Replies
View Related
Oct 16, 2008
Macro on copied sheet. I have a macro using the following to bring a shape to the front:
View 4 Replies
View Related
Sep 12, 2013
I want to create a macro that creates a new .xlsx document based on cell values. And input 4 tabs into each document with specified tab names. I have a document that pulls from external sources and fluctuates with amount of data per day. I envision something that will make a new document named for the contents in cell A1, then function as a control+down to create a new document for A2, then A3 until there is no content left (should mention these are lookup formulas, and if there is no data it pulls a value of "0")
Is there a way to put in the code, the tab names it would create in each new document, or would that have to look to a cell value for the naming? The tab names would be "GS", "MYSS", "COLL SHEET", and "WIRE".
View 1 Replies
View Related
Mar 11, 2014
I have a Workbook on a shared drive, with a button that runs a macro.
Is there a way to disable this if anyone makes a copy of this workbook, or does Save As? (If it is not the original workbook).
View 6 Replies
View Related
May 25, 2007
I have a macro that works fine in one workbook. I copied it to use in another one, but then I get this error message: "Invalid Use of Me Keyword".
Dim wSheet As Worksheet, strMsg As String
For Each wSheet In Worksheets
If wSheet.ProtectContents = True Then
strMsg = "All sheets protected."
Else
strMsg = "All sheets unprotected."
End If
Next wSheet
MsgBox strMsg
Unload Me
End Sub
View 7 Replies
View Related
Mar 8, 2014
How to disable a macro when the workbook opened is a copy ** the original, the only difference between the 2 files would be the date created, could it possible to use some VBA to check for this "Date Created", and if the date is different from the original, then the copy will not able to run the macro or some sheets will be hidden in the copied file?
View 5 Replies
View Related
Jun 27, 2009
I have a workbook, 'MyWb.xls' and a sheet with buttons to which I attach some sheet macros. When I assign a macro to a button I pick the appropriate reference, e.g. Sheet2.MyMacro, and this gets recorded by default as MyWb.xls!Sheet2.MyMacro
When I create a new single sheet workbook with the instruction ActiveSheet.Copy, I end up with a new workbook, say 'Book2' along with the buttons as expected. However the macros attached to the buttons still refer to the MyWB.xls file and not the new 'Book2.xls'. As part of the process of creating the new book I've tried redefining the macro with the instruction
View 4 Replies
View Related
Jan 26, 2014
I don't know if a macro is necessary, but the built in Insert -> Insert copied cells -> Shift cells down function can't accomplish what I require.
I've pasted in two example tables for what I'd like to do. For the first table, the data in column C is a name and the cells from columns D-Z, rows 7-10 is what I want to copy and paste from row 12, column D; row 13, column D; etc, etc. However, I want the full rows below the name in column C to move down when I paste from column D. Using the Insert function on the Insert blade only gives me the data in columns D-Z moving down.
The second pasted table gives an example of how I want it to look.
A
B
C
D
E
F
G
H
I
[code].....
View 4 Replies
View Related
Sep 14, 2009
I've been using the following macro ....
View 8 Replies
View Related
Mar 20, 2007
I've created a custom toolbar and populated it with a custom button. I have created a macro and it resides in ThisWorkbook. The macro is assigned to the custom button and the toolbar attached to the workbook. So far, so good. If I copy the workbook and rename it, when I click the button to run the macro it launches the original workbook and runs the macro. The macro effects changes on the renamed workbook copy. The macro name found in the assign macro dialogue starts with the original workbook name. The same effect if the macro resides in a module or sheet. Any known way to have the macro name change along with the renaming of the workbook such taht it refers to the currently opened workbook macros? Regards.
View 5 Replies
View Related
Oct 16, 2009
I am trying to make a save&close workbook macro.
I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).
The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.
If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.
The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)
The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.
View 10 Replies
View Related
Jan 4, 2012
I have a simple list of data (Name, Room, etc.) in a spreadsheet. Each day I click on the spreadsheet tab and create a "copy" and (move to end). This creates a spreadsheet for the next day's data.
My problem is that the macro I use to sort this data never works in the newly created spreadsheet within the same workbook. As usual, I'm sure it is something relatively simple that I am overlooking.
View 1 Replies
View Related
Jan 28, 2013
I have this macro code in Sheet1 assigned to a button named Combination. I copied the whole sheet twice and moved to end. So they have the same data content and hopefully the macro in sheet1 will work as well in sheets2 and 3. But it's not. I just copied the code from VB codes Excel objects Sheet1 to Sheet2 and Sheet3. The cursor changed to a reading glass but it doesn't display any result. I am sure it's the worksheets(1).range part that needs to be edited but i am not sure if that is simply changing it to worksheets(2) or worksheets(3). I tried that also but no effect also.
Option Explicit
Public Sub ModelPricing_Template()
Dim a As Integer, b As Integer, c As Integer, d As Integer
Dim e As Integer, f As Integer, g As Integer
Worksheets(1).Range("a15:IV65536").ClearContents
[Code] ........
View 5 Replies
View Related
Jan 3, 2014
After using a vlookup formula, and a if then statment I found out that there are duplicates on my other sheets. I want to use a Macro to use the duplicate value in search and find to locate the item and highlight it on my other sheet. I have searched the web but can't find anything on this.
I can't seem to paste my coppied cell into the what in the find.
Sub Macro15()
'
' Macro15 Macro
'
'
Selection.Copy
[Code]....
View 2 Replies
View Related
Nov 7, 2006
original thread 2. Originally Posted by Dave Hawley
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wSheet As Worksheet, wSheet2 As Worksheet
Dim strNum As String, strNum2 As String
If Target(1, 1).Address = "$J$5" Then
If Target = vbNullString Then Exit Sub
If IsNumeric(Target) Then
strNum = Target + 1
strNum2 = Target
On Error Resume Next
Set wSheet = Worksheets(strNum)
Set wSheet2 = Worksheets(strNum2)
On Error Goto 0
If Not wSheet Is Nothing Then
Application.EnableEvents = False
Application.Undo......................
What I would like to do is after the new sheet is created it would make cell j5 on the newly created sheet the same name as the sheet itself. I've tried to do that but the above code tries to activate. Is there a way to have the above code only work on the very first sheet and for the cell naming code to work on only the created sheets
View 2 Replies
View Related
Sep 7, 2012
I am working on a database and trying to remove duplicates. When there are duplicates, I want to take the contents of one cell and copy them as a comment on the cell above (or below).
I tried to work on a small macro to do so, but the contents of the cell cannot be copied (it comes out as a blank).
The long way is to 'double click' in the cell, Ctrl+A, cut, click the cell above, Ctrl+F2, paste. This way is very long as I have thousands of duplicates.
View 4 Replies
View Related
May 5, 2014
I'm trying to copy the a name from cell X2...JohnDoe and rename the current sheet with the contents of X2. I'm using the following code and getting my sheet named as "True"
Sub SheetName()
'
' SheetName Macro
'
Dim ShName As String
ShName = Range("X2").Select
Selection.Copy
Sheets.Add.Name = ShName
End Sub
View 4 Replies
View Related
Apr 25, 2006
I have created some code that copies a master workbook into a new workbook that then creates new tabs for the sheets relevant to week numbers. In the master workbook there are 8 sheets, most of which reference each other in their formulas at some point. The problem arises in the newly created workbook as the sheettab names are renamed e.g "manager" in the master will become "manager wk1", "manager wk2" and so on. All the sheets are copying over great but they the lose their references to each of the sheets within their week groups because the formulas do not change to the correct week references. I am trying to solve the problem by using the Indirect funtion to change the formulas in the master to reference the correct week number, but i feel this is not the best way?
View 2 Replies
View Related
Jun 11, 2007
When i push a button how could i get the buttons text copied to cell? For Example:
If i have button and it's text is TestButton. I push the button and the text TestButton is copied to cell(1,1). How can i do this?
View 2 Replies
View Related
Mar 22, 2012
I am using Excel 2003.
It’s been working perfectly up until around an hour ago. However, now when I close Excel, I get compile errors.
These compile errors seem to be because Excel is trying to access controls in the workbook after it has closed. Since the workbook is closed, VBA can no longer ‘see’ the controls, and therefore it thows up errors.
I’m also getting a similar error on a Worksheets("DataExplorer").unprotect line, which seems to be because the worksheet isn’t there after closing.
These errors only occur when I close the whole excel application using the big cross in the top right. If I just close the workbook (using the smaller cross just below the big cross in the top right of excel), it doesn’t throw up these errors.
Just to clarify: all of the code runs perfectly when Excel is open. The errors are being thrown up for lines of code which run without problem until Excel is closed.
View 9 Replies
View Related
Oct 8, 2008
I have the following Macro setup and I would like the macro to run once the Save button is clicked.
Sub LetterCol()
Columns("C:C").Select
Selection.Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("D:D").EntireColumn.AutoFit
Range("A1").Select
Application.CutCopyMode = False
Range("D1").Select
ActiveCell.FormulaR1C1 = "Letter Column"
Range("A1").Select
End Sub
View 4 Replies
View Related
