Q:

Copy columns from one sheet to another using VBA?

I want to copy the contents from one sheet to another. I'm trying to use:
        For Each r In tempWbk.Sheets("BSC").Columns
            tempWbk.Sheets("BSC").Columns(r) = instanceWbk.Sheets("BSC").Columns(r)
        Next

But it doesn't work. Any suggestion?

A:

If you're copying the contents of a sheet in one workbook to a sheet in another, you don't need to loop through the columns.
You can do something like this:
Sub CopyCells()
    Dim srcWb As Workbook
    Dim tgtWb As Workbook
    Dim srcWs As Worksheet
    Dim tgtWs As Worksheet

    Set srcWb = ThisWorkbook
    Set tgtWb = Workbooks.Open("C:\Temp\Targetbook.xlsx")
    Set srcWs = srcWb.Sheets("BSC")
    Set tgtWs = tgtWb.Sheets("BSC")

    srcWs.Cells.Copy tgtWs.Range("A1")
End Sub

If there is a specific reason you need to copy column by column, you can use this as a starting point:
Sub CopyColumns()
    Call CopyThisManyColumns(5)
End Sub
Sub CopyThisManyColumns(colCount As Integer)
    Dim srcWb As Workbook
    Dim tgtWb As Workbook
    Dim srcWs As Worksheet
    Dim tgtWs As Worksheet
    Dim lastRow As Long
    Dim i As Integer

    Set srcWb = ThisWorkbook
    Set tgtWb = Workbooks.Open("C:\Temp\Targetbook.xlsx")
    Set srcWs = srcWb.Sheets("BSC")
    Set tgtWs = tgtWb.Sheets("BSC")

    For i = 1 To colCount
        lastRow = srcWs.Cells(srcWs.Rows.Count, i).End(xlUp).Row
        srcWs.Range(srcWs.Cells(1, i), srcWs.Cells(lastRow, i)).Copy tgtWs.Cells(1, i)
    Next i

End Sub

