Speed up Macro 3

[mattloflin]

Hey guys I have a macro that reads a sheet then rearranges it and then reformats it. However it takes probably 2 to 3 minutes to run. This is with just Columns i'd say A -> O and about 250 lines.

Here is the code that takes the longest time:

Sheets("Build Sheet").Range("A2:A" & newLastRow).Value = Sheets("PreProcess Doc").Range("B2:B" & newLastRow).Value
Sheets("Build Sheet").Range("B2:B" & newLastRow).Value = Sheets("PreProcess Doc").Range("R2:R" & newLastRow).Value
Sheets("Build Sheet").Range("C2:C" & newLastRow).Value = Sheets("PreProcess Doc").Range("W2:W" & newLastRow).Value
Sheets("Build Sheet").Range("D2" & newLastRow).Value = Sheets("PreProcess Doc").Range("T2:T" & newLastRow).Value
Sheets("Build Sheet").Range("E2:E" & newLastRow).Value = Sheets("PreProcess Doc").Range("U2:U" & newLastRow).Value
Sheets("Build Sheet").Range("F2:F" & newLastRow).Value = Sheets("PreProcess Doc").Range("V2:V" & newLastRow).Value
Sheets("Build Sheet").Range("G2:G" & newLastRow).Value = Sheets("PreProcess Doc").Range("D2" & newLastRow).Value
Sheets("Build Sheet").Range("H2:H" & newLastRow).Value = Sheets("PreProcess Doc").Range("M2:M" & newLastRow).Value
Sheets("Build Sheet").Range("I2:I" & newLastRow).Value = Sheets("PreProcess Doc").Range("J2:J" & newLastRow).Value
Sheets("Build Sheet").Range("J2:J" & newLastRow).Value = Sheets("PreProcess Doc").Range("N2:N" & newLastRow).Value
Sheets("Build Sheet").Range("K2:K" & newLastRow).Value = Sheets("PreProcess Doc").Range("K2:K" & newLastRow).Value
Sheets("Build Sheet").Range("L2:L" & newLastRow).Value = Sheets("PreProcess Doc").Range("P2" & newLastRow).Value
Sheets("Build Sheet").Range("M2:M" & newLastRow).Value = Sheets("PreProcess Doc").Range("G2:G" & newLastRow).Value

it reads from one sheet and puts it on another by using ranges.

Is there anyway to speed this process us?

Also once it redoes everything it says "Calculating" in the bottom left is this necessary? If asked i'll try to upload a copy of the macro or the code. Just trying to get the ball rolling

 

[mattloflin]

that pops up two errors

the first is "Are you sure you want to delete, data may exist.. etc"

second the same 1004 that says cannot rename a sheet to the same name.

Well before I started working on this macro I got it clocked at 3.5 minutes.

Now I got it down around 10 - 18 seconds. But redoing that part was the last thing on my list. So once that's complete i'm done.

 

[ck1999]

I do not think redoing this section will speed up the time much.

I do not know why you get a message since

Application.DisplayAlerts = False

should not give you the 1st msg box (not an error but a question) second you are getting the 1004 error because you are trying to rename a sheet the same name as an existing sheet. Do you have to delete the 1st sheet before renaming it. (That sound more confusing than it is)

I think 10 - 18 seconds is not to bad.

Can you repost your vba file again.

ck1999

 

[AnotherHiggins]

The code ck1999 offered in his last post will delete the sheet "build sheet" if it already exists. The DisplayAlerts = False prevents you from getting an error. So if you run that, it will NOT ask if you are sure you want to delete.

If you are getting the "cannot rename a sheet to the same name" error, that means a sheet with that name already exists. It shouldn't. Delete it and try again.

Copy your sheet names from the actual sheet tabs and paste into your macro - it sounds like you might have a spelling difference somewhere.

Once you've done that, try the code from my post dated 1 Feb 08 15:21 again. I'll repost the code here:

Sheets("PreProcess Doc").Copy Before:=Sheets("PreProcess Doc")
Sheets("PreProcess Doc (2)").Name = "Build Sheet"

Range("A:A,C:C,E:F,H:H,I:I,L:L,O:O,Q:Q,S:S").Delete 'Shift:=xlToLeft
Columns("I:I").Cut
Range("B1").Insert Shift:=xlToRight
Columns("M:M").Cut
Range("C1").Insert Shift:=xlToRight
Columns("K:M").Cut
Range("D1").Insert Shift:=xlToRight
'Columns("K:K").Select
'Selection.Cut
Columns("K:K").Cut
Range("H1").Insert Shift:=xlToRight
Columns("J:J").Cut
Range("I1").Insert Shift:=xlToRight
Columns("L:L").Cut
Range("J1").Insert Shift:=xlToRight
Columns("K:K").Cut
Range("N1").Insert Shift:=xlToRight

What errors do you get with that?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mattloflin]

I reposted the file, this is the update version. I forgot to add the displayalerts.. its now in it.

It comes up with 7 columns having the REF# issue.

 

[mattloflin]

ok Higgins, i posted as you were posting. I tried that, still comes up with the 1004 error. I have alerts as false.

Says cannot rename a sheet to the same name ... etc...

 

[ck1999]

If you were wanting to speed up you code you would of listened to this

anotherhiggins (TechnicalUser)
1 Feb 08 16:00
Looking at your code now....

No one is going to go through 414 lines of code with a fine tooth comb - but you obviously don't expect that, that's why you just posted the chunk above.

Here's an example of avoiding Select from the beginning of your macro. I've also gotten rid of unnecessary calculations of how many rows there are.... If you are deleting everything in a column, just delete the column. I've commented out your original code and, below each section, offered alternate code that will run faster:
CODE
'Remove unwanted columns

' Old Code Commented Out
' Range("N1:Q" & LastRow).Select
' Selection.Delete
Range("N:Q").Delete

' Range("C:C").Select
' Selection.Delete
Range("C:C").Delete

' Range("A1:B" & LastRow).Select
' Selection.Cut
Range("A:B").Cut

' Range("K:K").Select
' Selection.Insert Shift:=xlToRight
Range("K:K").Insert Shift:=xlToRight
Macro Recorder always spits out the
Range("C:C").Select
Selection.Delete
type of layout. I think it's worth the time to clean all the mess up as shown in my example.

_____
-John
The plural of anecdote is not data

This takes time and space in your code.

Also another note. Is if you use the above code and delete "build sheet" and then copy and rename "preprocess doc" you will have to have your formatting of "build sheet" below the delete and rename code.

I think you should look at following John's advice about select 1st. And leave this other code alone

ck1999

 

[mattloflin]

Oh believe me I did, I went through and did a lot of that.

The Build Sheet is an external document loaded at the beginning of the code.

basically saying
if LABEL = LABEL in Build Sheet then Part = Build Sheet Part and Numnber = Buildsheet Number.

So we can't delete the build sheet and we can't directly copy the build sheet.

 

[ck1999]

Well the lastest code still needs alot

I noticed you have alot of loops like

For i = 1 To LastRow - 1
    Range("E" & i + 1).Select
    ActiveCell.Formula = "=IF(ISNA(VLOOKUP(B" & i + 1 & ",'" & ConnectorDB & "'!$B:$Z,1,FALSE)),""NIS"",VLOOKUP(B" & i + 1 & ",'" & ConnectorDB & "'!$B:$Z,2,FALSE))"
    Range("F" & i + 1).Select
    ActiveCell.Formula = "=IF(ISNA(VLOOKUP(B" & i + 1 & ",'" & ConnectorDB & "'!$B:$Z,1,FALSE)),""NIS"",VLOOKUP(B" & i + 1 & ",'" & ConnectorDB & "'!$B:$Z,3,FALSE))"
    Range("G" & i + 1).Select
    ActiveCell.Formula = "=IF(ISNA(VLOOKUP(B" & i + 1 & ",'" & ConnectorDB & "'!$B:$Z,1,FALSE)),""NIS"",VLOOKUP(B" & i + 1 & ",'" & ConnectorDB & "'!$B:$Z,4,FALSE))"
    Range("H" & i + 1).Select
    ActiveCell.Formula = "=IF(ISNA(VLOOKUP(B" & i + 1 & ",'" & ConnectorDB & "'!$B:$Z,1,FALSE)),""NIS"",VLOOKUP(B" & i + 1 & ",'" & ConnectorDB & "'!$B:$Z,5,FALSE))"
'etc              
    Next i

INstead of looping use


Range("H2","H" & newlastrow).formula = "=IF(ISNA(VLOOKUP(B" & 2 & ",'" & ConnectorDB & "'!$B:$Z,1,FALSE)),""NIS"",VLOOKUP(B" & 2 & ",'" & ConnectorDB & "'!$B:$Z,5,FALSE))"


That might speed it up some. But go back and take the time with selection .

ck1999