Slow code when used as VBA code instead of macro (copying visible columns)

[jgrappy]

I have a macro that copys three columns Worksheet1 and pastes them
into Worksheet2. The code is run from a Worksheet3 using a command
button that hits a macro containing the following code:

Sheets("Worksheet1").Select
Columns("C:E").Select
Selection.Copy
Sheets("Worksheet2").Select
Cells.Select
ActiveSheet.Paste


That works fine, but I tried to move this code to the
Worksheet_Activate event for Worksheet3 with:

Application.Run ("Macro1")

With this it takes a long time to run and I often get an "Out of Stack
Space" error.

I've also tried just using:

Worksheets("Worksheet2").Columns("A:C").Value =
Worksheets("Worksheet1").Columns("C:E").Value

but, this copies all the cells...I need only the visible cells after a
filter has been applied. Any ideas? Thanks!

-Josh

 

[jgrappy]

I just realized that I also have the following code that returns the
focus back to Worksheet3 at the end of the code....if I take this out
it runs fine, but I need the focus to return to Worksheet3.

Worksheets ("Worksheet3").select

So, the original code was:
Sheets("Worksheet1").Select
Columns("C:E").Select
Selection.Copy
Sheets("Worksheet2").Select
Cells.Select
ActiveSheet.Paste
Worksheets ("Worksheet3").select

-Josh

 

[Dave Peterson]

I'd use:

worksheets("worksheet1").range("C:e").copy _
destination:=worksheets("sheet2").range("a1")

And it kind of sounds like you have an event that's firing after each change.
Maybe something like:

application.enableevents = false
worksheets("worksheet1").range("C:e").copy _
destination:=worksheets("sheet2").range("a1")
application.enableevents = true

to stop the event from starting.

 

[jgrappy]

Thanks, Dave that worked beautifully! Much appreciated.