Excel Macro Stops Working In Copied Spreadsheet Within Same Workbook

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


One Macro Stops Another From Working

Dec 2, 2011

I have this macro which sits in the workbook module:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldRng As Range

On Error Resume Next
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then

[Code] .......

It's used to highlight the selected/active row.

But, as soon as I run another macro, it simply stops working entirely and I'm left with a row permanently highlighted until you manually remove the conditional formatting. All formats and cell colours controlled in the other macro stop working as well. This is the other (edited for publishing) macro, which is used to send an email, and is found in a module:

Sub Send_Mail()
'this sends an email that sends a text message

Dim OutApp As Object
Dim OutMail As Object
Dim MyButton As String
Dim strTo As String

[Code] .......

I didn't write the first macro so I'm not sure where the problem is? I'm guessing there is something in the code that is stopping it from working (i.e. running conditional formats) as soon as another macro is run? Is that was is happening?

View 5 Replies View Related

Saving As Stops Macro Working

Feb 6, 2008

i have a worksheet named for example 'allocation 1' this is a master document and is opened and modified and 'saved as' under a customer name. This then stops a few important macros working properly because they refer to the original title and not the new saved title. Is there any code that will let the macro recognise any new title it is saved under?

View 10 Replies View Related

Excel 2013 :: VLookup Stops Working After Certain Number?

Jan 28, 2014

Excel 2013

Our company has a shipping report spreadsheet that has the job number in column A and the ship date in column F. I want to track other things on a separate spreadsheet. So far, we are only down to row 1440 so in order to account for future jobs, my code on the other spreadsheet is:

=VLOOKUP(A2, '[Shipping Report.xlsx]Monthly shipping schedule'!$A$1:$F$5000, 6, FALSE)

Also, the jobs are arranged by ship date, not by number order.

This has worked perfectly for months but for some reason, it will no longer recognize job numbers greater than 1331. The format is the same between the working and non-working cell. Is there a max limit for vlookups that I am not aware of? If this was a sudden failure, I could nail it down but there has to be an underlying reason I'm not seeing.

Here is a sample of my spreadsheet:
Ship Date
1325 12/27/13
1326 3/10/14
1327 1/4/14
1328 1/31/14
1329 3/3/14
1330 1/22/14
1331 1/15/14
1332 #N/A
1333 #N/A
1334 #N/A
1335 #N/A
1336 #N/A
1337 #N/A
1338 #N/A
1339 #N/A
1340 #N/A

View 9 Replies View Related

Button In Sheet1 With Macro Copied To Sheet2 And Sheet3 Not Working

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


[Code] ........

View 5 Replies View Related

Macro Stops After After Opening Another Workbook

Sep 3, 2007

I am a novice at excel macro programming. My Excel 2000 macro inexplicably stops after the line: Workbooks.Open Filename:=stAddrFullname, ReadOnly:=True

1) I know that the remainder of the code is not executed because a breakpoint on the following line is never reached.
2) I know that this line is executed because the stAddrFullname workbook is clearly opened and is displayed when the code stops prematurely.
3) No error message is displayed and I have no 'OnError' instructions in the code.
4) (This is the bit I really don't understand). If I put a Breakpoint in the macro anywhere in the function preceding the "Workbooks.Open" statement, the macro reaches the Breakpoint and then runs through to completion with no problem. When I remove the breakpoint, it reverts to stopping as soon as it has opened the Workbook.
5) The stAddrFullname workbook has no macros in it.

View 5 Replies View Related

Disable Macro If Workbook Copied

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

Change Sheet Macro Name In A New Copied Workbook.

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

Assigned Macro Name Does Not Change When Workbook Is Copied And Renamed

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

Code Stops Working After X Rows

Nov 23, 2008

I'm using Excel 2007 and my s/s is 360000 rows deep.
To cut+paste formulas+formats from one column to another I'm using the following
Sub move_formula_and_formats_from_I_to_L()

Dim cell As Range

Application.ScreenUpdating = False

For Each cell In Range("I1", Cells(Rows.Count, "I").End(xlUp))
With cell
If .HasFormula And Not .Offset(1).HasFormula Then
.Cut Destination:=.Offset(1, 3)
End If
End With
Next cell

Application.ScreenUpdating = True

End Sub
The code stops working after 159000 rows and highlights (in yellow) the line:
.Cut Destination:=.Offset(1, 3)

View 9 Replies View Related

Command Button Stops Working

Jun 13, 2009

I have a command button on worksheet2 of my spreadsheet, which has a macro assigned that will take the user to worksheet1 when it is clicked.

My problem is that when I hide worksheet1 using xlSheetVeryHidden - the maro seems to die and the command button no longer works.

View 9 Replies View Related

Active X Control Stops Working

May 16, 2006

I have joined out of desperation relating to a problem with an Active X control inserted into a userform. This control accesses and returns data from an instrument on the RS232 PC comms port. All works well until I try to save, copy or print the workbook using usual VBA code, then mysteriously the code just stops, and Excel needs to be restarted again. It seems to just get lost!

View 2 Replies View Related

Macro Not Working On Other Spreadsheet

Feb 7, 2014

The attached workbook contains a macro ("Transfer_Data") that copies values from one cell to another when the "Update Progress" button (near cell A3) is selected. It works perfectly fine in this workbook but when I try the same code in another workbook, which looks and operates exactly like this one, nothing happens.

Is this due to a security setting or missing code line items?

copy&paste in next blank cell2.xlsm

View 10 Replies View Related

Excel 2010 :: OnTime Macro Stops Running When Window Minimized

Dec 12, 2013

I 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.

View 6 Replies View Related

Excel 2010 :: Hyperlinks Not Working When Spreadsheet Moved

Oct 17, 2013

Using MS Excel 2010.

As I understand it hyperlinks set in a spreadsheet should work regardless of where you save and/or copy the spreadsheet i.e. as long as the directory location of the location / file the hyperlink is referencing doesn't change (notwithstanding, the user must have appropriate access rights to the relevant directory locations)

However, the situation I have is the spreadsheet was generated and saved to directory location A (intention being this would be the 'master' yearly template) and the hyperlinks set, all of which work when you open the spreadsheet in this directory location. But when I copy the spreadsheet to directory location B, which is intended to be the yearly 'register' location for the spreadsheet, none of the hyperlinks work? When I hold my cursor over a link in the copied spreadsheet, in directory B, I do not get a 'pop-up' path like I do in directory A and when I click a link I get an error message:

Microsoft Excel The address of this site is not valid. Check the address and try again.

As stated above the documents the hyperlinks relate to have not moved, they remain in the same template directory (i.e. the link path has not altered).

View 1 Replies View Related

Formula Stops Working After Performing Certain Tasks

Nov 21, 2006

I was given the following formula by Domenic some time ago that I use along with VBA code supplied by Fin Fan Foom to open do a lookup on a closed workbook:

=If(ISNUMBER(MATCH(MIN(If( Date=D2,If(ABS(Time-E2)<"0:30:30"+0,ABS(Time-E2)))),If(Date=D2,ABS(Time-E2)),0)),INDEX(Contact,MATCH(MIN(If(Date=D2,If(ABS(Time-E2)<"0:30:30"+0,ABS(Time-E2)))),If(Date=D2,ABS(Time-E2)),0)),"")
Everything works fine, until I attempt to…
- cut & paste a block of cells, or
- do a SaveAs, or
- change a worksheet name

The formula will no longer function after performing any of these tasks (the cells go blank), and I have to close the workbook without saving to preserve the original workbook functionality. All other tasks, including even importing of different files into the workbook have NO negative effect.

The formula uses an “INDIRECT” named range. Sheet name changes are reflected in the named ranges, but any change of names somehow causes a malfunction (no sheet names are referenced in the VBA).

I thought that the large lookup workbook (40,000 rows) may be a problem, but even after deleting most of the data and retaining only a few rows, the problem still remained.

After the formula cuts out (after performing the above tasks), if I open up the lookup file, then the lookup will work again, but if I close the lookup file, it will stop working.

I doubt the problem is in the VBA code since IT WORKS, and besides, I have other simple VLookups formulas that also uses the same process (and VBA code) and they continue to work just fine when that one formula gives out. The only thing I can think of is that the complexity of that one formula may be an issue.

Anyway, I the original thread is long, so I’m starting a new one here. The original thread is here (Domenic’s formula, pg 7; FFF’s code, pg 8):
Dedicated Cell To Choose Lookup Table

View 9 Replies View Related

Taking Over Spreadsheet With Macro Not Working?

Jun 3, 2014

Debug error points to:

BuildPlan(Worksheets(SourceWorksheet).Cells(j, "A"), Worksheets(SourceWorksheet).Cells(j, "E"),
Worksheets(SourceWorksheet).Cells(j, "F")) = Worksheets(SourceWorksheet).Cells(j, "K")

Sub is below:

Sub UpdateMonthForecast()
Dim Month As Integer
Dim Year As Integer


View 2 Replies View Related

Conditional Formatting Stops Working After Adding Column

May 21, 2013

I have a conditional formula that highlights dates red when it meets a certain criteria. The file named MS Working, the conditional formatting formula (formula below) works as it should with no issues. The other file MS NOT Working, the conditional formatting formula (formula below) has stopped working as it should. What I did? I inserted a new column to the left. Where the MS Working file has only 1 column to the far left, the MS NOT Working file now has 2 columns to the far left.

It seems as if the formula adjusted itself when I inserted the new column; however, its not working.

MS Working
Conditional Formula: =AND(TODAY()>B3,NOT(ISODD(COLUMN())),B3<>"",OR(C3="",C3=0),B3<>0)

MS NOT Working
Conditional Formula: =AND(TODAY()>C3,NOT(ISODD(COLUMN())),C3<>"",OR(D3="",D3=0),C3<>0)

View 3 Replies View Related

Beforedoubleclick Create Sheet Stops Working Intermittently

Apr 27, 2007

I am using a BeforeDoubleClick event to look for a worksheet name and if it exists, go to the sheet. If the worksheet does not exist, it is creates a new sheet by copying a hidden sheet and naming it using a name in a cell reference. It works for the most part, but I am finding that it is having an intermittant issue. Sometimes, when I double-click, it double-clicks the target cell for editing, and then it renames the active sheet to the cell reference rather than copying the hidden sheet. It seems to happen when I reach 35 sheets created, but not always. Here is the

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim WSname As String
Dim WScheck As Worksheet
Dim WScheckname As String
If Not Intersect(Target, Range("A5:L85")) Is Nothing Then
WSname = Range("L" & Target.Row)
On Error Resume Next
Set WScheck = Sheets(WSname)
If WScheck Is Nothing Then 'Doesn't exist so create it
Sheets("Master_SLP").Copy Before:=Sheets(Worksheets.Count)
ActiveSheet.Name = WSname.........................

View 5 Replies View Related

Excel Macro To Insert Copied Row To The Cell Currently Highlighted

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

Reference Not Working When Copied

Oct 16, 2008

I've been trying to copy and drag formulas on my spreadsheet by the references are not working. Example. I have =$B1 in cell A1. If I copy and drag that to A2 it remains =$B1. And it should change to =$B2, correct? Same as if if make the column absolute.

View 9 Replies View Related

Protecting Spreadsheet From Being Copied And Used

Dec 7, 2008

I have a spreadsheet model I want to give to my students. I do not want them to be able to copy the spreadsheet.

Know of a product which can protect a spreadsheet from being copied and distributed to non intended users.

Something like "pairing" to a computers ID.

View 6 Replies View Related

Speed-up Slow Macro Code: Entries In Column 73 - 85 Will Be Copied From WorkbookRust To The Other Workbook If The Numer In Column 5 Is The Same

Mar 25, 2008

my existing macro, as the run takes to much time to complete. (at least 20min) I've already tried several loops, but no one worked for me. Following situation: There are two excel files, entries in column 73 - 85 will be copied from WorkbookRust to the other workbook if the numer in column 5 is the same. Not every cell within this columns contains data, so the macro should automatically jump to the last entry in each of the above mentioned columns, instead of predefine the range as you see in the code below. After the data is copied to the other workbook, it will be filtered acc. to Sub FilterMain and then copied back to WorkbookRust. As already said, the whole thing works, just to lame.

Sub Allmacros()
Dim WorkbookRust As String
WorkbookRust = ActiveWorkbook.Name
ChDir "C:Documents and Settings vogtMy DocumentsRüstplausch"
Workbooks.Open Filename:= _
"C:Documents and Settings vogtMy DocumentsRüstplauschCH_Revenue_2008.xls"
Application.run ActiveWorkbook.Name & "!UpdateEntries"
Application.run ActiveWorkbook.Name & "!FilterMain"
'not ask to overwrite existing file
Application.DisplayAlerts = False
End Sub

Sub UpdateEntries()......................

View 7 Replies View Related

VBA Macro Not Working When Workbook Is Protected

Apr 23, 2008

I wish I could have been more specific with my Title. This is what I am encountering. I used the McGimpsey code for generating a unique sequential number every time a workbook opens. I tailored the code to fit my needs and stuck it into my invoice sheet. For the longest time I was unable to make it work until I moved the code from a template to a standard workbook. Then I was able to make it work as long as the sheet was unprotected. I realized that to get the code to work I had to then unprotect the cells that the code was writing to. Now that I've done that I am getting errors in the code that weren't there before, rendering the macro useless. The code I am using is:

View 14 Replies View Related

Excel 2010 :: Command Button In Shared Workbook Not Working?

Sep 7, 2010

I am using Excel 2010. I am using a shared workbook that was originally created in Excel 2003. This workbook has a command button that is supposed to be clicked to open a userform. However, I cannot even click on the button when opening the shared form in Excel 2010. When the form is not shared, the button works fine.

I have no problems with the button when opening the shared form in 2003 or 2007.

Is there anything I can do to make the command button work while the form is shared and while using Excel 2010?

View 4 Replies View Related

VBA Script Stops When The Workbook Is Not Active.

Jan 3, 2010

I work in Excel with a VBA script. The purpose of my script is that every 5 seconds a procedure will be executed. This works well. If my workbook is not active, it does not work. Or when I work with another workbook. Or when I work with another program. Then the script will stop to execute. It looks like a break. I have tried several scripts that have the same effect. I want the script continues normally with execution.

View 3 Replies View Related

Workbook Close Stops QueryTable Refresh

Aug 22, 2006

I have a bunch of workbooks that are used to capture information about implants used in surgical cases. I use a small VBA process each morning to update a querytable which contains all of the core patient information for future cases, however it is not working properly. The code goes through the process OK, or so it appears, opens and closes each sheet, and saves them appropriately, but the query table doesn't seem to refresh no matter what I do.

Sub RevisedRefresh()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.LookIn = "G:SHSTheatreCommonReceptionImplants"
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)...............................

View 3 Replies View Related

Macro To Migrate Access Database To Spreadsheet/Workbook

Sep 7, 2006

I would like to know how to migrate an Access database to a spreadsheet, I do not mean Exporting.

More specifically, the migration is conditional. The spreadsheet only needs a few fields from specific tables in the database. It is basically a data dump, however, the magnitude of the amount of information that I will be handling will be far too time consuming and not within the scope of the budget to do this all by-- Exporting-->Copy-->Paste method.

Also, changes often need to be made "The night before..." so the macro will need to be able to ran possibly a number of times to update the additional data or changes in data.

In my research I have found a few snipets of VBA code from the MSDN website as well as some other websites, however none of it seems to be consistent with one another and ultimately results in it not working.

Is this possible?

View 9 Replies View Related

Macro Stops Running The Macro Completely And Hangs In The Middle

Jul 3, 2007

Here in our department we made a pretty elaborate macro that takes a report and sorts them out to 17 different sheets in a one workbook. This Macro pulls a file from a specific location on our server and then opens the CSV sorts it out color codes all the important information and saves it back onto the server under you specific initials.

They are four PC's along with our Managers laptop that run this Macro daily.

About 3 weeks ago my Managers laptop stops running the Macro completely and hangs in the middle of the whole thing. Eventually crashing Excel.

We try to remove the modules and re-import them back into the personal macro workbork but this does not work. The Macro's did not change and still fully function on the other four desktops to this day.

I uninstall Office on my Managers laptop and reinstall. Import the Modules again and still hangs up in very same spot it did 3 weeks ago.

I've tried to lower the macro security to the lowest level also and I've still had no luck with this laptop. I don't understand. The Macro's function perfectly on other PC's but will not function on this laptop.

View 9 Replies View Related

Excel 2010 :: Insert Macro Not Working

Sep 20, 2013

I am using this macro below to paste pictures from a folder. This works perfect when run on excel 2007, however it only links image when this is run on excel 2010. How to eliminate the error.

Option Explicit
Sub alex_it_saving_lives()

'get the image folder path
Dim strImagePath As String
strImagePath = GetImageFolderPath
If (Len(strImagePath) = 0) Then

[Code] .........

View 3 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved