Copy multiple column.

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
299
Office Version
365, 2016
Platform
Windows
Hello,

In a folder I have 2000+ files. I have to copy from each sheet1, range C5: Cx (each file has a certain number of rows ...),
in the "Master" sheet, (starting with A1) from the file where the VBA code is launched. The data to be copied are TEXT.
Can anyone help me with a VBA code?

Thanks in advance to those who will help me.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
Just a 'heads up' re your couple of bumps within the first day, which may actually be hurting your chances of a quick resolution rather than helping. I refer you to the second paragraph of number 12 of the Forum Rules
 

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
299
Office Version
365, 2016
Platform
Windows

ADVERTISEMENT

I didn't want to upset anyone.
I apologize if I offended anyone.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,137
Office Version
365
Platform
Windows
I didn't want to upset anyone.
I apologize if I offended anyone.
Bumping too early/often isn't offending anyone, it is just hurting your chances of getting timely replies by removing your thread from the "Zero Reply Posts" listing.
I usually recommend to people to wait 24 hours before bumping, so people from all over the world have a chance to see it in the "Zero Reply Posts" listing.

Regarding your question, here are a few things to get you started.
Do a Google Search on "Excel VBA loop through all files in a folder". You should be able to find lots of posts with the VBA code you need to do this.

You can find the last row in column C with data like this:
Code:
Dim lastRow as Long
lastRow = Cells(Rows.Count, "C").End(xlUp).Row
So then you can copy like:
Code:
Range("C5:C" & lastRow).Copy
You can use the same methodology for finding the last row on the sheet you are pasting to, and going down one more row (+1).

See how far you get with that, and if you run into trouble, post back here with your code and questions.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,137
Office Version
365
Platform
Windows

ADVERTISEMENT

Borrowing the logic from this link I found with a Google Search: http://codevba.com/office/loop_files_in_folder.htm#.XYTOIWlKiCg and the other tips I gave you, you can do something like this:
Code:
Sub MyCopyMacro()

    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim strFileName As String
    Dim strFolder As String
    Dim strFileSpec As String
    Dim lr2 As Long
    
    Application.ScreenUpdating = False
    
    'Specify folder name here (be sure to have slash at end)
    strFolder = "C:\temp\"
    
    'Limit to Excel files only
    strFileSpec = strFolder & "*.xls*"
    
'   Capture current workbook in workbook object
    Set wb1 = ActiveWorkbook
    
    strFileName = Dir(strFileSpec)
    Do While Len(strFileName) > 0
        'Open file
        Workbooks.Open Filename:=strFolder & strFileName
'       Capture new workbook in workbook object
        Set wb2 = ActiveWorkbook
'       Find last row with data in column C of new workbook
        lr2 = Cells(Rows.Count, "C").End(xlUp).Row
'       Copy data from row 5 to end
        Range("C5:C" & lr2).Copy
'       Go to original file
        wb1.Activate
'       Paste data to first available row in column A
        Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
'       Close new workbook
        wb2.Close
'       Move to next file
        strFileName = Dir
    Loop

    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"

End Sub
I added lots of documentation to explain what each step is doing.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,137
Office Version
365
Platform
Windows
You are welcome.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,348
Messages
5,510,781
Members
408,809
Latest member
Matthiasek

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top