vba help to copy specific colulmn.. from a sheet

[malleshg24]

Hi Team,


I am looking for a vba code, to copy specific column from sheets(1) [ 17 Column out of 80 column]

Below I just mentioned 5 column to copy, later i can increase it . if you guys help me in coding.

Copy and paste it in my Macro books. (sheet2) . I want to use Ubound and Lbound array concept here.

I am new in VBA , Here i am copied the code from net. Please assist. how i can go with this or share some code to handle this task.


Sub Test
Dim LR As Long, MyCopyRange As Variant, MyPasteRange As Variant, X As Long
Dim wbk1 as workbook.

Set wbk1 = Workbooks.open("D:\D Drive\mis\daily report\15 august.xls")

set wbk2 = Thisworkbook.worksheets()


wbk1.sheets(1).activate


MyCopyRange = Array("AC2:AC" & LR, "D2:DC" & LR, "I2:I" & LR, "K2:K" & LR ) ' Column to pick.
MyPasteRange = Array("C2", "D2", "S2", "M2") Column where I want to paste the value


For X = LBound(MyCopyRange) To UBound(MyCopyRange) 'Loop the array copying and pasting based on element in the array
wbk(1).sheet(1).Range(MyCopyRange).Copy

Thisworkbook.sheets("Macro").Range(MyPasteRange).PasteSpecial xlPasteValues
Next x

End sub

[Bob Phillips]

Sub Test()
Dim LMyCopyRange As Variant, MyPasteRange As Variant
Dim LR As Long, X As Long
Dim wbk1 As Workbook, wbk2 As Workbook

Set wbk1 = Workbooks.Open("D:\D Drive\mis\daily report\15 august.xls")
Set wbk2 = ThisWorkbook.Worksheets()

wbk1.Sheets(1).Activate

LR = wbk(1).Sheet(1).Range("AC").End(xlDown).Row

MyCopyRange = Array("AC2:AC" & LR, "D2:DC" & LR, "I2:I" & LR, "K2:K" & LR) ' Column to pick.
MyPasteRange = Array("C2", "D2", "S2", "M2") 'Column where I want To paste the value


For X = LBound(MyCopyRange) To UBound(MyCopyRange) 'Loop the array copying and pasting based on element in the array

wbk(1).Sheet(1).Range(MyCopyRange(X)).Copy

ThisWorkbook.Sheets("Macro").Range(MyPasteRange(X)).PasteSpecial xlPasteValues
Next X
End Sub