Retain view on one worksheet 1

[burtech]

Hi. I've had a good look in the forums for a similar thread, but I couldn't find one so I'm sorry if I'm repeating this.

I'm producing a export spreadsheet that loads in other spreadsheet files and performs simple copy/paste routines from those into the export spreadsheet before performing a saveas into CSV. Each spreadsheet has a weeks worth of data spread over 7 workbooks. There's an awful lot of screen flickering while the export functions activate each workbook, copy and paste. Is there any way to retain focus on the workbook in the export spreadsheet that I start on initially, so that the process appears seamless to the user? If it helps, here is my import code:

Sub Import()
'
' Import Macro
'

Dim dyArray(7) As String
Dim i, j As Integer
Dim sheetname As String

sheetname = ActiveWorkbook.Name

dyArray(1) = "Sun"
dyArray(2) = "Mon"
dyArray(3) = "Tue"
dyArray(4) = "Wed"
dyArray(5) = "Thu"
dyArray(6) = "Fri"
dyArray(7) = "Sat"

For i = 1 To 7
Windows("A1Performance.xls&quot.Activate
Sheets(dyArray(i)).Select
Range("A1:AE124&quot.Select
Application.CutCopyMode = False
Selection.Copy
Windows(sheetname).Activate
Sheets(dyArray(i)).Select
Range("A1&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
For j = 1 To 124
Range("AF" & j).Value = dyArray(i)
Range("AG" & j).Value = "X"
Next j
Range("A1&quot.Select
Next i

Sheets("Tools&quot.Select

End Sub

The code is currently hardcoded to one input spreadsheet (A1Performance.xls) but I plan to make it read a folder to pick up the filenames.

Thanks in advance,

Steve

 

[xlbo]

just use
application.screenupdating = false

as the 1st line of code

Should be set back to false at the end of the sub but it isn't absolutely necessary - good practice though as there are other application properties that DO need to be reset when code has run

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[burtech]

Worked a treat!

Many thanks,

Steve