Simple Copy/Paste - columns from one sheet/workbook - to a different sheet/workbook

[jerryr0125]

Hi -
I am looking for a macro that would to the following :
- I am a current workbook (say workbookcalc)
- When the macro executes it references workbookdata in the same folder
- It goes to sheet1 in workbookdata
- Copies the first 10 columns (A:J)
- Pastes the 10 columns to workbookcalc sheet1

Thanks - jerry

[mdmackillop]

Sub Test()
    Dim ws As Worksheet
    Dim wbSource As Workbook
    Dim wbS As String
    Dim Pth As String
     
    wbS = "WorkBookData.xlsx"
    Set ws = ActiveSheet
    Pth = ThisWorkbook.Path
    On Error Resume Next
    Set wbSource = Workbooks(wbS)
    On Error GoTo 0
    If wbSource Is Nothing Then
        Set wbSource = Workbooks.Open(Pth & "\" & wbS)
    End If
    wbSource.Sheets("Sheet1").Range("A:J").Copy ws.Range("A1")
    Application.CutCopyMode = False
    wbSource.Close False
End Sub

[mdmackillop]

FYI you can create a Sub routine to which you pass the relevant parameters allowing you to reuse the same basic code

Sub Test1()
    Call DoCopy("WorkBookData.xlsx", "Sheet1", "A:J", ActiveSheet.Range("A1"))
End Sub


Sub DoCopy(wbs, sht, r, tgt)
    Dim wbsource As Workbook
    Dim Pth As String
    Application.ScreenUpdating = False
    Pth = ThisWorkbook.Path
    On Error Resume Next
    Set wbsource = Workbooks(wbs)
    On Error GoTo 0
    If wbsource Is Nothing Then
        Set wbsource = Workbooks.Open(Pth & "\" & wbs)
    End If
    wbsource.Sheets(sht).Range(r).Copy tgt
    Application.CutCopyMode = False
    wbsource.Close False
    Application.ScreenUpdating = True
End Sub