VB Script for copying

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:

1. Constants: Define the paths for Excel1 and Excel2.
2. Excel Application: Create an instance of Excel using CreateObject("Excel.Application").
3. Open Workbooks: Open both Excel files.
4. Get Worksheets: Reference Sheet1 of Excel1 and Sheet2 of Excel2.
5. Get Range: Get the range of non-empty cells in Column 1 of Sheet1.
6. Copy Values: Loop through each cell in the range and copy its value to the corresponding cell in Column 2 of Sheet2.
7. Save and Close: Save changes to Excel2 and close both workbooks.
8. 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.