Copy multiple column.

[Tom.Jones]

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.

 

[Tom.Jones]

Nobody?

 

[Tom.Jones]

bump

 

[Peter_SSs]

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]

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

 

[Joe4]

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:

Dim lastRow as Long
lastRow = Cells(Rows.Count, "C").End(xlUp).Row

So then you can copy like:

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.

 

[Joe4]

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:

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.

 

[Tom.Jones]

Thank you so much.

 

[Joe4]

You are welcome.