Pasting Without Selecting And Speeding Up Macro In Background
Mar 14, 2013
1. I need to paste data that is copied from an internet window into sheet1 without selecting the cell so that I can continue working on a data in sheet5. The macro prevents me from editing sheet5 in an orderly fashion because when it goes to paste the data into sheet1 it selects the cell in sheet1 interupting what I am trying to manually do with the data in sheet5. How to paste data into a cell withou selecting the cell so that my program stops interupting what I am doing in a different sheet?
2. My macro is meant for real-time defect monitoring in a production facility. The macro opens an IE window that contains the defect occurences, copies the data, pastes it in sheet 1 then sheets2 and 3 analyze the data, closes the IE window, and then waits for a time period before repeating the process. This macro is so slow though that it is hard to work on this file while the macro is running. How to run the macro in the background and speed it up so that I can continue working on Sheet5 of the file while the macro repeats itself over and over again? Here is my code.
The variable website is determined by earlier parts of the code that direct the macro to a specific network website.
Sub Half_Hour_Data
1
'Open Internet Window and Navigate to Website
Set myIE = CreateObject("InternetExplorer.Application")
myIE.Navigate Website
myIE.Visible = True
Application.Wait Now + TimeSerial(0, 0, 10) needed to wait until the page loads
[code]....
Is there a way to speed up the macro by removing the timeserials and just telling it to continue when the task is complete. Then run the whole thing in the background so I can work on another sheet while this program runs?
View 1 Replies
ADVERTISEMENT
Jul 24, 2006
I want to paste into cell A1 of Sheet1, but without selecting that cell first. This code works, but it selects the cell:
Range("A1").Select
Sheet1.Paste Range("A1")
I've tried different variations such as:
Sheet1.Range("A1").Paste Range("A1")
or
Sheet1.Range("A1").Paste
but they result in errors.
View 2 Replies
View Related
Mar 13, 2008
This may be a long shot, but I've been working on a macro today which runs a few Vlookups, does some copying and pasting and some re-aligning....basically nothing too complicated. The Vlookup is looking through approx 45,000 records and returning about 200 - 300 on average, this info returned is company name, address, contact info etc etc.
This macro is taking a ludicrously long time to run, I've checked through and removed any coding that I think is unnecessary without altering the final outcome of the macro.
It still takes ages. Has anyone got any ideas why this may be, or any tips of speeding it up?
I have a brand new computer, dual core processor, 2 gigs of ram etc so it shouldn't be performance of the machine slowing it down, and I ran it with the least applications open that I can (I'm at work so can't close everything, but none of the other applications are memory hogs or anything). I'm running on Office 2003 on Windows XP.
View 9 Replies
View Related
May 13, 2008
I am running a rather large simmulation where I have 12 for loops with each running between 0 and 100% with variable steps and the where the sum of the 12 variables has to sum to 100% and sometimes combinations of the sums of a few of the 12 have to sum up to specific values. That part works, now I am trying to get this to run faster.
Question:
Is there a different design that might be able to run this process more efficiently?
If not, is there a way to speed up the process by letting the computer know that all these variables are between 0 and 1 (or 100%) and that I only need 3 digits after the decimal if the value is in % form?
(the only problem with the rounding or cutting off the decimals at the end that I can think of is that it will only speed up the process a bit but will also make the summing upto 100% or any other value difficult)
View 9 Replies
View Related
Jun 28, 2007
I need help with speeding up my macro. I have been building this using alot of the recorder and the maco is just horribly sluggish. I don't know if it is possible, but it seems like it is. I need the macro to obviously work the same way. The way it has been recorded, the order of operation is important. But if any one knows how to do this better it would be a huge help. The slowest part is during the insertion of the formulas into col. J,K,&L takes like over 7min -- Macro does work perfect though.
View 9 Replies
View Related
Aug 23, 2007
This code is pretty slow if most rows have to be hidden. I'm certain there is a faster way to do this but I'm not seeing it.
What am I doing with
For each row from 12 to 236 I'm looking to see if there is data in any cell in columns A:F, H, & J. If not, I hide the row. I'm starting at row 236 and working up to 12 continuing to hide rows until I find data or I reach row 11. As soon as any row has data or row 11 is reached the the procedure ends leaving all remaining rows visible ...
View 9 Replies
View Related
Aug 27, 2009
Ive found a few ways to speed up the macro in my workbook thats taking about 40 minutes to process usually.
One way - Press Escape and it goes about twice as fast! - Wy does it do this?
Another way - Inserting these lines into the code -
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic
View 9 Replies
View Related
Mar 27, 2014
I want to select the dates(from column H) in which the value(in column I) is greater than zero and paste them in column K(Attachment). At the same time, I want to expand each day for 24 hours. That means, I want to repeat the date 24 times while pasting in column K.
View 5 Replies
View Related
Jul 14, 2008
Delete rows where column A and B are duplicated.
However, this runs extremely slow. I had to stop it after about 10 min, as it only got to about row 1000 of 20,000.
Sub Delete_Dupes()
Dim rw1 As Long: rw1 = 1
Dim rwx As Long: rwx = rw1
Dim stepx As Integer
Dim co1 As Integer: co1 = 1
Dim co2 As Integer: co2 = 2
Dim bool1 As Boolean
Dim bool2 As Boolean
Dim bool As Boolean
Dim count As Integer
count = 0
Do Until Cells(rwx, co1) = ""
stepx = 1
If rwx > rw1 Then
On Error GoTo NewCrit
bool1 = IsError(Application.WorksheetFunction.Match(Cells(rwx, co1), Range(Cells(rw1, co1), Cells(rwx - 1, co1)), 0))
bool2 = IsError(Application.WorksheetFunction.Match(Cells(rwx, co2), Range(Cells(rw1, co2), Cells(rwx - 1, co2)), 0)).................
View 9 Replies
View Related
Jul 7, 2009
i'm working on floyd algorithm macro for a project,
problem is, the spreadsheet matrix is huge, it's a 1043 x 1043 spreadsheet
And this is taking wayyy too long, i've been waiting for almost 2 days already..
here's the file :
http://ifile.it/6v2j39f
(You start the macro using "run" command in "input" sheet)
So is there any way to speed it up?
Or if anyone got a supercomputer, could you help me process this? :p
I'm thinking about changing the 10^5 value into infinite value, but i can't find a way to display infinite value..
And here's some reference for Floyd's algorithm,
http://ifile.it/12p5zle
I have tried to split the algorithm to make it process one iteration at a time, but i've been waiting like 2 hours with no result,
and that's for 1 iteration, can't imagine how long it takes for 1043 iterations
You can do this by deleting the.
View 14 Replies
View Related
Feb 27, 2009
My macro generates some graph from some datas. Datas are in some range and when I filtering it shows only filtered values. I use Fixed axis (min and max) because if I use Auto it fit data to the window.
My problem is: I would like to use Auto axis because my layout would be better but bacground picture remains the same and it's useless. Background picture should change as I change filters.
example:
untitled.JPG
here is picture:
current situation is 1.
With Auto axis I get 2. (blue B and green D are filtered results)
And I would like to have 3.
Is it possible that background picture variate as datas variates?
View 11 Replies
View Related
Feb 21, 2008
i have a cell in a specific workbook which i intend to update it using a macro every min. but during the interval, i might look at other excel files. thus, i hope the macro of that file can run at background.
View 3 Replies
View Related
Aug 6, 2008
I am trying to develop an Excel spread- sheet by using some macros. In detail, in my first worksheet, I have something like -
Exp1 ....
Exp2 ....
Exp3 ....
:
:
:
Expn ....
This rows are coming from another worksheet and I have used a macro (developed by me) to populate these rows. Even the number of rows to be populated is not fixed.
However, I would like to add one row with text - " Total Expense" after those rows. This row will have some background color - say Tan - and font color - say Green - and also a border around its own - in nature, the border will be thick, Double and with color Rose. I have tried the following code in macro but not working properly -
Sub Include_Fields_n_Format(loc As String)
tot_exp_scell = loc & f_scell
tot_exp_ecell = loc & (f_scell + 1)
Range(tot_exp_scell, tot_exp_ecell). Merge
Range(tot_exp_scell, tot_exp_ecell).WrapText = True
Range(tot_exp_scell, tot_exp_ecell).Interior.Color = RGB(150, 150, 150)
With Range(tot_exp_scell, tot_exp_ecell).Font...................
View 4 Replies
View Related
Jul 16, 2007
I've been working on the following code and need to find a way to speed it up.
The premise is that I have a list of columns (From C2 to potentially EH and beyond) and these columns will contain the values Y, N, end or nothing. I'm looking for the quickest way to zap along the columns and delete all that have an N and to stop when end or "" is reached.
There are 2 versions as I'm not sure which one is best ...
Range("B2").Select
LastColumn = Range("A1").End(xlToRight).Offset(, 1).Column
For k = 2 To LastColumn
ActiveCell.Offset(0, 1).Select ' right 1
If ActiveCell.Value = "Y" Then Next k
End If
If ActiveCell.Value = "N" Then
ActiveCell.EntireColumn.Delete 'If employee has no sickness then delete column
ActiveCell.Offset(0, -1).Select
End If
If ActiveCell.Value = "end" Then Exit For
If ActiveCell.Value = "" Then Exit For
Next k
or a longer version
StripSickness:
Range("B2").Select
ActiveCell.Offset(0, 1).Select ' right 1
PrintView = ActiveCell.Value 'Y/N whether employee has data
If PrintView = "Y" Then GoTo StripSickness
If PrintView = "N" Then GoTo NotNeeded
If PrintView = "End" Then GoTo Complete 'last column shows "end"
If PrintView = "" Then GoTo Complete
NotNeeded:
ActiveCell.EntireColumn.Delete
ActiveCell.Offset(0, -1).Select
GoTo StripSickness 'once employee is deleted, go back to stripsickness process
Complete:
Range("a1").Select
View 9 Replies
View Related
Oct 21, 2008
I have an excel sheet in which many cells have a background color of Green. These cells are located all over the sheet. I want to change the background color from Green to Red not affecting the color of other cells in the sheet.
I will be realy really thankful, as I have to do this exercise for more than 50 worksheets.......If I start doing it manually by selecting each celll with green color background and changing it to red.....you can well imagine my condition.....my life would get shorten significantly while doing this.
View 6 Replies
View Related
May 4, 2009
I am working with a excel file having some auto open macro,which process my data and file closes automatically. but during this whole operation the excel window appears on my desktop for about one second. I need this excel window must not appear and all operations in excel must be performed in background only so that i can save my processing time.
View 9 Replies
View Related
Jul 17, 2007
I've been working on cleaning up some code that copies particular information from one sheet (as the original must remain unedited) and then edits out irrelivant information.
However I've noticed that my "before" code moves far faster than my "after" code, which is odd considering that I've cleaned out a lot of excess code and the end results are the same.
I attach an example of my old fast code and my new slow code - can anyone advise where I have gone wrong ? Run the fast code first and then the slow one - you'll see the difference!
View 9 Replies
View Related
Apr 9, 2014
Getting a macro to work. I've looked through the forums pretty extensively but ad I'm not too hot with the old vba, I haven't been able to get it working.
I have two worksheets in the same workbook. The first worksheet, let's call it Input, is one for data entry; and the second one, let's call in Output, is formatted for printing.
There are 8 drop down boxes from a data validation list, that when a particular option or three are selected, I need the Output worksheet to unhide only the rows associated with those options.
In trying to get this all to work, I'd added a function in the cell to the left of each option in the Output page that will show the text "show" when that option is selected on the Input page, or the text "hide" if not selected.
I think I could do this with some time with a clunky and long macro, but would prefer to us some kind of "for each" option to hide rows that have "hide" shown in column A, as I'm looking at a range of 100~ cells.
View 3 Replies
View Related
Apr 20, 2014
i am looking macro code to extract data from sheet1 to sheet2 & sheet3, with criteria background color :
1) extract data from sheet1 to sheet2 for data contains/highlighted yellow color background
2) extract data from sheet1 to sheet3 for data contains/highlighted red color background
For detail information, see attached workbook : extract after coloring.xlsx
View 3 Replies
View Related
Aug 20, 2007
first, id like to thank the board for the help on setting up an array or ranges.
ive been able to get my code to work but its painfully slow...if anyone had any recommendations on speeding it up would appreciate it...i was playing around with using formulaarray instead of writing and reading the data on each loop but cant get it to work...and i'm not really even sure thats the right way to go.
i copied the entire code below...what i was doing was taking 8 time series and looping thru each one to calculate some basic statistics...i ran the function EDFlyRateFromPrice to calculate another series of data which i then run the standardize, max and min functions on.
View 9 Replies
View Related
Jun 23, 2008
This part of my macro can be improved as it runs through over 100 sheets and takes a while?
Sheets.Select
For Each sh In Worksheets
With sh.PageSetup
.Zoom = False
.LeftFooter = "&F" & Chr(10) & "&A"
.CenterFooter = "&P of &N"
.RightFooter = "&D"
.FitToPagesWide = 1
.FitToPagesTall = 1
.LeftMargin = Application.InchesToPoints(0.15748031496063)
.RightMargin = Application.InchesToPoints(0.15748031496063)
.TopMargin = Application.InchesToPoints(0.393700787401575)
.BottomMargin = Application.InchesToPoints(0.354330708661417)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.196850393700787)
.TopMargin = 1
.BottomMargin = 1
.Orientation = xlPortrait
End With
Next sh
View 9 Replies
View Related
May 17, 2013
with performing the following tasks with VBA:
1). For each cell that in the range that has a Red background delete the entire column
I have attached a sample spreadsheet where I have tried to do this. It's not quite working. It does not seem to work when there are RED cells next to each other.
I know that I can do a simple thing like column("A:A"), but I'd rather have it in a loop as in the sample code, in case the columns change in the future.
2). The second task is to delete the rows in the spreadsheet where the first cell in the row is a blank.
View 4 Replies
View Related
Mar 15, 2013
creating a macro. I would like the background color of certain cells under column A (cells 12:25, 27:32, 34:38, 40:45, 47:51, 53:61, 63:70, 72:79, 81:88, 90:92) to change to the color green and display the text "Complete" on mouseclick.
Before the cell is clicked, it would be the color red, with no text. (I'm not sure if you need to know that).
Next, I would need a macro that resets everything back to red. I hope this macro could be assigned to a form button.
View 1 Replies
View Related
Sep 17, 2008
My boss wants a spreadsheet that has multiple buttons on it that will change the background color of a specific cell to four different colors. Example:
Text written in Cell B3, Button in Cell A3 that will change the Background color of Cell B3 to either Green, Yellow, Red or Blue.
There will be a lot of buttons on this worksheet following the same format as above. I don't want to change the value of what is in the cell, just the background color.
View 7 Replies
View Related
Oct 28, 2013
How to make a text background on editable cells and print without text background?
View 4 Replies
View Related
Mar 4, 2014
I am putting together a workbook which calculates a rota for 5 days that shows overall headcounts every 15mins.
It currently has 12672 cells containing SUMIFS.
But this takes a very long time to calculate.
Is there a faster / more efficient way for me to approach this task?
Can I attach the workbook so you can see what I mean?
View 1 Replies
View Related
Jan 15, 2007
Did not want to hijack Jonny's thread about ways in which to update and streamline code to make it run faster. The update speed of my first attempt at a macro is woefully slow - 5 minutes for 2.5K records. I was wondering if some of you more knowledgeable folk could look at my code and make suggestions as to how it could be modified to run faster.
When I ctrl-break out of it I usually end up in a private sub that concatenates columns A & B in Column C (see below). When it resorts the database it must keep triggering this concatenate sub which (I think) is slowing things down.
Concatenate Private Sub:
Option Explicit
Private Sub CommandButton1_Click()
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub ......................
View 9 Replies
View Related
Apr 17, 2008
Does anyone know how to use a macro to make a cell background blank if it is not orange or dark blue?
View 12 Replies
View Related
Apr 19, 2014
I am looking some macro code to extract data based on background color (yellow color) from sheet1 to sheet2
for detail like this below :
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
[Code] ........
View 5 Replies
View Related
Jan 11, 2009
I am trying to use the code below to select certain information from different areas of each sheet in the workbook and paste it onto the List sheet one after the other so I end up with a long list made up of short lists off each sheet. The macro runs through each sheet OK. each sheet has the correct area I want to copy highlighted, so I know it's selecting the right area for copying, but the 'List' sheet is blank, i.e. nothing has been copied to it.
This macro is one I'm trying to put together from other macros I already have, and they all work
Sub Generate_Repair_Kit_List()
For Each ws In Sheets
ws.Activate
Const testColumn = "A" ' change for your data
Dim lastRow As Long
Dim topCell As Range
Set topCell = Range(testColumn & 1) ' top of column
lastRow = Range(testColumn & Rows.Count).End(xlUp).Row
'work up from the bottom until we hit
'a cell that has a border on the bottom edge.
Do While lastRow > 1
lastRow = lastRow - 1.........
View 9 Replies
View Related
