There are more than one way to command Excel to obey. A recent problem was to automate some basic cut-n-paste sequences between Excel files and run some VBA-snippets. After a few miserable hours, I found a sure way to move data with precision between a source and destination.
Seems Excel cares about the active application/window, when moving data around. Transporting data within a file is simpler than between files. Transfers across open Excel files can cause weird system errors. Also, share global variables between functions to activate the right window.
The code to use for copying between 2 workbooks (macro code is in destination file):
Windows(wkbSource.Name).Activate
wkbSource.Sheets("For Front Page").Select
Set shttocopy = wkbSource.ActiveSheet
' Table 1
range_from = "A1:E3"
range_dest = "A1:E3"
wkbDest.Worksheets("Front Page Data").Range(range_dest).Value = shttocopy.Range(range_from).Value
Maybe this can be simplified even more?
Dan Gustafsson 