Copy multiple column.

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
285
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,135
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
285
I didn't want to upset anyone.
I apologize if I offended anyone.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,644
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
53,644
Office Version
365
Platform
Windows
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
53,644
Office Version
365
Platform
Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,134
Messages
5,484,926
Members
407,474
Latest member
Pam Sander

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top