{"id":68923,"date":"2005-09-20T15:29:00","date_gmt":"2005-09-20T15:29:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2005\/09\/20\/how-can-i-copy-column-c-of-one-worksheet-to-column-a-of-a-second-worksheet\/"},"modified":"2005-09-20T15:29:00","modified_gmt":"2005-09-20T15:29:00","slug":"how-can-i-copy-column-c-of-one-worksheet-to-column-a-of-a-second-worksheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-copy-column-c-of-one-worksheet-to-column-a-of-a-second-worksheet\/","title":{"rendered":"How Can I Copy Column C of One Worksheet to Column A of a Second Worksheet?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I copy column C of one worksheet to column A of a second worksheet?

— JP<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, JP. Well, that\u2019s easy: you just open Excel, highlight column C, press Ctrl+C, put the cursor in column A1 of the second worksheet, and then press Ctrl+V. There you go, and thanks for your question.<\/P>\n

Oh, right: you wanted to know how to do this using a script<\/I>. (What were<\/I> we thinking?) But that\u2019s OK; it\u2019s just as easy to do this using a script as it is to do it manually:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)<\/p>\n
Set objWorksheet = objWorkbook.Worksheets(2)\nobjWorksheet.Activate<\/p>\n
Set objRange = objWorkSheet.Range(“C1”).EntireColumn\nobjRange.Copy<\/p>\n
Set objWorksheet = objWorkbook.Worksheets(1)\nobjWorksheet.Activate<\/p>\n
Set objRange = objWorkSheet.Range(“A1”)\nobjWorksheet.Paste(objRange)\n<\/PRE>\n
The script starts out by creating an instance of the Excel.Application<\/B> object and then setting the Visible<\/B> property to True; that gives us an instance of Excel that we can see on screen. We then use the Open<\/B> method to open the file C:\\Scripts\\Test.xls. For this sample script, we\u2019re assuming that this file has at least two worksheets, and that there is data in column C of Sheet 2 that we want to copy.<\/P>\n
To do that we first create an object reference to Sheet 2 (the second worksheet in the Worksheets<\/B> collection), and then call the Activate<\/B> method to make this the active worksheet. That\u2019s what these two lines of code are for:<\/P>
Set objWorksheet = objWorkbook.Worksheets(2)\nobjWorksheet.Activate\n<\/PRE>\n
To select column C, we create an instance of the Range<\/B> object, specifying cell C1 as the starting point, then using the EntireColumn<\/B> property to select the rest of column C. That\u2019s what we do here:<\/P>
Set objRange = objWorkSheet.Range(“C1”).EntireColumn\n<\/PRE>\n
After we\u2019ve selected the column, we then call the Copy<\/B> method to copy the data to the Clipboard.<\/P>\n
Now we need to switch to Sheet 1; we do that by creating an object reference to the first worksheet in the Worksheets collection and then calling the Activate method to make Sheet 1 the current worksheet. We then use this line of code to create a Range object that points to cell A1:<\/P>
Set objRange = objWorkSheet.Range(“A1”)\n<\/PRE>\n
Because we\u2019re pasting data there\u2019s no need to select the entire column; all we have to do is pick the starting cell and then call the Paste<\/B> method, passing this method the object reference that indicates where we want the data pasted:<\/P>
objWorksheet.Paste(objRange)\n<\/PRE>\n
That should do the trick, and in fully automated fashion to boot. Like we always say (sometimes a bit prematurely): there you go, and thanks for your question. <\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I copy column C of one worksheet to column A of a second worksheet?— JP Hey, JP. Well, that\u2019s easy: you just open Excel, highlight column C, press Ctrl+C, put the cursor in column A1 of the second worksheet, and then press Ctrl+V. There you go, and thanks for your […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[710,711,48,49,3,5],"class_list":["post-68923","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-excel-application","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I copy column C of one worksheet to column A of a second worksheet?— JP Hey, JP. Well, that\u2019s easy: you just open Excel, highlight column C, press Ctrl+C, put the cursor in column A1 of the second worksheet, and then press Ctrl+V. There you go, and thanks for your […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/68923","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=68923"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/68923\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=68923"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=68923"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=68923"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}