Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste selected columns between Excel workbooks
Hi,
I am trying to copy a selected number of columns from one workbook and paste it into another worksheet in a second workbook. I keep on getting an error on ActiveSheet.Paste.(ie. TargetWS.Paste below) How can i get round this or better still could a vb champion show me the correct way to do this. Thanks. kolu Set SrcWkb = Workbooks("source.xls") Application.Windows("source.xls").Activate Columns("G:I").Select Application.CutCopyMode = False Selection.Copy Application.Windows("Target.xls").Activate Columns("G:I").Select With TargetWkb.Worksheets("sheet") TargetWS.Paste End With The error message i get is : object invoked has disconnected from its cilents when i execute TargetWS.Paste |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste selected columns between Excel workbooks
On Apr 4, 4:13*pm, Orimslala wrote:
Hi, I am trying to copy a selected number of columns from one workbook and paste it into another worksheet in a second workbook. I keep on getting an error on ActiveSheet.Paste.(ie. TargetWS.Paste below) How can i get round this or better still could a vb champion show me the correct way to do this. Thanks. kolu * * Set SrcWkb = Workbooks("source.xls") * * Application.Windows("source.xls").Activate * * Columns("G:I").Select * * Application.CutCopyMode = False * * Selection.Copy * * Application.Windows("Target.xls").Activate * * Columns("G:I").Select * * With TargetWkb.Worksheets("sheet") * * * * TargetWS.Paste * * End With The error message i get is : object invoked has disconnected from its cilents when i execute *TargetWS.Paste Hi Orimslala, If both workbooks are open, then the following should be all that you need: Workbooks("Target").Sheets("sheet").Columns("G:I") = _ Workbooks("source").Sheets("...").Columns("G:I").V alue Make sure to replace the "..." in the second bit with your sheet name. Alternatively, you could get away with using ActiveSheet if you will always have the source data sheet active when you run it. If you need something that will work with the books closed, let us know. Cheersa, Ivan. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste selected columns between Excel workbooks
On Apr 4, 7:18*pm, Ivyleaf wrote:
On Apr 4, 4:13*pm, Orimslala wrote: Hi, I am trying to copy a selected number of columns from one workbook and paste it into another worksheet in a second workbook. I keep on getting an error on ActiveSheet.Paste.(ie. TargetWS.Paste below) How can i get round this or better still could a vb champion show me the correct way to do this. Thanks. kolu * * Set SrcWkb = Workbooks("source.xls") * * Application.Windows("source.xls").Activate * * Columns("G:I").Select * * Application.CutCopyMode = False * * Selection.Copy * * Application.Windows("Target.xls").Activate * * Columns("G:I").Select * * With TargetWkb.Worksheets("sheet") * * * * TargetWS.Paste * * End With The error message i get is : object invoked has disconnected from its cilents when i execute *TargetWS.Paste Hi Orimslala, If both workbooks are open, then the following should be all that you need: * * Workbooks("Target").Sheets("sheet").Columns("G:I") = _ * * * Workbooks("source").Sheets("...").Columns("G:I").V alue Make sure to replace the "..." in the second bit with your sheet name. Alternatively, you could get away with using ActiveSheet if you will always have the source data sheet active when you run it. If you need something that will work with the books closed, let us know. Cheersa, Ivan.- Hide quoted text - - Show quoted text - Just in reference to your original posted code, if you really want to use the paste method (I usually try to avoid running data throught the Clipboard), then an optimised version of your code would look something like this: Sub CopyCols() Dim SrcWkb As Workbook, TargetWkb As Workbook Set SrcWkb = Workbooks("source.xls") Set TargetWkb = Workbooks("Target.xls") SrcWkb.ActiveSheet.Columns("G:I").Copy _ Destination:=TargetWkb.Sheets("sheet").Columns("G: I") End Sub The only advantage in using the clipboard is the it will also copy more formatting than any other single method I know. Cheers, Ivan. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste selected columns between Excel workbooks
On 4 Apr, 09:31, Ivyleaf wrote:
On Apr 4, 7:18*pm, Ivyleaf wrote: On Apr 4, 4:13*pm, Orimslala wrote: Hi, I am trying to copy a selected number of columns from one workbook and paste it into another worksheet in a second workbook. I keep on getting an error on ActiveSheet.Paste.(ie. TargetWS.Paste below) How can i get round this or better still could a vb champion show me the correct way to do this. Thanks. kolu * * Set SrcWkb = Workbooks("source.xls") * * Application.Windows("source.xls").Activate * * Columns("G:I").Select * * Application.CutCopyMode = False * * Selection.Copy * * Application.Windows("Target.xls").Activate * * Columns("G:I").Select * * With TargetWkb.Worksheets("sheet") * * * * TargetWS.Paste * * End With The error message i get is : object invoked has disconnected from its cilents when i execute *TargetWS.Paste Hi Orimslala, If both workbooks are open, then the following should be all that you need: * * Workbooks("Target").Sheets("sheet").Columns("G:I") = _ * * * Workbooks("source").Sheets("...").Columns("G:I").V alue Make sure to replace the "..." in the second bit with your sheet name. Alternatively, you could get away with using ActiveSheet if you will always have the source data sheet active when you run it. If you need something that will work with the books closed, let us know. Cheersa, Ivan.- Hide quoted text - - Show quoted text - Just in reference to your original posted code, if you really want to use the paste method (I usually try to avoid running data throught the Clipboard), then an optimised version of your code would look something like this: Sub CopyCols() * * Dim SrcWkb As Workbook, TargetWkb As Workbook * * Set SrcWkb = Workbooks("source.xls") * * Set TargetWkb = Workbooks("Target.xls") * * SrcWkb.ActiveSheet.Columns("G:I").Copy _ * * * * Destination:=TargetWkb.Sheets("sheet").Columns("G: I") End Sub The only advantage in using the clipboard is the it will also copy more formatting than any other single method I know. Cheers, Ivan.- Hide quoted text - - Show quoted text - Hi Ivan, Thanks for responding... Workbooks("Target").Sheets("sheet").Columns("G:I") = _ Workbooks("source").Sheets("...").Columns("G:I").V alue The above solves 80% of my problem, the remaining 20% is that it does not paste the colour of cells from the source workbook. Your other alternative CopyCols() gives me the same error as my original post Thanks for your feedback Orimslala |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste selected columns between Excel workbooks
On Apr 4, 7:41*pm, Orimslala wrote:
On 4 Apr, 09:31, Ivyleaf wrote: On Apr 4, 7:18*pm, Ivyleaf wrote: On Apr 4, 4:13*pm, Orimslala wrote: Hi, I am trying to copy a selected number of columns from one workbook and paste it into another worksheet in a second workbook. I keep on getting an error on ActiveSheet.Paste.(ie. TargetWS.Paste below) How can i get round this or better still could a vb champion show me the correct way to do this. Thanks. kolu * * Set SrcWkb = Workbooks("source.xls") * * Application.Windows("source.xls").Activate * * Columns("G:I").Select * * Application.CutCopyMode = False * * Selection.Copy * * Application.Windows("Target.xls").Activate * * Columns("G:I").Select * * With TargetWkb.Worksheets("sheet") * * * * TargetWS.Paste * * End With The error message i get is : object invoked has disconnected from its cilents when i execute *TargetWS.Paste Hi Orimslala, If both workbooks are open, then the following should be all that you need: * * Workbooks("Target").Sheets("sheet").Columns("G:I") = _ * * * Workbooks("source").Sheets("...").Columns("G:I").V alue Make sure to replace the "..." in the second bit with your sheet name. Alternatively, you could get away with using ActiveSheet if you will always have the source data sheet active when you run it. If you need something that will work with the books closed, let us know. Cheersa, Ivan.- Hide quoted text - - Show quoted text - Just in reference to your original posted code, if you really want to use the paste method (I usually try to avoid running data throught the Clipboard), then an optimised version of your code would look something like this: Sub CopyCols() * * Dim SrcWkb As Workbook, TargetWkb As Workbook * * Set SrcWkb = Workbooks("source.xls") * * Set TargetWkb = Workbooks("Target.xls") * * SrcWkb.ActiveSheet.Columns("G:I").Copy _ * * * * Destination:=TargetWkb.Sheets("sheet").Columns("G: I") End Sub The only advantage in using the clipboard is the it will also copy more formatting than any other single method I know. Cheers, Ivan.- Hide quoted text - - Show quoted text - Hi Ivan, Thanks for responding... Workbooks("Target").Sheets("sheet").Columns("G:I") = _ Workbooks("source").Sheets("...").Columns("G:I").V alue The above solves 80% of my problem, the remaining 20% is that it does not paste the colour of cells from the source workbook. Your other alternative CopyCols() gives me the same error as my original post Thanks for your feedback Orimslala- Hide quoted text - - Show quoted text - Hmmm... not sure why the second one is failing for you... I did test it before posting and it worked fine for me. It might be the 'ActiveSheet' part in the copy line. You could try substituting for something like the following (change for your sheet name): SrcWkb.Sheets("Some Sheet").Columns("G:I").Copy _ Destination:=TargetWkb.Sheets("sheet").Columns("G: I") Other than that, the only thing I can suggest is triple checking all your book names etc. Oh, and I haven't tested this code with either of the books closed, so I am assuming they are both open when you are running it. Cheers, Ivan. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Copy/ move selected data from workbooks to seperate worksheets or workbooks | Excel Worksheet Functions | |||
How I can copy / paste a selected cells to other columns in Excel | Excel Discussion (Misc queries) | |||
How do I copy selected cells in excel and paste into word table | Excel Programming | |||
How to Copy & Paste selected Range in Excel (MFC) | Excel Programming |