How Can I Copy Column C of One Worksheet to Column A of a Second Worksheet?

ScriptingGuy1

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

— JP

Hey, JP. Well, that’s 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.

Oh, right: you wanted to know how to do this using a script. (What were we thinking?) But that’s OK; it’s just as easy to do this using a script as it is to do it manually:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)

Set objWorksheet = objWorkbook.Worksheets(2)
objWorksheet.Activate
Set objRange = objWorkSheet.Range(“C1”).EntireColumn
objRange.Copy
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate
Set objRange = objWorkSheet.Range(“A1”)
objWorksheet.Paste(objRange)

The script starts out by creating an instance of the Excel.Application object and then setting the Visible property to True; that gives us an instance of Excel that we can see on screen. We then use the Open method to open the file C:\Scripts\Test.xls. For this sample script, we’re 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.

To do that we first create an object reference to Sheet 2 (the second worksheet in the Worksheets collection), and then call the Activate method to make this the active worksheet. That’s what these two lines of code are for:

Set objWorksheet = objWorkbook.Worksheets(2)
objWorksheet.Activate

To select column C, we create an instance of the Range object, specifying cell C1 as the starting point, then using the EntireColumn property to select the rest of column C. That’s what we do here:

Set objRange = objWorkSheet.Range(“C1”).EntireColumn

After we’ve selected the column, we then call the Copy method to copy the data to the Clipboard.

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:

Set objRange = objWorkSheet.Range(“A1”)

Because we’re pasting data there’s no need to select the entire column; all we have to do is pick the starting cell and then call the Paste method, passing this method the object reference that indicates where we want the data pasted:

objWorksheet.Paste(objRange)

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.

ScriptingGuy1

Follow