| 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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . |
| Reply |
| 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) | |||
Linear Mode
