Opening a Workbook, Clearing Contents, Adding New Content, Saving&Closing

Setting full address of file:

Dim str, filePath As String
str = ThisWorkbook.Path & "\"
filePath = str & "destination" & ".xlsx"

Opening workbook:

Dim wb As Workbook

Set wb = Workbooks.Open(filePath)

Clearing all contents,formats and comments for a sheet, say Sheet1:

wb.Worksheets("Sheet1").Cells.ClearContents

wb.Worksheets("Sheet1").Cells.ClearFormats

wb.Worksheets("Sheet1").Cells.ClearComments

Putting some data:

     With wb.Worksheets("Sheet1")

        Dim i As Integer

        For i = 1 To 100

            .Range("A" & i).Value = i

        Next i

     End With       

Save and Close:

        .Save

        .Close

     

Working with Multiple Open Workbooks

This post is only about scenario when workbooks, to work with, are open. If any workbook remains close then "Subscript out of range" error will be thrown.

Consider two workbooks:

destination.xlsm : where data will be pasted. Here data is values in two cells - for simplicity.
source.xlsm: cell values will be copied from sheet1 of this workbook.

Code:

Declaration and setting of two workbook variables:

Dim wbRead As Workbook
Set wbRead = Workbooks("source.xlsx")
Dim wbWrite As Workbook
Set wbWrite = Workbooks("destination.xlsx")


Copy paste values from cells A1 and B1 of source to A1 and A2 of destination.

wbWrite.Worksheets(1).Range("A1") = wbRead.Worksheets(1).Range("A1")
wbWrite.Worksheets(1).Range("A2") = wbRead.Worksheets(1).Range("B1")

Save and Close destination workbook:

wbWrite.Save

wbWrite.Close