Copy paste with macros

With ThisWorkbook

  'select the last row in a sheet
    r = .Worksheets(1).Range("A100").End(xlUp).Row

    'copy the rows from cell A1 to last diagnally filled cell
    .Worksheets(1).Range("A" & 1, "E" & r).Copy

  'activate the target worksheet and paste values and formats there        
    .Worksheets("Sheet2").Activate
     ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
     ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats
     
End With

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

Accessing Workbook via VBA Code

When accessing current workbook:

use ThisWorkBook method

Debug.Print ActiveWorkbook.FullName


When accessing Any open workbook:

use Workbooks("name")

Debug.Print Workbooks("test.xlsx").FullName

Please note: above example will throw script out of range error if workbook is not opened or you mention full path of workbook.

Avoid using ActiveWorkbook and Workbooks(index)