The code opens a varying workbook with the same format as the source workbook and copies specific columns into the source workbook.
It is currently taking about 20seconds to run.
The worksheets are protected and have merged cells hence the call TestMe line.
My code runs so slow! The sheet only has 233 rows and 6 of them are not in the loop.
View 6 Replies View Related this code to minimize it and avoid copy to clipboard.
VB:
Sub SapOutputRun()
Application.EnableEvents = False
Application.ScreenUpdating = False
[Code].....
I have a workbook with macros Ive created that have been running just fine for 2 years now. Ive upgraded to a top of the line new Dell D830 dual core laptop with 2gb ram and installed excel 2003 (same excel as always). Certain macros that used to take a second or two to run on a celeron dell now take 3min's! Ive stepped through the code in debug mode and found that its hanging up in simple areas like "Columns("A:A").Select" and "Selection.ClearContents". We have 16 older laptops running these exact codes just fine all day long and the 2 new fast computers with core 2 duo processors are hanging up constantly in the same places in code. Ive gone through and made sure all the "option" settings are identical in excel. Is there some system setting That I'm missing or possibly an excel setting That I may have overlooked?
View 2 Replies View RelatedBelow 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.
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 RelatedI have a simple Macro. I assigned to a Form button.
when i click that button systems takes lots of time to complete the macro.
left down task bar it say calcuating cells and load from 1% to 100% ...
I have quite a large workbook, which runs very, very slowly when I'm on a certain sheet.
Scrolling seems to lag, and there is a long delay between me clicking a cell and it becoming active.
The sheet contains a number of web queries, and a number of cells which refernce other cells, both in the same worksheet, and other worksheets.
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?
I was unable to get a loop to reliably run my analysis, and I have locked in specific cells that cannot change, and anything unlocked will need to copy/paste from the row below. I will need this to continue this loop until the dates from the Range B8-B1500 end (Output Tab).
[Code] .....
I have an excel sheet with about 3,000 products listed; there are 26 items of data listed against each.
I've been using Data Filters to manipulate the info, but my worksheet has suddenly started running incredibly slowly - up to 7 minutes to implement a simple filter selection.
There was no problem until I made a couple of changes last week:
1. Adding a simple pivot table
2. Using an Index column plus some linked formulae to extract a list of suppliers from the product list.
I assumed that it may be the INDEX and related formulae or the pivot table that were causing the Data Filters to run slow, so I deleted them, but to no effect. I've even gone so far as to copy the raw data into a new sheet, but it's still running incredibly slowly when I try to filter. I am convinced that the problem is related to some hidden legacy of the table or functions, as it only started once I'd added these, but I don't know if this is plausible in reality and if so how to get rid of it.
i have a simple question regarding vba code involving opening/eleboration/closing of different excel workbooks' format. I saw that with xls/xlsx the macro works with relatively high speed, but it is very slow when it comes to xlsm file. Is there an explanation? Does a command which enhance speed for xlsm file exist?
View 8 Replies View RelatedI have built an Application in Excel that is used by a userbase of 50+ people. Now, as standard, they are only able to access the document read only. The application writes data to an ".mdb" but the user cannot change the application directly istself.
Now, we've had the problem over the last few days that the program is running slowly when it is trying to write data back to the ".mdb" file.
I believe that it could be because connection to the sever that our application is running slowly (or the server is too highly trafficed) but our server team are telling me there is nothing wrong with the server and it's running slowly because there are too many people in one Excel application/
I have my macro that every second does a copy and paste.
but window doesn't copy and paste anything else but the macro's value
is it normal?
1- Open enclosed file.
2- Run Macro1.
3- Wait some time completing running Macro1.
4- Run Macro2.
5- Wait some time completing running Macro2.
6- Look MessageBox to see that what is Macro2 running time. (Note: My Macro2 running time is 06 minutes and 49 seconds)
My question is how can I decrease Macro2 running time ?
I am open all ideas like copy data to NotePad and paste again excel etc.
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?
I have inherited support for a suite of Excel 2003 spreadsheets with complicated macros which run fine on XP. Having been tasked to test them on Windows 7 with Office 2010, I have not converted them as they are run by several sites globally who may not upgrade to Office 2010 at the same time. Hence they run in compatibility mode which in general is fine.
However, certain macros are veeeery slow and to the user would look like the app has hung. In debug I have found that the macro takes 10 minutes plus whenever it hits any of the following code:
Code:
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkBook.PrecisionAsDisplayed = False
It goes slow on each of the three 'lines' so it seems that it is actually doing an auto calc each time!
Is there some configuration I can do to prevent this? Setting auto calc to Manual didn't work and anyway I loose things, like data validation, when I save it in Excel 2010.
I have two files and need to copy the information from columns B,C,F,H rows 5 true 29, and past it in the other file in Columns B,D,G,J; rows 3 true 35.
The code that I have works fine, but have one problem - it past the info in the rows starting from 37 instead from row 3. Here is where I need your help. The code is triggered when the condition Arr ( arrived) is chosen in the column AH ( file Delivery Status) and then the specific warehouse is chosen in the column AI. To make it easy I have left only one warehouse as a choice.
Attached you can find sample files ( I have delete the info from the heading cells as it was in another lenguage)
I need a little look on a Copy Paste Code, as bellow:
View 3 Replies View RelatedI created this spreadsheet a few montsh ago to help with forecasting for my companies products. The tabs of interest are the HIST vs FORECAST, FORECAST TOOLKIT, and the FORECAST TABLE tab – now the FORECAST TABLE tab is always hidden and if you unhide it then it will rehide itself once one of the macro’s is run. I set it that way to prevent people accidently making adjustments to the forecast data.
The way the sheet is supposed to run is that when you are on the hist vs forecast page then you can click one of the grey buttons on the right which will then graph the numbers for the relevant product on the toolkit page – so far so good, this bit all works fine, there are separate macro’s for this each named after the product they control.
Then once you are on the toolkit you can play about with the numbers until you come up with a 6 month forecast you are happy with which will be on the 6 cell line in the bottom right were it says “ IMPORT”. You can then click on the “IMPORT” button which will then copy and paste it back into the forecast table under the relevant setting – the macro for this is called IMPORT2 and it works off a “product” range and a “ date” range – again this pretty much works as intended.
Sub IMPORT2()
Dim nDate, nProd
With Sheets("Forecast Toolkit")
nDate = Application.Match(.Range("O31"), Range("Dates")) + Range("Dates")(1).Row - 1
nProd = Application.Match(.Range("I3"), Range("Products")) + Range("Dates")(1).Column...........................
I'm using code that SHG provided for me here; http://www.excelforum.com/excel-programming/630464-sheet-macro-for-preventing-paste-not-working.html
It worked great in the first workbook I set up but I just set up a new workbook and it's not doing the same thing. For example, it allows me to copy items from column A which have 1 type of validation (restriction on text) to another column which has a formula validation.
It still won't allow me to paste from outside a validated range to inside but I need to prevent people from screwing up the validation between columns.
I have the following bit of VBA code which copies a particular cell and pastes it in the next available cell in column "AD", but I need to paste it as a value. How do I incorporate this into the code?
Sub copypaste()
Worksheets("USD IR Swap 10 Yr").Range("L5").Copy _
Destination:=Worksheets("USD IR Swap 10 Yr").Cells(Worksheets("USD IR Swap 10 Yr")
.Rows.Count, "AD").End(xlUp).Offset(1, 0)
End Sub
I am looking for VBA code that will select a data validation cell, copy the selection, and paste the value of that cell in a different worksheet.
The data validation list is in cell L47. The user will select a date from cell D31, type a description of activities in the adjacent column and then select initials from the aforementioned validation list in cell L47. I need the code to fit into a button I created so that when they click it to approve the activity, the code will copy the value of the initials and paste it into column AB in a separate worksheet. Column AB runs parallel to column A, which contains all of the dates located on Sheet1 in cell D31. I think I might need some sort of loop to run this so that it pastes initials on the correct date.
I have a shape called "Company Logo". Code to copy the shape from sheet 1 and paste to sheet 2?
(I tried macro record but had trouble when I pasted it into another macro).
I want to automate the transferring of specific pieces of data from one spreadsheet into another spreadsheet. There are many project spreadsheets that all feed into a resource plan spreadsheet showing every employee, what projects each of them are on, and how many hours they are spending on each project for the quarter.
Now what I would like to do is to automate this process of feeding the data from the many project spreadsheets into the resource plan spreadsheet. With transferring the specific data from the one to the other. Basically I need it so that excel looks at the project spreadsheet copies the name of the employee and the hours they have worked each week for the quarter and paste It into the big resource plan. What I am finding tricky is knowing how to make it automatically paste into the right area as in copy an employee and paste the details in the same employees cells in the other spreadsheet.
Very slow small vba code. I have the following
View 2 Replies View RelatedI have the following piece of
Sub Summarise_Variations()
Dim myRange As Range
Dim Write_Cell As Long
With Application
.Calculation = xlCalculationManual
.StatusBar = "Please wait - Summarising variations..."
End With
It happily goes through a list and sums multiple records. It might not be the most efficient or best way to do it but it works and when I run it on a couple of stand alone sheets it takes a couple of seconds to process if that.
Unfortunately when I run it as part of the overall application that I've developed it takes ages to run, i.e. more like ten minutes.
The spreadsheet has a few graphs and about 250 sumproduct and array formula live in it but all other formula on other sheets are created and then paste valued as part of other VBA routines. As you can see I've also turned calculation off as the procedure runs so don't understand why it is suddenly taking so long.
way the code below can be made more efficient? Basically, I drop down a combobox, and based on the value i select, it populates a lot of textboxes etc based on the listindex.....
Private Sub ComboBox3_Click()
On Error Resume Next
With Me.ComboBox3
If .ListIndex = -1 Then Exit Sub
Label23.Caption = Sheets("overview").Cells(.ListIndex + 2, "c").Value
Label24.Caption = Sheets("overview").Cells(.ListIndex + 2, "b").Value
Label38.Caption = Sheets("overview").Cells(.ListIndex + 2, "d").Value
TextBox1.Text = Sheets("overview").Cells(.ListIndex + 2, "e").Value
TextBox2.Text = Sheets("overview").Cells(.ListIndex + 2, "f").Value
TextBox3.Text = Sheets("overview").Cells(.ListIndex + 2, "g").Value
It takes about 10 seconds every time i choose another project
I do those things, I set every object = nothing... I even moved them up to right after I use the object. When I open the workbook, my process's shows excel at 38000K. my first go of running code, stepping thru is great... I watch my process and see that I get up to 78000K... I then exit my code, properly... and the process goes only down to about 50000K. If I run it a 2nd time excel process show almost a 100000k and stepping thru is slow, slow motion... a 3rd time and I am at 120000k. I am setting everything to nothing and can't figure it out... I have done bigger projects with more forms, recordsets, collections and never have had this problem.
View 2 Replies View RelatedI have 4-5 worksheets (sheet1,sheet2,etc) on a workbook. All of the rows except for the ones that are filled in are currently HIDDEN.
I have one worksheet called "Add record" which has a VBA code that adds a record to any of these sheets.
When it does this I want it to recognise when pasting the new record into any fo the sheets.. IF the row is hidden, the sheet needs to reveal that row.
I'm desperate trying to get this to work!!
Here is the code I have so far! This is a command button macro used in the "Add Record Sheet" I have FOUR different versions of this code. Each one adds the data to a specific sheet.
[Code] .....