Results 1 to 9 of 9

Thread: Copy multiple column.

  1. #1
    Board Regular
    Join Date
    Sep 2011
    Posts
    180
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Copy multiple column.

    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.

  2. #2
    Board Regular
    Join Date
    Sep 2011
    Posts
    180
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy multiple column.

    Nobody?

  3. #3
    Board Regular
    Join Date
    Sep 2011
    Posts
    180
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy multiple column.

    bump

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,002
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Copy multiple column.

    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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Board Regular
    Join Date
    Sep 2011
    Posts
    180
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy multiple column.

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

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,814
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Copy multiple column.

    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 by Joe4; Sep 20th, 2019 at 08:21 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,814
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Copy multiple column.

    Borrowing the logic from this link I found with a Google Search: http://codevba.com/office/loop_files...m#.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 by Joe4; Sep 20th, 2019 at 10:55 AM. Reason: removed unnecessary declared variable that wasn't being used
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    Board Regular
    Join Date
    Sep 2011
    Posts
    180
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy multiple column.

    Thank you so much.

  9. #9
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,814
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Copy multiple column.

    You are welcome.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •