Copying Multiple workbooks columns into another workbook.

Motestfilter

New Member
Joined
Aug 6, 2019
Messages
13
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.

Code:
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?
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Motestfilter

New Member
Joined
Aug 6, 2019
Messages
13
Forgot to add that the column positions differ between workbooks and workbooks have differing number of columns. So basically, the logic would be searching for the columns I need, and then copy those columns of data from the workbooks and combine them into one master file.
 

Motestfilter

New Member
Joined
Aug 6, 2019
Messages
13
I've tried using this code:

Code:
Sub Merges()

    Dim strFileName As String
    Dim strFilesLike As String
    Dim strPathName As String
    Dim strCurrentFile As String
 pth = "C:\Users\totoro\Desktop\CSAT\"
Set tgt = Workbooks.Open(pth & "master.xlsm")
    strPathName = "C:\Users\totoro\Desktop\CSAT\"
    strFilesLike = "*.xlsx*"
    strFileName = strPathName & strFilesLike


    strCurrentFile = Dir(strFileName)
    Do While strCurrentFile <> ""
    
        ' Combine file data code goes here
 Set src = Workbooks.Open(strFileName)
      Set dest = tgt.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Set src = ActiveWorkbook
    With src.Sheets("Sheet1")
        Set colh = .Range("1:1").Find("Respondent ID")
        cnt = Cells(Rows.Count, colh.Column).End(xlUp).Row - 1
        dest.Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
        Set colh = .Range("1:1").Find("Start Date")
        dest.Offset(, 1).Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
    End With
    src.Close False
        ' Get next file to Import
        strCurrentFile = Dir
    Loop
  


End Sub
but I am getting a "method of open workbooks failed" error and it highlights " Set src = Workbooks.Open(strFileName)"
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
Hi
try to change to
Code:
 Set src = Workbooks.Open(strCurrentFile)
 

Motestfilter

New Member
Joined
Aug 6, 2019
Messages
13
Hi
try to change to
Code:
 Set src = Workbooks.Open(strCurrentFile)
Hi. Thanks for the reply. Tried that and got this error:

"Sorry we couldn't find. Is it possible it was moved, renamed or deleted?" with this highlighted - Set src = Workbooks.Open(strCurrentFile)
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
So you have to check the file extension xlsx?
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
Code:
 Set src = Workbooks.Open(strPathName & strCurrentFile)
 

Motestfilter

New Member
Joined
Aug 6, 2019
Messages
13
Code:
 Set src = Workbooks.Open(strPathName & strCurrentFile)
Thanks a lot. it worked! Do you know which part should I change so it would start pasting on the second columns rather than the first one and also to paste it on the 10th row instead of the third? thanks
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,680
Messages
5,488,228
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top