Clear destination, then copy betwen workbooks
 

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.

Clear destination, then copy betwen workbooks
 

Russell already stated that the code runs from the
Control workbook, so your code needs to be placed in a
module in that book.
This makes sense too, since its that workbook from which
he does the trial balance. I expect the Data workbook
might be a temporary book or one that changes daily - one
would not want code therein for obvious reasons.

I'd suggest one minor change to your code. There's a risk
that since the Data workbook isn't specified that Control
might be active when the code is run...


Sub TestMe()

Workbooks("Control.xls").Worksheets
("TrialBal").Cells.ClearContents
Range("A1").CurrentRegion.Copy _
Workbooks("Control.xls").Worksheets("TrialBal").R ange
("A1")
Application.CutCopyMode = False
End Sub




-----Original Message-----
russel,

Try this out:

Sub TestMe()
Workbooks("Control.xls").Worksheets
("TrialBal").Cells.ClearContents
Range("A1").CurrentRegion.Copy _
Workbooks("Control.xls").Worksheets("TrialBal").R ange
("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.

.

Clear destination, then copy betwen workbooks
 

Sub TestMe()
dim wsData as WorkSheet, wsControl as WorkSheet
SET wsData = Workbooks("Data").Sheets(1)
SET wsControl = _
Workbooks("Control.xls").Worksheets("TrialBal")
wsControl.Cells.ClearContents
wsData.Range("A1").CurrentRegion.Copy _
wsControl.Range("A1")
Application.CutCopyMode = False
End Sub





-----Original Message-----
russel,

Try this out:

Sub TestMe()
Workbooks("Control.xls").Worksheets
("TrialBal").Cells.ClearContents
Range("A1").CurrentRegion.Copy _
Workbooks("Control.xls").Worksheets("TrialBal").R ange
("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.

.

Clear destination, then copy betwen workbooks
 

Patrick,

Russell already stated that the code runs from the
Control workbook
You're absolutely right. Somehow I missed that.

I'd suggest one minor change to your code
Saw the suggestion in your second post.
Makes perfect sense and insures that the macro
starts out from the right place.

Thanks,
John



Patrick Molloy wrote:

Russell already stated that the code runs from the
Control workbook, so your code needs to be placed in a
module in that book.
This makes sense too, since its that workbook from which
he does the trial balance. I expect the Data workbook
might be a temporary book or one that changes daily - one
would not want code therein for obvious reasons.

I'd suggest one minor change to your code. There's a risk
that since the Data workbook isn't specified that Control
might be active when the code is run...

Sub TestMe()

Workbooks("Control.xls").Worksheets
("TrialBal").Cells.ClearContents
Range("A1").CurrentRegion.Copy _
Workbooks("Control.xls").Worksheets("TrialBal").R ange
("A1")
Application.CutCopyMode = False
End Sub

-----Original Message-----
russel,

Try this out:

Sub TestMe()
Workbooks("Control.xls").Worksheets
("TrialBal").Cells.ClearContents
Range("A1").CurrentRegion.Copy _
Workbooks("Control.xls").Worksheets("TrialBal").R ange
("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.

.