Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search files and copy with destination on file | Excel Discussion (Misc queries) | |||
Range.Copy [Destination] should not use clipboard | Excel Discussion (Misc queries) | |||
Using copy and destination formatting | Excel Discussion (Misc queries) | |||
VBA .copy destination | Excel Discussion (Misc queries) | |||
Display 2 formulas from source workbooks to destination workbooks | Excel Discussion (Misc queries) |