Copy multiple column.

Tom.Jones

Well-known Member
Joined
Sep 20, 2011
Messages
506
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Mobile
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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
 
Upvote 0
I didn't want to upset anyone.
I apologize if I offended anyone.
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
Solution

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top