VBA copy specific columns from closed file and paste into open file

[Palucci]

I have code which are copy all columns from closed select Range to open file which im working now. But could you tell me how to change code if i will need for example Just only few columns. For example (A, D, G, H) not 90 like a below

Sub hereitgoes()

Sheets("Reference List").Select
Range("J1:J90").Select
Selection.Copy

Workbooks.Open Filename:="PATH GOES HERE" mine is "C:\Users\luke\Desktop\xxxx.xlsm"
Sheets("Comparison Data").Select
Range("B1:B90").Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close

end sub

 

[Zot]

You can do something like this. By creating reference to workbook and sheet, you can avoid using Select which can cause confusion which workbook or sheet is active at the moment during execution. The macro will also runs faster.

Sub hereitgoes()

Dim wsSource As Worksheet, wsTarget As Worksheet
Dim wbSource As Workbook, wbTarget As Workbook

Application.ScreenUpdating = False

Set wsSource = wbSource.Sheets("Reference List")

Set wbTarget = Workbooks.Open(Filename:="C:\Users\luke\Desktop\xxxx.xlsm")
Set wsTarget = wbTarget.Sheets("Comparison Data")

wsSource.Range("A:A,D:D,G:G,H:H").Copy
wsTarget.Paste                                              ' The column A,D,G,H will be copied to column A,B,C,D in wbTarget

' Save and close wbTarget
wbTarget.Close True

End Sub

 

[Palucci]

If i would like to copy Row or some range like e68:g71 and copy on e15:g20 to another file similiar like you show. How it code be Look?

 

[Zot]

If i would like to copy Row or some range like e68:g71 and copy on e15:g20 to another file similiar like you show. How it code be Look?

If i would like to copy Row or some range like e68:g71 and copy on e15:g20 to another file similiar like you show. How it code be Look?

You can just put starting range in target sheet like this (shown in red). No need to calculate same range size

wsSource.Range("E68:G71").Copy wsTarget.Range("AD5")
wsSource.Range("E15:G20").Copy wsTarget.Range("X30")

or can also write like this

wsSource.Range("E68","G71").Copy wsTarget.Range("AD5")
wsSource.Range("E15","G20").Copy wsTarget.Range("X30")

 

[Palucci]

This is my code which i edited after was i posted here. And when i copy columns its working but Rows range still not.

Sub Przycisk11_Click()
Dim strPath As String
Dim wbMe, wb As Workbook

strPath = selectFile
If strPath = "" Then

Set wbMe = ActiveWorkbook

Set wb = Workbooks.Open(strPath, False, True)
wb.Sheets("vintage_agr").Columns("A:C").Copy Destination:=wbMe.Sheets("input_4").Range("A1")
wb.Sheets("vintage_agr").Columns("H").Copy Destination:=wbMe.Sheets("input_4").Range("D1")


'This is rows what i want copy from analityka_id-tabele and paste strona 3 but its not working :( !!!! HERE
wb.Sheets("analityka_id-tabele").Columns("E68:G71").Copy Destination:=wbMe.Sheets("strona 3").Range("E16")

wb.Close False
Set wb = Nothing

ThisWorkbook.RefreshAll
Beep
MsgBox "Mamy To!"
End If
End Sub

Private Function selectFile()
Dim fd As Office.FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
.InitialFileName = ActiveWorkbook.Path
.AllowMultiSelect = False
.Title = "Wybierz"
.Filters.Clear
.Filters.Add "Excel", "*.xlsm"

If .Show = True Then selectFile = .SelectedItems(1)


End With
End Function

 

[Palucci]

You can just put starting range in target sheet like this (shown in red). No need to calculate same range size

wsSource.Range("E68:G71").Copy wsTarget.Range("AD5")
wsSource.Range("E15:G20").Copy wsTarget.Range("X30")

or can also write like this

wsSource.Range("E68","G71").Copy wsTarget.Range("AD5")
wsSource.Range("E15","G20").Copy wsTarget.Range("X30")

Bu i want to

wsSource.Range("E68","G71").Copy wsTarget.Range("AD5")

and past there wsSource.Range("E15","G20").Copy wsTarget.Range

 

[Zot]

Your line
wb.Sheets("analityka_id-tabele").Columns("E68:G71").Copy Destination:=wbMe.Sheets("strona 3").Range("E16")

It should be Range("E68:G71") because you are not copying entire column but range from E68 to G71.