PDA

View Full Version : Simple Copy/Paste - columns from one sheet/workbook - to a different sheet/workbook



jerryr0125
05-18-2017, 12:56 AM
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
05-18-2017, 10:26 AM
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
05-18-2017, 10:57 AM
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