Results 1 to 10 of 10

Code not copying and transposing data from all other files in Directory

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365 Version 2501 64-bit
    Posts
    1,474

    Code not copying and transposing data from all other files in Directory

    User has a MASTER and two UPLOAD files in same Folder.

    Code should open each workbook in turn and, if is not the MASTER workbook, copy the data from Sheet 1 of the UPLOAD file, transpose and paste it to next free row on sheet 1 of MASTER, close the UPLOAD workbook, and move on to next one:

    Option Explicit
    Dim f As Long
    Dim Filename As String
    Dim wbDst As Workbook, wbSrc As Workbook
    Dim wsDst As Worksheet, wsSrc As Worksheet
    
    Sub MergeFilesInFolder()
    
    'Find next free row in Master sheet
    
        With ActiveSheet
        f = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        If f < 2 Then f = 2
        
        Set wbDst = ThisWorkbook
        Set wsDst = ActiveSheet
        
                    
    'Loop through all files in folder
    
        Filename = Dir(CurDir() & "\*.xlsx")
        
    'Ignore the Master workbook
     
    '**Throwing Runtime Error 438 here   
        Do While Filename <> "" And Filename <> wbDst
            Set wbSrc = Workbooks.Open(Filename)
    
    'Copy all date from Source sheet
                With wbSrc
                Set wsSrc = .Sheet1
                wsSrc.Range("A1").UsedRange.Copy
                        
    'Transpose and paste to last row on this worksheet
    
                With wsDst
                .Range("A" & f).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
                Application.CutCopyMode = False
                End With
                
    'Close other file without saving changes
                wbSrc.Close savechanges:=False
                
                End With
                
    'Move to next workbook
    
                Filename = Dir
        
        Loop
        
        End With
        
    End Sub
    Any solutions, suggestions or alternatives welcome as ever.

    Ochimus
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Code for Copying data from different files to Destination file
    By mtakawane in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-25-2018, 03:16 AM
  2. VBA code to open all files in a directory and move data to destination sheet
    By anthony777 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-08-2017, 04:45 PM
  3. [SOLVED] VBA code to open all files in a directory and move data to destination sheet
    By anthony777 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-07-2017, 04:10 PM
  4. Copying Files from One Directory to Another
    By tomtom412 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-16-2017, 04:55 PM
  5. VBA code to find .asc files in a directory
    By dubbdan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-11-2013, 05:41 PM
  6. Replies: 0
    Last Post: 03-13-2013, 09:08 PM
  7. Copying Files to Current Directory and Compiling an Outline
    By mike@lcs in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-02-2012, 10:00 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1