VB Script for copying

Question

I need a VB Script to copy values from Excel1 - sheet1 - column 1 to Excel2 - sheet2 - column 2

Uday Dodiya · Answer

Hello Nidhi Kowalli To create a VBScript that copies values from Excel1 (Sheet1 Column 1) to Excel2 (Sheet2 Column 2) you can follow the steps below. This script uses the Excel.Application object to automate Excel operations. Here is a sample VBScript: Option Explicit ' Define constants for the Excel file paths Const Excel1Path = C:\Path\To\Excel1.xlsx Const Excel2Path = C:\Path\To\Excel2.xlsx ' Create Excel application object Dim objExcel Set objExcel = CreateObject(Excel.Application) ' Open Excel1 and Excel2 Dim wb1 wb2 Set wb1 = objExcel.Workbooks.Open(Excel1Path) Set wb2 = objExcel.Workbooks.Open(Excel2Path) ' Get the relevant worksheets Dim ws1 ws2 Set ws1 = wb1.Sheets(Sheet1) Set ws2 = wb2.Sheets(Sheet2) ' Get the range in Column 1 of Sheet1 Dim range1 Set range1 = ws1.Columns(1).SpecialCells(2) ' 2 stands for xlCellTypeConstants which means non-empty cells ' Copy values from Column 1 of Sheet1 to Column 2 of Sheet2 Dim cell targetCell For Each cell In range1 Set targetCell = ws2.Cells(cell.Row 2) targetCell.Value = cell.Value Next ' Save and close the workbooks wb2.Save wb1.Close False wb2.Close False ' Quit Excel application objExcel.Quit ' Clean up Set range1 = Nothing Set ws1 = Nothing Set ws2 = Nothing Set wb1 = Nothing Set wb2 = Nothing Set objExcel = Nothing WScript.Echo Values copied successfully from Excel1 - Sheet1 - Column 1 to Excel2 - Sheet2 - Column 2. Explanation: Constants : Define the paths for Excel1 and Excel2 . Excel Application : Create an instance of Excel using CreateObject(Excel.Application) . Open Workbooks : Open both Excel files. Get Worksheets : Reference Sheet1 of Excel1 and Sheet2 of Excel2 . Get Range : Get the range of non-empty cells in Column 1 of Sheet1 . Copy Values : Loop through each cell in the range and copy its value to the corresponding cell in Column 2 of Sheet2 . Save and Close : Save changes to Excel2 and close both workbooks. Clean Up : Release all objects and quit the Excel application. Make sure to replace C:\Path\To\Excel1.xlsx and C:\Path\To\Excel2.xlsx with the actual file paths of your Excel files. Save this script with a .vbs extension and run it. Ensure that Excel is installed on your system and the paths to the files are correct.