russel,

Try this out:

Sub TestMe()
Workbooks("Control.xls").Worksheets("TrialBal").Ce lls.ClearContents
Range("A1").CurrentRegion.Copy _
Workbooks("Control.xls").Worksheets("TrialBal").Ra nge("A1")
Application.CutCopyMode = False
End Sub

The above is placed in a module in the "Data" workbook.
There are no Select statements, so you'll never see it switch
to the "Control" workbook.
It'll leave the "Data" workbook active all the time.
The above also assumed that both workbooks are opened.

John

John

"russell (skmr3)" wrote:

Hi All,

I have two workbooks, Control and Data.
The Control workbook contains the macro, while Data
contains data to be
formatted and copied over to the Control workbook.
Control workbook has a sheet called TrialBal. This sheets
already contains data.
The macro needs to format the data in the Data workbook,
clear the contents
that’s already in the TrialBal sheet, and copy the data
across from the Data
workbook.

I’ve tried different combinations of the above actions,
but most result in long and poor coding.
If anyone has a suggestion on how to proceed above, that
would be greatly appreciated.

Some coding I have: (it errors out on the
ActiveSheet.Paste, as there is nothing to paste.)

‘Select the data range
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

‘Clear destination, and paste.
Workbooks(Control).Activate
Sheets("TrialBal").Select
Cells.Select
Selection.ClearContents
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Cheers.