Copying Multiple workbooks columns into another workbook.

[Motestfilter]

Hi,

Good morning. I am very new to this. I am currently trying to create my first macro with the function of copying specific columns from different workbooks (4 workbooks) to a "master" workbook. The copied column should proceed to the last row on the master file. My code is painful to look at but it kind of works when I was just trying to copy from a single workbook but all hell broke loose when I tried to add another workbook to be copied from.

Sub Button1_Click()Dim a As Worksheet, y As Worksheet, LastRow&, b As Worksheet, c As Worksheet, d As Worksheet


Workbooks.Open ("C:\Users\Totoro\Desktop\Test\Test1.xlsx")
Workbooks.Open ("C:\Users\Totoro\Desktop\Test\Test2.xlsx")
Workbooks.Open ("C:\Users\Totoro\Desktop\Test\Test3.xlsx")
Workbooks.Open ("C:\Users\Totoro\Desktop\Test\Test4.xlsx")




Set a = Workbooks("Test1.xlsx").Worksheets("Sheet")
Set b = Workbooks("Test2.xlsx").Worksheets("Sheet")
Set c = Workbooks("Test3.xlsx").Worksheets("Sheet")
Set d = Workbooks("Test4.xlsx").Worksheets("Sheet")


Set x = ThisWorkbook.Worksheets("Sheet1")


    LastRow = a.Cells.SpecialCells(xlCellTypeLastCell).Row
    a.Range("A1:A" & LastRow).Copy x.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)


    LastRow = b.Cells.SpecialCells(xlCellTypeLastCell).Row
     b.Range("A1:A" & LastRow).Copy x.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)


    LastRow = c.Cells.SpecialCells(xlCellTypeLastCell).Row
     c.Range("A1:A" & LastRow).Copy x.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)


    LastRow = d.Cells.SpecialCells(xlCellTypeLastCell).Row
     d.Range("A1:A" & LastRow).Copy x.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)




Application.CutCopyMode = False


End Sub

On this code I am just trying to copy from a single column but I would later on add other columns when I figure out what am I doing.

1. Is it possible to do it without opening the source workbook?

 

[mohadin]

 [COLOR=#ff0000]dest.Offset(5, 10)[/COLOR].Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value

 

[Motestfilter]

 [COLOR=#ff0000]dest.Offset(5, 10)[/COLOR].Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value

I know this kinda lat but thank you so much for all the help!

 

[mohadin]

You'r well come
And thank you for feedback
Be happy