Why don't you consider not selecting anything to copy... just copy what you
want directly to where you want it without using the Select method at all?
Here is an example to show you how it works. I'll assume you have 3
worksheets in your workbook. Go to Sheet2 and put some data in the range
B6:F15. Next, copy/paste this macro into a Module (Insert/Module from VB's
menu bar)...
Sub TestCopyDataDirect()
Worksheets("Sheet2").Range("B6:F15").Copy Worksheets("Sheet3").Range("G8")
End Sub
Now, go to Sheet1 (make A1 the active cell), press Alt+F8, select the
TestCopyDataDirect macro and Run it. Notice you didn't leave Sheet1 and your
cursor is still on A1. If you go to Sheet3, you will see the data from
B6:F15 on Sheet2 has been copied to G8:K17 on Sheet3. No selections were
made, no worksheets were activated and the active cell remained the active
cell.
Perhaps this previous posting of mine (a response to another person using
Select/Selection type constructions) will be of some help to you in your
future programming...
Whenever you see code constructed like this...
Range("A1").Select
Selection.<whatever>
you can almost always do this instead...
Range("A1").<whatever>
In your particular case, you have this...
Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows
which, using the above concept, can be reduced to this...
For Each r In Range("C2:C8193").Rows
Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.
