+ Reply to Thread
Results 1 to 15 of 15

Extracting data from two sheets in external workbooks - 2nd sheet won't paste

Hybrid View

  1. #1
    Registered User
    Join Date
    10-11-2013
    Location
    falkirk
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Extracting data from two sheets in external workbooks - 2nd sheet won't paste

    Please ignore last post - back on track again.
    Stepping through the program using F8, Filename 1 pastes to A2-A31 then sheet 4 data is pasted into B2-B31, then sheet 6 is pasted into B32-B61. Filename 2 pastes to A32-A61 and the next sheet 4 overwrites the previous data.
    Need Filename 1 A2-A61, sheet 4 cells to B2-B31, sheet 6 cells to B32-B61, then
    Filename 2 A62-A121, sheet 4 cells to B62-B91, sheet 6 cells to B92-B121 etc., etc.
    Cannot see how to increase the offset in column A with there being a knock on effect.
    Can anyone see how this could be modified?

    Current code:
    Sub MergeAllWorkbooks()
        Dim MyPath As String, FilesInPath As String
        Dim MyFiles() As String
        Dim SourceRcount As Long, FNum As Long
        Dim mybook As Workbook, BaseWks As Worksheet
        Dim sourceRange As Range, destrange As Range
        Dim sourceRange1 As Range, destrange1 As Range
        Dim rnum As Long, CalcMode As Long
    
        ' Change this to the path\folder location of your files.
        MyPath = "C:\temp1"
    
    
        ' Add a slash at the end of the path if needed.
        If Right(MyPath, 1) <> "\" Then
            MyPath = MyPath & "\"
        End If
    
        ' If there are no Excel files in the folder, exit.
        FilesInPath = Dir(MyPath & "*.xl*")
        If FilesInPath = "" Then
            MsgBox "No files found"
            Exit Sub
        End If
    
        ' Fill the myFiles array with the list of Excel files
        ' in the search folder.
        FNum = 0
        Do While FilesInPath <> ""
            FNum = FNum + 1
            ReDim Preserve MyFiles(1 To FNum)
            MyFiles(FNum) = FilesInPath
            FilesInPath = Dir()
        Loop
    
        ' Set various application properties.
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        ' Set worksheet for information to be merged to.
        Set BaseWks = Worksheets(1)
        rnum = 2
    
        ' Loop through all files in the myFiles array.
        If FNum > 0 Then
            For FNum = LBound(MyFiles) To UBound(MyFiles)
                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(MyPath & MyFiles(FNum))
                On Error GoTo 0
    
                If Not mybook Is Nothing Then
                    On Error Resume Next
    
                    ' Change this range to fit your own needs.
                    With mybook.Worksheets(6)
                        Set sourceRange = .Range("b5:b34")
                    With mybook.Worksheets(4)
                        Set sourceRange1 = .Range("b5:b34")
                    End With
                    End With
    
                    If Err.Number > 0 Then
                        Err.Clear
                        Set sourceRange = Nothing
                    Else
                        ' If source range uses all columns then
                        ' skip this file.
                        If sourceRange.Columns.Count >= BaseWks.Columns.Count Then
                            Set sourceRange = Nothing
                        End If
                    End If
                    On Error GoTo 0
    
                    If Not sourceRange Is Nothing Then
    
                        SourceRcount = sourceRange.Rows.Count
    
                        If rnum + SourceRcount >= BaseWks.Rows.Count Then
                            MsgBox "There are not enough rows in the target worksheet."
                            BaseWks.Columns.AutoFit
                            mybook.Close savechanges:=False
                            GoTo ExitTheSub
                        Else
    
                            ' Copy the file name in column A.
                            With sourceRange
                                BaseWks.Cells(rnum, "A"). _
                                        Resize(.Rows.Count).Value = MyFiles(FNum)
                            End With
    
                            ' Set the destination range.
                            Set destrange = BaseWks.Range("B" & rnum)
                            Set destrange1 = BaseWks.Range("B" & rnum + 30)
                            ' Copy the values from the source range
                            ' to the destination range. Overwrite is here
                            With sourceRange
                                Set destrange = destrange. _
                                                Resize(.Rows.Count, .Columns.Count)
                            destrange.Value = sourceRange.Value
                            End With
                            With sourceRange1
                                Set destrange1 = destrange1. _
                                                Resize(.Rows.Count, .Columns.Count)
                            destrange1.Value = sourceRange1.Value
                            End With
                            rnum = rnum + SourceRcount
                        End If
                    End If
                    mybook.Close savechanges:=False
                End If
    
            Next FNum
            BaseWks.Columns.AutoFit
        End If
    
    ExitTheSub:
        ' Restore the application properties.
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 365 on Win11 (365 on Win11 (desktop), 365 on Win11 (notebook)
    Posts
    8,207

    Re: Extracting data from two sheets in external workbooks - 2nd sheet won't paste

    Hi, ids,

                            ' Set the destination range.
                            Set destrange = BaseWks.Range("B" & rnum)
                            Set destrange1 = BaseWks.Range("B" & rnum + 30)
    are the least of the lines which would need to be altered (no sheet6 to be copied when I glimpsed through the code).

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 365 on Win11 (365 on Win11 (desktop), 365 on Win11 (notebook)
    Posts
    8,207

    Re: Extracting data from two sheets in external workbooks - 2nd sheet won't paste

    Hi, ids,

    regarding different Columns you could use code like
                            ' Set the destination range.
                            Set destrange = BaseWks.Cells(rnum, BaseWks.Cells(2, Columns.Count).End(xlToLeft).Column + 1)
                            Set destrange1 = BaseWks.Cells(rnum + 30, BaseWks.Cells(2, Columns.Count).End(xlToLeft).Column + 1)
    Regarding the rows where to copy you should comment out
                            rnum = rnum + SourceRcoun
    in order not to go down the rows.

    Ciao,
    Holger

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 0
    Last Post: 07-29-2013, 03:00 AM
  2. Extracting and combining data from specific sheet from multiple workbooks
    By aggies2010 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-19-2013, 03:39 PM
  3. Copy Paste Data across Workbooks with multiple sheets & Referencing.
    By v999 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-30-2012, 09:16 AM
  4. Replies: 5
    Last Post: 02-06-2012, 09:45 AM
  5. Replies: 7
    Last Post: 01-05-2009, 02:08 PM

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