• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to copy specific cumulus from one excel sheet to another excel

Ahmad Noor

New Member
Hi
I have 2 excel files
1. master1
2. master2
and i'm searching a vba to copy specific column from first excel sheet (master1) to the 2nd excel sheet (master2).
can anyone help me how to create vba code to do that? Thanks

P.S: check the attach example
 

Attachments

  • master1.xlsx
    7.9 KB · Views: 41
@Ahmad Noor ,
Change the files names in the code to whatever you want:
Code:
Sub CopyColumnToWorkbook()
Dim sourceColumn As Range, targetColumn As Range

Set sourceColumn = Workbooks("Source.xlsm").Worksheets(1).Columns("A")
Set targetColumn = Workbooks("Target.xlsm").Worksheets(1).Columns("A")

sourceColumn.Copy Destination:=targetColumn
EndSub
 
Hi Kmahraz :)

Many Thanks i will try the code.
How to choose multi column Like (A,C,F...) ?

Thanks again for ur help.

Ahmad :)
 
Hi @Kmahraz

i copy your code and change the target name and the source and I tried the code but there is an error (Run-time error 9, subscript out of range), can you help please ?

Ahmad
 
@Ahmad Noor
I modified the code, make sure the source file is closed (Tested), Change as you fit
Code:
Sub Button1_Click()
Dim x As Worksheet, y As Worksheet, LastRow&
'Change the link to where you saved your files
Workbooks.Open ("C:\Users\kmahraz\Desktop\New folder\Source.xlsm")

Set x = Workbooks("Source.xlsm").Worksheets("Sheet1")
Set y = ThisWorkbook.Worksheets("Sheet1")

LastRow = x.Cells.SpecialCells(xlCellTypeLastCell).Row

x.Range("A1:A" & LastRow).Copy y.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
x.Range("B1:B" & LastRow).Copy y.Cells(Rows.Count, "E").End(xlUp).Offset(1, 0)
x.Range("H1:H" & LastRow).Copy y.Cells(Rows.Count, "H").End(xlUp).Offset(1, 0)
x.Range("I1:I" & LastRow).Copy y.Cells(Rows.Count, "I").End(xlUp).Offset(1, 0)

Application.CutCopyMode = False

End Sub
 
Last edited:
Hi @Kmahraz

Thanks for the code earlier, if there multi sheets in workbook how to copy the columns from sheet1 and sheet2 in the same workbook? and when source workbook open how to close it after the copying finish? because every time when the copying finish the source file stay open. really Thank you for your help.

Ahmad
 
@Ahmad Noor
You can use something like:
Code:
ThisWorkbook.Worksheets("Sheet1").Range("L:M").Copy
wbDest.Worksheets("Sheet1").Range("L:M").Insert

ThisWorkbook.Worksheets("Sheet2").Range("Y:Z").Copy
wbDest.Worksheets("Sheet1").Range("Y:Z").Insert
..........

And

Code:
sourceColumn.Copy Destination:=targetColumn
targetColumn.Replace what:="[*]", replacement:=""
Application.CutCopyMode = False
Application.ScreenUpdating = True

To close the workbook you can use something like:

Code:
activeworkbook.save
activeworkbook.close

though generally it is safer to specify the workbook by name, also if you want to close excel
Code:
workbooks("Source.xlsm").close
application.quit

For more examples
https://support.microsoft.com/en-us/kb/213428
http://www.excel-easy.com/vba/examples/close-open.html
 
Back
Top