After Printing, The Macro Works Very Slow

Apr 5, 2007

I programmed a simple macro (attached to a button) that scan the lines in the sheet and uses the function: Cells(y,2).EntireRow.Hide=TRUE (in order to hide certain lines). Usually, It runs fast without problem. But when I print out an area of the sheet, it cause the macro to slow down substantially. (when it runs again). In order to fix that , I had to close the file and reopen it again ( and not print out)

View 7 Replies


ADVERTISEMENT

Slow Running Macro

Oct 3, 2007

Below is the entire code that I am using. It is a simple routine which checks whether a part has started its release process or not, based upon dates. The code works and does what I want.

The problem that I have is that it is very slow, for example it takes 35 seconds to go through 530 lines items. In my (limeted) experiance, based on other VBA doodlings this slow.

View 9 Replies View Related

Slow Delete Row Macro

Mar 9, 2009

I have got the macro below, my problem is that when you run it, it takes 20 seconds or so. In my other larger spreadsheet it was almost instant.

View 4 Replies View Related

Macro Running Very Slow

Jun 11, 2009

I have the following macro in a worksheet...and it is running very slow. There are other macros in the worksheet and they all run very well. Any ideas by looking at this code why it would be so slow in running?

View 7 Replies View Related

Macro Runs Slow

Nov 13, 2008

The code below runs on a spreadsheet that has approx 600 rows which INDEX and MATCH another spreadsheet which has approx 600 rows. I takes about 4 mins to run.
Sub QC()

Range("T1").Select
ActiveCell.FormulaR1C1 = "Compared CCCD"
Range("V4").Select
Columns("T:T").ColumnWidth = 15.29
Range("S1").Select
Selection.Copy
Range("T1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("T:T").Select...................

View 9 Replies View Related

Slow Macro Loop

Jan 10, 2007

Need some alternative code that would speed the execution of this macro. My weak attempt runs noticeably SLOW.

Dim cell As Range
For Each cell In Range("TPD")
If IsNumeric(cell) And Not IsEmpty(cell) Then
If cell.Value > 0 Then
cell.EntireColumn.Hidden = False
Else
cell.EntireColumn.Hidden = True
End If
End If
On Error Goto 0
Next
End Sub

View 2 Replies View Related

Macro Running Slow

Jan 30, 2007

I have a macro that isn't very complex but it is running very slow, does anyone know any tricks on how to speed it up?

View 9 Replies View Related

Macro Works Stepping Through But Not When Run On Its Own?

Jul 21, 2014

I have a very simple macro that copies data from one worksheet to the other. This macro worked fine in Excel 2007. I have since migrated to to 2013. The newly arisen problem is that when I run the macro, the data is not copied to the 'Previous Part Data' worksheet. However, if I step through the code line by line, it works fine.

Here is the code:

VB:
Sheets("Current Part Data").Select 'Select the 'Current Part Data' worksheet
'Copy the data to the 'Previous Part Data' worksheet then clear the data
Range("A4:V" & CurrentPartDataFinalRow).Copy

[Code]....

I have two questions:
1. Why did this work in 2007 and not in 2013?

2. Is there something I can do to get rid of the sheet selection statements to avoid cluttering my code?

View 3 Replies View Related

PDF Macro Works With Windows But Not On Mac

May 18, 2014

I've sent the file to 2 different windows computers and it works fine, but doesn't work with the two macs that I've sent it to.

This is the code:

[Code] .....

View 2 Replies View Related

Macro Works On One Sheet But Not The Other

Mar 31, 2014

In the following user defined function

Code:
Function CA2GT(ByVal S As String) As String
Dim x As Long, CA As Long, Total As Long
CA = InStr(1, S, "CA", vbTextCompare)
For x = CA To Len(S)
If Mid(S, x, 1) = "(" Then

'replace arguments: oldtext, start at number, number of characters, new text

[Code] .......

It works on one workbook but not another workbook. Why?

View 5 Replies View Related

Speed Up Slow Copy Macro

Sep 24, 2007

i am currently using the following code to copy records from one sheet to new sheets that that are created and named in the first part of the if(). this works fine, however when i am dealing with 50,000 records it still takes 5-10min to get them all sorted. I think this although functional is horribly inefficiant, and am wondering how i might be able to speed up the process. possibly rather than check each record, then copy then paste individually to sort them, then read and select the list of cells until value changes, then copy over at once. i dont know if this would be faster or not, let me know what you think.


Sub autorec ()

Dim wSheet
Dim newSheetName As Variant Dim FNAC As Double
Dim OU As Double
Dim DS As Double
Dim CCY As String

View 9 Replies View Related

Diplay Progress Of Slow Macro

Jan 14, 2008

I'm running some macros that take a bit of time (1000-2000ms), and would like to give the user some feedback as to what's happening.... Ideally, I'd like to show a msgbox-like pop-up window saying something "processing..." while the macro executes.

I have tried using userforms (based on this post Excel VBA Macro- How to Create a Splash Screen for your Excel Workbook); however they halt background execution...

Is there any built-in functionality similar to .statusbar, but more obvious/visual that would allow this? I'm about to experiment with showing/hiding a textbox shape, but this seems a bit sloppy...

View 2 Replies View Related

Vlookup To .xlsm Macro Is Incredibly Slow

Jul 23, 2009

This is designed to be run on bid proposals, to calculate special discounts. My idea was that whoever was designing the bid would open this file, which has the discount information and the macro, and then run the macro on the file containing the bid.

This macro works. It worked really fast when it was in my personal.xls and referenced an .xlsx file for the vlookup. It was slower, but still tolerable, when I moved it to the .xlsm file and it still refenced the .xlsx file (an oversight on my part). Now that I fixed the vlookup reference to the .xlsm file, the same one that the macro itself is in, it has slowed to a crawl. What is going on?

View 2 Replies View Related

Macro Works In 97 & 2000 But Not 2003

Mar 15, 2007

I convert text file into Excel. Then what I want is to look in column C for cell value “UNT:” (it is chopped word account: ) and copy value what is in the same row next column D into column K.

Then in column K copy down the value until next not empty cell.

My problem is that it works on Excel 97 and 200 but 2003 doesn’t work.

View 9 Replies View Related

Macro That Doesnt Work In 07 But Works In 03

Oct 28, 2008

I've been searching around here to see if anyone had an answer about codes and macro changes from 03 to 07. The closest thing I found was something about lists being tables and such. However, I am still unsure of what the issue might be in my case.

Sub ArrangeColumns()
'
' ArrangeColumns Macro
' Macro recorded 3/7/2008 by ****
'

'
endRow1 = ActiveSheet.UsedRange.Rows.count + 1
Range1 = "A1:O" & endRow1

Range(Range1).Select
Range("A3:O39").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects("List1").Unlink
ActiveSheet.ListObjects("List1").Unlist
Columns("A:B").Select
Selection.Delete shift:=xlToLeft
Columns("I:I").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert shift:=xlToRight
Columns("L:L").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert shift:=xlToRight
Columns("M:M").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert shift:=xlToRight
Columns("L:L").Select
Selection.Cut
Columns("G:G").Select
Selection.Insert shift:=xlToRight
Columns("B:B").ColumnWidth = 11.29
End Sub

So here, the red text is what gets flagged when you run the macro. I seem to be getting a Run-time error '9': Subscript out of range error.

I am not exactly sure what the macro does besides clean up and sort a portion of a worksheet. Also, there is not worksheet called "List1," but changing that value does nothing. Is there an equivalent command to those highlighted in red? Or, perhaps if anyone knows of a place where I may reference these commands myself, that would be great as well.

View 9 Replies View Related

Autofilter Macro Works On One Column, But Not The Other

Jul 7, 2006

I'm using Excel to map automotive parts to vehicle applications (two different spreadsheets) for importation into a relational Access database. Production dates for parts rarely match the on-sale dates of the cars, so I've set up a macro that:

1. copies the start production date for a part record into the vehicle workbook at the top of the 'discontinued' column,
2. copies the end production date for the part into the vehicle workbook at the top of the 'release' column,
3. uses these pasted data points as criteria for the autofilter in the vehicle workbook as 'vehicle release date' < 'part end production date' and 'vehicle discontinued date' > 'part start production date'.

I find that the macro works perfectly in the autofilter for the vehicle discontinued date, but not for filtering the vehicle release date. The date is copied and pasted OK. When I check the custom filter dialogue box, the date has been entered in with the 'is less than' menu item selected, but it doesn't bring up any records unless you click on the 'OK' button once the dialogue box is open. I want to run the macro without having to run the autofilter manually at all. To troubleshoot the issue, I broke up the macro into two separate macros, one for release and the other for discontinued. Problem remains, even though the only difference between the two macros is the relative cell addresses. I've checked formatting of the cells for text vs. numeric and that doesn't appear to be a problem.

View 4 Replies View Related

Macro Processing Time Is Very Slow (using Record Function)

Jun 17, 2009

I created this macro (below) using the record function but when I kick it off it takes 40 seconds or more to run.

View 4 Replies View Related

Macro Running Very Slow - Cell (press Esc To Cancel)

Dec 10, 2009

I have written some very basic code to format a report in excel. When I run the code it take a very long time to execute and I receive the following error message at the foot of the page:

Cell (press esc to cancel)

Annoyingly I have had this error before and found the solution on the web but can't remember where. If memory serves my right I deleted some temp files from a specific location on my hard drive?

View 3 Replies View Related

Slow Macro - Multiple Vlookups To External Sheets

Feb 29, 2008

I'm working on a macro which compares values in particular cells across different versions of a sheet. Unfortunately there are quite a lot of values, and my code is running very slowly.

I'm trying to get the data in a grid, with the file version across the top, and down the left the project name. For each file version I need to look up a specific value for every project listed.

At the moment, my macro is creating a seperate formula for each cell which does a vlookup on the project name to the specific external file.

The relevant bit of code is below

At the moment its runnig to slow by several orders of magnitude, Working on a 4 by 10 grid it takes about a minute, and I'm going to need it to be able to handle a 50 by 800 grid.

While the code is running, the CPU is not maxed out, so I'm assuming that its the calls to the file system which are taking all the time.

Application.ScreenUpdating = False

Dim ccount As Integer
Dim rcount As Integer
Dim sFilename As String
Dim sPath As String

With ActiveSheet
For ccount = 1 To Range("c1").End(xlToRight).Column - 2

sPath = Left(FileNamesList(ccount), InStr(FileNamesList(ccount), "Pipeline ~") - 1)

sFilename = Right(FileNamesList(ccount), Len(FileNamesList(ccount)) - InStr(FileNamesList(ccount), "Pipeline ~") + 1)

For rcount = 1 To Range("A65536").End(xlUp).Row - 2

by SDB.xls]Pipeline'!$A$1:$AO$300,3,0)

Cells(rcount + 2, ccount + 2) = "=VLOOKUP(""" & Cells(rcount + 2, 1) & """,'" & sPath & "[" & sFilename & "]Pipeline'!$A$1:$AO$300,3,0)"

Next rcount
Next ccount

End With

View 9 Replies View Related

Macro No Longer Works - Getting Compile Error?

Jul 30, 2014

I created three macros to work with reporting for my company.

One sorts a field, another deletes unwanted columns, and the third double checks columns for answers--I work for a telecom polling firm.

It was working last week on my computer, and it still works on another computer at the office. However, I keep getting an error message when I try to run it.

The message is "Compile Error: Expected Function or Variable". The code is below, and bolded where it breaks down.

--selection.Autofilter

[Code] .......

What could be the rationale for the sudden breakdown? I was testing it on a file that I have tested with before without issue. I also tried a second file, and even restarted my computer. I am currently trying to write a fourth macro for counting responses and giving percentages, so while I don't need to have the perfectly cleaned data to do so it would be nice to have.

View 4 Replies View Related

Countif Function In A Macro: SUM And COUNTA Works

Jan 27, 2009

I'm using the SUM, COUNTA and COUNTIF functions in a macro. The SUM and COUNTA works but the COUNTIF function does not return results.

Sub B_Test()
Dim myRange
Dim Results
Dim Run As Long
myRange = Workbooks(1).Worksheets("Master").Range("S6", Range("S6").End(xlDown))
Range("M3") = Application.WorksheetFunction.Sum(myRange)
myRange = Workbooks(1).Worksheets("Master").Range("D6", Range("D6").End(xlDown))
Range("D3") = Application.WorksheetFunction.CountA(myRange)
End Sub

I have tried countless ways to rewrite the COUNTIF line with no results or compiler errors returned. Originally had problems with the SUM and COUNTIF function and found that column formating was the problem. After clearing all column formats, the SUM function promptly began working but the COUNTIF keeps eluding all my efforts. The column which the COUNTIF is pointed to contains values of 0 to 500. Only values greater than 0 are to be counted.

View 2 Replies View Related

Save As Date Macro Works But Not On Mondays

Jun 5, 2013

I created a report that runs each weekday morning, using data from the previous workday.

I have written code that saves the file into a LAN directory as "BEST CASH MM-DD-YYYY" with the variables being the previous day's date.

My problems is that when I run the report on Monday morning using Friday's data, of course it's including Sunday's date and not that previous Friday...

Here's the test code I'm working with right now; I'm using a folder on my desktop until I get the code right, then I'll change it to the proper LAN directory:

ChDir "C:UsersC700MDesktopTEST"
ActiveWorkbook.SaveAs Filename:= "C:UsersC700MDesktopTEST" & "BEST CASH " & Format(Date - 1, "mm-dd-yyyy"), FileFormat:= xlOpenXMLWorkbook, CreateBackup:=False

View 7 Replies View Related

Macro Works When The User Tabs Out The Cell

Aug 7, 2007

How do you add a macro to a cell that works wen the user tabs out the cell?

View 9 Replies View Related

Macro Works In Debug Mode But Not In Runtime

Apr 7, 2008

I am trying to open a xls file and convert into csv. My macro works when I'm in debug mode. but If i run the macro (Not in debug) mode then After opening a file control is not going to next function. What is problem? Even I am not getting any error too

sub open file (FileName as string)

Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim RowNo As Integer
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
'Set xlw = xlx.Workbooks.Open(SourceFolder + "" + FileName)

Workbooks.Open FileName:=SourceFolder + "" + FileName
' Columns("F:G").Select
' Selection.Delete Shift:=xlToLeft
Save_in_WDrive (FileName)
Set xlx = Nothing
end sub

View 9 Replies View Related

Macro Works On Notebook Laptop But Not On Desk Top PC

Jun 6, 2006

I have this code working on my notebook:

Dim lastrow As Integer
Dim irow As Integer
lastrow = Workbooks("tmp").Worksheets("vRptMOMarkToHedgeFacilities"). Range("C65356").End(xlUp).Row
For irow = 2 To lastrow
Workbooks("tmp"). Sheets("vRptMOMarkToHedgeFacilities").Range("C" & irow).Copy
Windows("vlookupsheet.05.01.06.xls").Activate
Sheets("new stuff").Activate
Range("D" & irow + 1).Select
ActiveSheet.Paste
Next irow
Range("D1").Select

but it does not work on my desktop. For some reason when I go and execute the macro it gives me a "Run-time error '9' - Subscript out of range"

View 5 Replies View Related

2007 Macro Relative References Works On Only One Workbook

Oct 8, 2009

In Excel 2007 I am creating a macro with the "relative references" setting turned on. I want to repeat some tasks like: go to column A, sort, hide columns B thru F, hide columns H & I, return to column A.

Every time I try to create this macro by capturing key strokes, it inserts the workbook / worksheet name in the macro! Kinda defeats the ability to use it in any other workbook.

I am recording the macro in the PERSONAL.XLSX workbook. I have other macros that successfully perform in any workbook they are used in . . . but today I can't create one that doesn't incorporate the name of the original sheet it was recorded in.

View 12 Replies View Related

Script Works In Debugger But Doesn't When Macro Runs

Dec 1, 2009

I am trying to do a very simple Macro for merging data. My problem is that the macro works when I am stepping through in debugger, but does not work when I am in excel and I use the shortcut key (crtl + m) to run the macro. What happens when it doesn't work properly is it selects rows 2 and 3 and tries to paste them to the new workbook and I get an error saying the cells are not the correct type. I don't want it to do this because rows 2 and 3 are titles and have nothing to do with the data I am moving. As I said before, when I open VBE and step through the code it works just fine. The error only happens when there is no data on the initial work sheet.

View 10 Replies View Related

Macro Works Stored In File But Not In Personal Folder

Apr 2, 2012

I have created a simple macro that inserts a header onto a sheet on the first row of a workbook. It works fine when the macro is stored in the file that I am wanting to paste the header into, but when I moved the macro to my "personal" folder it gives me a debug error with this code:

Code:
ThisWorkbook.Sheets("Bid Sheet").Rows("1:1").Insert Shift:=xlDown
ThisWorkbook.Sheets("Bid Sheet").Columns("K:K").ColumnWidth = 50

I am assuming this error is being caused by the phrase "ThisWorkbook" but I do not know how else to reference the workbook I want to run the macro on.

Full Code:

Sub Insert_Header()Dim wb As Workbook'Open Workbook
Set wb = Workbooks.Open("C:NewPage_Logo.xlsm", UpdateLinks:=False)    
'Go out to File and copy logo and header  

[Code] ........

View 3 Replies View Related

Macro Works Unless Change Font Size In Document?

Jul 7, 2014

I have this macro that works perfectly. My boss wanted the subtotal lines within the report to be in a smaller font, so I added lines to the macro to do that. It won't work with those lines in there! It says the reference is not valid on the red line in the code below. The 2 blue sections are the ones I added to change the font size.

Code:
'Add subtotals at the end of each age bucket
For i = 10 To LR Step 1
fr = Range("E" & i - 3).End(xlUp).Row
If Range("B" & i).Value = "Totals" Then
Range("E" & i).Formula = "=SUBTOTAL(9,E" & fr & ":E" & i - 2 & ")"
With Range("E" & i & ":R" & i).FillRight

[code].....

Why would the font size make a difference? Is there some other way to change the font size that would work better?

View 5 Replies View Related

Paste Special As Values Macro Only Works Intermittently

Feb 23, 2008

I often need to use Paste Special... Values, using Excel 2003. So I recorded a Macro into "Personal.xls" and assigned a shortcut key combination "CTRL-Shift-V" so that it would always be available. I first select one or more cells and hit CTRL-C to copy to clipboard, click on another cell, then hit CTRL-Shift-V to run the following macro: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

It often works . . . and it often fails. When it is working it seems to keep working over and over. When it is failing it keeps failing. So it is almost as if there are two "modes" of operation, which for lack of terms, for this post I will coin the terms "fail mode" and "fixed mode". Once I am in one of the two modes, it stays there for a while. When it is in the "fail mode", I always get the dreaded: Run-time error 1004: "Pastespecial method of Range class failed".

Since this problem is very intermittent, I believe this is why there are so many posts in the Internet about this, and so many people are replying back "it works fine for me". I have seen many solutions offered but none work so far for just a simple shortcut key to do a PasteSpecial :Values. Some solutions "work" . . . such as adding the PasteSpecial button to the Toolbar, or pressing ALT-E-S-V <Enter> - but both of these bring up the Paste Special dialog box which I want to avoid. I spent days trying the many solutions offered but so far all I have found is a trick to convert Excel from "fail mode" to "fixed mode". The trick is to add a line to the macro to first do a "Paste Special: Format:

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
...................................

View 8 Replies View Related







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