Copy/Paste Between Workbooks faster..?

[Lukums]

Hey guys,

Need some desperate help with this one the code works fine but it's just sooooo **** slow... "probably my fault"

Any advise? Needs to be a hell of a lot quicker for what I'm trying to achieve.

<sub>Sub ImportBlueDownpipe()


LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row


For i = 1 To LastRow


Sheets("Downpipe").Select


If Range("H" & i).Value = "MB" And Range("W" & i).Value = "Downpipe" Then
Rows(i).Select
Selection.Copy


Workbooks.Open Filename:="B:\Best Shed Scheduler.xlsm" '''''Found on local machine drive'''''nothing networked''''


Dim p As Integer, q As Integer


p = Worksheets.Count


For q = 1 To p


Next q


Sheets("Downpipe Machine").Select


erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False




End If

Next i


End Sub</sub>

 

[Michael M]

It's the weekend.....so have been at golf today...

It destroyed my macro buttons which I have in row 1 shouldn't have done that.....make row 1 bigger and see if they are hidden...autofilter won't remove them if they are in row 1

It also compacted all rows between H:BJ ( hidden all those rows ) after macro ran. do you mean columns ???....the code allows for "H" to "Y" to be hidden nothing else

 

[Lukums]

Hello Michael,

It's all good mate I turn my brain off at 5PM on a Friday also.

I've stepped through the code and now understand much more thank you again.

You are making all columns visable to obtain the other information which was copied across previously, this is working perfectly mind you.

When turning the H:Y True again at the least bit of the code it ends up hiding G:BJ columns it's super weird. So A,B,C,D,E,G is all good then it jumps to BJ as all columsn are hidden between that.

Apart from this its looking good.

Luke

 

[Lukums]

Hello Michael!

Please IGNORE! fixed it!

So at the very end I added these 2 lines:

Sheets("Downpipe").Range("I:U").Entire Column.Hidden = True
Sheets("Downpipe").Range("V:Y").Entire Column.Hidden = False

And everything goes back to normal!

Whooo hoo!!! thanks for all your help!

You're a gem!

 

[Michael M]

Is there any other code in the workbook....a worksheet_change event perhaps ???

 

[Michael M]

Ok, glad that solved the issue.....but I'm concerned that you should have to do that !!
However, the initial question was to make the workbook code faster......was it faster ???

 

[Lukums]

Hello Michael,

It's effectively increased this speed by 5 minutes easily (especially with a long list of data) the process takes 10-20 seconds to complete as a whole TOPS.

You've been amazing every step of the way, thank you so much Michael.

 

[Michael M]

Glad it helped....