Hi Dave,
Did all the suggestions but no changes.

"Dave Peterson" wrote:

The columnwidths stuff was germane to the other post--I should have taken that
out before I pasted.

Did you try the other suggestions.

MikeZz wrote:

Hi Dave,
I don't do any column width changing. Just paste values from my workbook to
the customer workbook. There aren't a massive amount of formulas so I don't
think it's the calculation udpate that's causing the problem. In addition,
each time I run the macro, it's just re-copying what the previous macro had
done so it's not doing anything different.

Thanks for the help.

"Dave Peterson" wrote:

(Saved from a previous post)

I would think that it would depend a lot on what your macro does--if it
inserts/deletes rows or columns, then...

Do you see the dotted lines that you get after you do a print or print preview?

If you do
Tools|Options|view tab|uncheck display page breaks

does the run time go back to normal?

Since you're changing columnwidths, excel could be figuring out where those
dotted lines go.

You may want to do something like:

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

ActiveSheet.DisplayPageBreaks = False

'do the work

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode

End Sub

Being in View|PageBreak Preview mode can slow macros down, too.

MikeZz wrote:

I have a timer in a VBA sub that copies data from one workbook to a series of
customer forms.

I've noticed that each consecutive time I re-run the same exact macro with
the same data, it takes a couple seconds longer. If I completely close
excel, it seems run faster, then get slower again.

Looking at windows task manager, my memory usage isn't going up so I'm
wondering what I'm doing wrong.

1st run: 6 sec
2nd run: 7 sec
3rd run: 8 sec
4th run: 10 sec
5th run: 12 sec
6th run: 13 sec
7th run: 14 sec

This is the first time I've done the following and I don't have problems
with any other macros taking longer:
1. Use Application.EnableEvents = False
2. Reference named ranges more instead of load data into array first.

Thanks, MikeZz

--

Dave Peterson


--

Dave Peterson

Maybe you could add a few strategically placed:
Debug.print "Step ###: " & now
into your code.

Then you may find out what area is taking the most time and see if you can
improve that area?????

MikeZz wrote:

Hi Dave,
Did all the suggestions but no changes.

"Dave Peterson" wrote:

The columnwidths stuff was germane to the other post--I should have taken that
out before I pasted.

Did you try the other suggestions.

MikeZz wrote:

Hi Dave,
I don't do any column width changing. Just paste values from my workbook to
the customer workbook. There aren't a massive amount of formulas so I don't
think it's the calculation udpate that's causing the problem. In addition,
each time I run the macro, it's just re-copying what the previous macro had
done so it's not doing anything different.

Thanks for the help.

"Dave Peterson" wrote:

(Saved from a previous post)

I would think that it would depend a lot on what your macro does--if it
inserts/deletes rows or columns, then...

Do you see the dotted lines that you get after you do a print or print preview?

If you do
Tools|Options|view tab|uncheck display page breaks

does the run time go back to normal?

Since you're changing columnwidths, excel could be figuring out where those
dotted lines go.

You may want to do something like:

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

ActiveSheet.DisplayPageBreaks = False

'do the work

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode

End Sub

Being in View|PageBreak Preview mode can slow macros down, too.

MikeZz wrote:

I have a timer in a VBA sub that copies data from one workbook to a series of
customer forms.

I've noticed that each consecutive time I re-run the same exact macro with
the same data, it takes a couple seconds longer. If I completely close
excel, it seems run faster, then get slower again.

Looking at windows task manager, my memory usage isn't going up so I'm
wondering what I'm doing wrong.

1st run: 6 sec
2nd run: 7 sec
3rd run: 8 sec
4th run: 10 sec
5th run: 12 sec
6th run: 13 sec
7th run: 14 sec

This is the first time I've done the following and I don't have problems
with any other macros taking longer:
1. Use Application.EnableEvents = False
2. Reference named ranges more instead of load data into array first.

Thanks, MikeZz

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

That's a good idea.
Just curious though, I haven't used the .print since college maybe 15 years
ago. Where does it print to? ie... where does the log data go?

Thanks,

"Dave Peterson" wrote:

Maybe you could add a few strategically placed:
Debug.print "Step ###: " & now
into your code.

Then you may find out what area is taking the most time and see if you can
improve that area?????

MikeZz wrote:

Hi Dave,
Did all the suggestions but no changes.

"Dave Peterson" wrote:

The columnwidths stuff was germane to the other post--I should have taken that
out before I pasted.

Did you try the other suggestions.

MikeZz wrote:

Hi Dave,
I don't do any column width changing. Just paste values from my workbook to
the customer workbook. There aren't a massive amount of formulas so I don't
think it's the calculation udpate that's causing the problem. In addition,
each time I run the macro, it's just re-copying what the previous macro had
done so it's not doing anything different.

Thanks for the help.

"Dave Peterson" wrote:

(Saved from a previous post)

I would think that it would depend a lot on what your macro does--if it
inserts/deletes rows or columns, then...

Do you see the dotted lines that you get after you do a print or print preview?

If you do
Tools|Options|view tab|uncheck display page breaks

does the run time go back to normal?

Since you're changing columnwidths, excel could be figuring out where those
dotted lines go.

You may want to do something like:

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

ActiveSheet.DisplayPageBreaks = False

'do the work

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode

End Sub

Being in View|PageBreak Preview mode can slow macros down, too.

MikeZz wrote:

I have a timer in a VBA sub that copies data from one workbook to a series of
customer forms.

I've noticed that each consecutive time I re-run the same exact macro with
the same data, it takes a couple seconds longer. If I completely close
excel, it seems run faster, then get slower again.

Looking at windows task manager, my memory usage isn't going up so I'm
wondering what I'm doing wrong.

1st run: 6 sec
2nd run: 7 sec
3rd run: 8 sec
4th run: 10 sec
5th run: 12 sec
6th run: 13 sec
7th run: 14 sec

This is the first time I've done the following and I don't have problems
with any other macros taking longer:
1. Use Application.EnableEvents = False
2. Reference named ranges more instead of load data into array first.

Thanks, MikeZz

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

It prints to the VBE's immediate window.

You can see it by hitting ctrl-g or View|immediate window (when you're in the
VBE).

MikeZz wrote:

That's a good idea.
Just curious though, I haven't used the .print since college maybe 15 years
ago. Where does it print to? ie... where does the log data go?

Thanks,

"Dave Peterson" wrote:

Maybe you could add a few strategically placed:
Debug.print "Step ###: " & now
into your code.

Then you may find out what area is taking the most time and see if you can
improve that area?????

MikeZz wrote:

Hi Dave,
Did all the suggestions but no changes.

"Dave Peterson" wrote:

The columnwidths stuff was germane to the other post--I should have taken that
out before I pasted.

Did you try the other suggestions.

MikeZz wrote:

Hi Dave,
I don't do any column width changing. Just paste values from my workbook to
the customer workbook. There aren't a massive amount of formulas so I don't
think it's the calculation udpate that's causing the problem. In addition,
each time I run the macro, it's just re-copying what the previous macro had
done so it's not doing anything different.

Thanks for the help.

"Dave Peterson" wrote:

(Saved from a previous post)

I would think that it would depend a lot on what your macro does--if it
inserts/deletes rows or columns, then...

Do you see the dotted lines that you get after you do a print or print preview?

If you do
Tools|Options|view tab|uncheck display page breaks

does the run time go back to normal?

Since you're changing columnwidths, excel could be figuring out where those
dotted lines go.

You may want to do something like:

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

ActiveSheet.DisplayPageBreaks = False

'do the work

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode

End Sub

Being in View|PageBreak Preview mode can slow macros down, too.

MikeZz wrote:

I have a timer in a VBA sub that copies data from one workbook to a series of
customer forms.

I've noticed that each consecutive time I re-run the same exact macro with
the same data, it takes a couple seconds longer. If I completely close
excel, it seems run faster, then get slower again.

Looking at windows task manager, my memory usage isn't going up so I'm
wondering what I'm doing wrong.

1st run: 6 sec
2nd run: 7 sec
3rd run: 8 sec
4th run: 10 sec
5th run: 12 sec
6th run: 13 sec
7th run: 14 sec

This is the first time I've done the following and I don't have problems
with any other macros taking longer:
1. Use Application.EnableEvents = False
2. Reference named ranges more instead of load data into array first.

Thanks, MikeZz

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson