Copy and Paste data from multiple workbooks into one workbook - Page 3
Thanks Thanks:  0
Likes Likes:  0
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 34

Thread: Copy and Paste data from multiple workbooks into one workbook

  1. #21
    Board Regular
    Join Date
    Apr 2012
    Location
    Texas, USA
    Posts
    515
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy and Paste data from multiple workbooks into one workbook

     
    If the macro was reaching the MsgBox, I'd say you're right about the reference. I think it might be this one that is causing the problem:
    Code:
    If ThisWorkbook.Sheets(1).Range("E" & i) = "" Then
    Isn't the new column for status in column G? If the values in column E are never empty, the stuff inside that If statement won't run, so it just skips over all the rows.

    On a side note, do you know how to debug by stepping through the code (insert a break line in the code and when the code stops, press F8 key to execute each line)? If the table changes every now and then, you may find yourself digging into the code and making changes each time it does.

    If you have two monitors, it helps to step through each line of code and see what is happening (VB Editor on one and Excel on the other).

  2. #22
    Board Regular
    Join Date
    Sep 2009
    Posts
    179
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy and Paste data from multiple workbooks into one workbook

    Quote Originally Posted by AFPathfinder View Post
    If the macro was reaching the MsgBox, I'd say you're right about the reference. I think it might be this one that is causing the problem:
    Code:
    If ThisWorkbook.Sheets(1).Range("E" & i) = "" Then
    Isn't the new column for status in column G? If the values in column E are never empty, the stuff inside that If statement won't run, so it just skips over all the rows.

    On a side note, do you know how to debug by stepping through the code (insert a break line in the code and when the code stops, press F8 key to execute each line)? If the table changes every now and then, you may find yourself digging into the code and making changes each time it does.

    If you have two monitors, it helps to step through each line of code and see what is happening (VB Editor on one and Excel on the other).

    Got it. I have changed the column reference to the G in the below code line. but now it shows error message that the file not found for the file which is not available. Actually column G wouldn't have the status of the unavailable files already updated, it will be completely blank when you start running the macro, if the macro doesn't find the source file which is mentioned in column A, on the path which is mentioned in column B then instead of showing error, it should populate "File Not Available" text in the column G for that row and move to the next row. but I believe right now, it's actually checking the column G for the file availability status .i.e. if it's blank then it will attempt to open the file and copy and paste the data but if it's not blank then it would skip it. We wouldn't know if the files are available for the day since multiple users are savings those files in multiple shared paths hence instead of opening each folder and checking, the macro checks it .i.e. if it's unable to find the file when trying to open then instead of showing error it simply populates the column G with "File not available". Now I am aware that this could happen even if the naming convention of the source file is slightly different from what is mentioned in the macro file column A but that will be taken care by user who is saving the source file as he will be asked to stick to the standard naming convention with no changes to be saved daily on the listed path.

  3. #23
    Board Regular
    Join Date
    Apr 2012
    Location
    Texas, USA
    Posts
    515
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy and Paste data from multiple workbooks into one workbook

    I believe you received that error because you removed the error handler code, right? I suppose the proper way to do that is to create a separate function "IsFound" that returns a boolean based on if the file exists and can be opened.

    The error handler is implicit as it doesn't check for that particular error and It doesn't specify the difference between an input error in the table and a legitimate "file doesn't exist".

  4. #24
    Board Regular
    Join Date
    Sep 2009
    Posts
    179
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy and Paste data from multiple workbooks into one workbook

    Quote Originally Posted by AFPathfinder View Post
    I believe you received that error because you removed the error handler code, right? I suppose the proper way to do that is to create a separate function "IsFound" that returns a boolean based on if the file exists and can be opened.

    The error handler is implicit as it doesn't check for that particular error and It doesn't specify the difference between an input error in the table and a legitimate "file doesn't exist".

    Ok. here is the updated code, I have updated the column reference as per the below table (which is final), I know that I had commented out the error handler earlier but i made the change back to the earlier code but still i was receiving the error msg for file not found first from excel and then when I click ok, it was updating the "File not available" text in column G, hence i had added Application.displayalert as false and then at the end of the code back to true. but it seems that the code is missing the target/template file information, .i.e. it opens the source file from the source file path and copies the data but doesn't open the target/template file and paste the data in it.

    here are the details once again (as per the below table snapshot):
    C1 = Template/Target File name
    E1 = Template/Target File path
    Column A starting row 4 : Source file names.
    Column B starting row 4: Source file paths
    Column C starting row 4: source workbook's worksheet names
    Column D starting row 4: source file data range (range which needs to be copied)
    Column E starting row 4: Target / Template workbook's worksheet names
    Column F starting row 4: Target/Template file paste range
    Column G starting row 4: Update the status of the file, if the file is not available then update "File not available" and if file is available post copy and pasting update text "file available. data copied". even if this column already has the values .i.e. "file not available" still it should check for the files because, for example, the user would run the macro at 3 pm when all files are not available so for those files, column G would be updated as file not available but then he gets the confirmation at 4 pm that all files are now available so when he reruns the macro, it would ignore the rows which has values .i.e file not available.








    Code:
    Sub WorkbookLoop()    Dim i As Integer
        Dim j As Integer
        Dim finalRow As Integer
        Dim masterWb As Worksheet
        Dim sPath As String
        Dim sFileName As String
        
        Set masterWb = ThisWorkbook.Sheets(1)
        
        finalRow = Cells(Rows.Count, 1).End(xlUp).Row
        Application.DisplayAlerts = False
        On Error GoTo MissWB
        For i = 4 To finalRow
            sPath = masterWb.Range("B" & i).Value
            sFileName = masterWb.Range("A" & i).Value
            
            If ThisWorkbook.Sheets(1).Range("G" & i) <> "File Available. Data Copied" Then
                Workbooks.Open Filename:=sPath & sFileName
                
                For j = i To finalRow
                
                    If Cells(j, 2).Value = sPath And Cells(j, 1).Value = sFileName Then
                        Range(masterWb.Range("D" & j).Value).Copy _
                            Destination:=Workbooks(masterWb.Range("D1").Value).Sheets(masterWb.Range("E" & i).Value).Range(masterWb.Range("F" & j).Value)
                        master.Range("G" & j).Value = "File Available. Data Copied"
                        
                    End If
                    
                Next j
                Workbooks(sFileName).Close SaveChanges:=False
                
            End If
            
    NextWB:
        Next i
        
        On Error GoTo 0
        
        MsgBox "Done."
        
        Set masterWb = Nothing
         Application.DisplayAlerts = True
        Exit Sub
        
    MissWB:
        masterWb.Range("G" & i).Value = "File Not Available"
        Resume NextWB
    End Sub
    can you please help to include the target/template sheet name and paste ranges to the above code which are in column E and F

  5. #25
    Board Regular
    Join Date
    Apr 2012
    Location
    Texas, USA
    Posts
    515
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy and Paste data from multiple workbooks into one workbook

    Try this:
    Code:
    Sub WorkbookLoop()
        Dim i As Integer
        Dim j As Integer
        Dim finalRow As Integer
        Dim masterWb As Worksheet
        Dim templateWb As Workbook
        Dim sPath As String
        Dim sFileName As String
        
        Set masterWb = ThisWorkbook.Sheets(1)
        Workbooks.Open masterWb.Range("C1").Value & Range("F1").Value
        Set templateWb = ActiveWorkbook
        
        finalRow = Cells(Rows.Count, 1).End(xlUp).Row
        Application.DisplayAlerts = False
        On Error GoTo MissWB
        For i = 4 To finalRow
            sPath = masterWb.Range("B" & i).Value
            sFileName = masterWb.Range("A" & i).Value
            
            If ThisWorkbook.Sheets(1).Range("G" & i) <> "File Available. Data Copied" Then
                Workbooks.Open Filename:=sPath & sFileName
                
                For j = i To finalRow
                
                    If Cells(j, 2).Value = sPath And Cells(j, 1).Value = sFileName Then
                        Range(masterWb.Range("D" & j).Value).Copy _
                            Destination:=templateWb.Sheets(masterWb.Range("E" & j).Value).Range(masterWb.Range("F" & j).Value)
                        master.Range("G" & j).Value = "File Available. Data Copied"
                        
                    End If
                    
                Next j
                Workbooks(sFileName).Close SaveChanges:=False
                
            End If
            
    NextWB:
        Next i
        
        On Error GoTo 0
        
        MsgBox "Done."
        
        Set masterWb = Nothing
        Application.DisplayAlerts = True
        Exit Sub
    
        
    MissWB:
        masterWb.Range("G" & i).Value = "File Not Available"
        Resume NextWB
    End Sub

  6. #26
    Board Regular
    Join Date
    Sep 2009
    Posts
    179
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy and Paste data from multiple workbooks into one workbook

    Quote Originally Posted by AFPathfinder View Post
    Try this:
    Code:
    Sub WorkbookLoop()
        Dim i As Integer
        Dim j As Integer
        Dim finalRow As Integer
        Dim masterWb As Worksheet
        Dim templateWb As Workbook
        Dim sPath As String
        Dim sFileName As String
        
        Set masterWb = ThisWorkbook.Sheets(1)
        Workbooks.Open masterWb.Range("C1").Value & Range("F1").Value
        Set templateWb = ActiveWorkbook
        
        finalRow = Cells(Rows.Count, 1).End(xlUp).Row
        Application.DisplayAlerts = False
        On Error GoTo MissWB
        For i = 4 To finalRow
            sPath = masterWb.Range("B" & i).Value
            sFileName = masterWb.Range("A" & i).Value
            
            If ThisWorkbook.Sheets(1).Range("G" & i) <> "File Available. Data Copied" Then
                Workbooks.Open Filename:=sPath & sFileName
                
                For j = i To finalRow
                
                    If Cells(j, 2).Value = sPath And Cells(j, 1).Value = sFileName Then
                        Range(masterWb.Range("D" & j).Value).Copy _
                            Destination:=templateWb.Sheets(masterWb.Range("E" & j).Value).Range(masterWb.Range("F" & j).Value)
                        master.Range("G" & j).Value = "File Available. Data Copied"
                        
                    End If
                    
                Next j
                Workbooks(sFileName).Close SaveChanges:=False
                
            End If
            
    NextWB:
        Next i
        
        On Error GoTo 0
        
        MsgBox "Done."
        
        Set masterWb = Nothing
        Application.DisplayAlerts = True
        Exit Sub
    
        
    MissWB:
        masterWb.Range("G" & i).Value = "File Not Available"
        Resume NextWB
    End Sub

    I have changed the cell references in the below line of code:

    Code:
     Set masterWb = ThisWorkbook.Sheets(1)
        Workbooks.Open masterWb.Range("E1").Value & Range("C1").Value
        Set templateWb = ActiveWorkbook
    but it still opens the template file and doesn't open the source files from the column A and copy and paste the data into the template file.

  7. #27
    Board Regular
    Join Date
    Apr 2012
    Location
    Texas, USA
    Posts
    515
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy and Paste data from multiple workbooks into one workbook

    Try this one:
    Code:
    Sub WorkbookLoop()
        Dim i As Integer
        Dim j As Integer
        Dim finalRow As Integer
        Dim masterWb As Worksheet
        Dim templateWb As Workbook
        Dim sPath As String
        Dim sFileName As String
        
        Set masterWb = ThisWorkbook.Sheets(1)
        finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
        Workbooks.Open masterWb.Range("E1").Value & Range("C1").Value
        Set templateWb = ActiveWorkbook
        
        Application.DisplayAlerts = False
        On Error GoTo MissWB
        For i = 4 To finalRow
            sPath = masterWb.Range("B" & i).Value
            sFileName = masterWb.Range("A" & i).Value
            
            If ThisWorkbook.Sheets(1).Range("G" & i) <> "File Available. Data Copied" Then
                Workbooks.Open Filename:=sPath & sFileName
                
                For j = i To finalRow
                
                    If Cells(j, 2).Value = sPath And Cells(j, 1).Value = sFileName Then
                        Range(masterWb.Range("D" & j).Value).Copy _
                            Destination:=templateWb.Sheets(masterWb.Range("E" & j).Value).Range(masterWb.Range("F" & j).Value)
                        master.Range("G" & j).Value = "File Available. Data Copied"
                        
                    End If
                    
                Next j
                Workbooks(sFileName).Close SaveChanges:=False
                
            End If
            
    NextWB:
        Next i
        
        On Error GoTo 0
        
        MsgBox "Done."
        
        Set masterWb = Nothing
        Application.DisplayAlerts = True
        Exit Sub
    
        
    MissWB:
        masterWb.Range("G" & i).Value = "File Not Available"
        Resume NextWB
    End Sub
    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    If you can get it into Excel, imagination is the usual limitation.

  8. #28
    Board Regular
    Join Date
    Sep 2009
    Posts
    179
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy and Paste data from multiple workbooks into one workbook

    Quote Originally Posted by AFPathfinder View Post
    Try this one:
    Code:
    Sub WorkbookLoop()
        Dim i As Integer
        Dim j As Integer
        Dim finalRow As Integer
        Dim masterWb As Worksheet
        Dim templateWb As Workbook
        Dim sPath As String
        Dim sFileName As String
        
        Set masterWb = ThisWorkbook.Sheets(1)
        finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
        Workbooks.Open masterWb.Range("E1").Value & Range("C1").Value
        Set templateWb = ActiveWorkbook
        
        Application.DisplayAlerts = False
        On Error GoTo MissWB
        For i = 4 To finalRow
            sPath = masterWb.Range("B" & i).Value
            sFileName = masterWb.Range("A" & i).Value
            
            If ThisWorkbook.Sheets(1).Range("G" & i) <> "File Available. Data Copied" Then
                Workbooks.Open Filename:=sPath & sFileName
                
                For j = i To finalRow
                
                    If Cells(j, 2).Value = sPath And Cells(j, 1).Value = sFileName Then
                        Range(masterWb.Range("D" & j).Value).Copy _
                            Destination:=templateWb.Sheets(masterWb.Range("E" & j).Value).Range(masterWb.Range("F" & j).Value)
                        masterWb.Range("G" & j).Value = "File Available. Data Copied"
                        
                    End If
                    
                Next j
                Workbooks(sFileName).Close SaveChanges:=False
                
            End If
            
    NextWB:
        Next i
        
        On Error GoTo 0
        
        MsgBox "Done."
        
        Set masterWb = Nothing
        Application.DisplayAlerts = True
        Exit Sub
    
        
    MissWB:
        masterWb.Range("G" & i).Value = "File Not Available"
        Resume NextWB
    End Sub
    Now it opens the source files as well and switches between template file and source file but doesn't copy any range from source file and doesn't paste any data into the template file range. can you please check the below piece of code again, i believe something is still going wrong.

    Code:
    For j = i To finalRow
                
                    If Cells(j, 2).Value = sPath And Cells(j, 1).Value = sFileName Then
                        Range(masterWb.Range("D" & j).Value).Copy _
                            Destination:=templateWb.Sheets(masterWb.Range("E" & j).Value).Range(masterWb.Range("F" & j).Value)
                        masterWb.Range("G" & j).Value = "File Available. Data Copied"
                        
                    End If
                    
                Next j
                Workbooks(sFileName).Close SaveChanges:=False

  9. #29
    Board Regular
    Join Date
    Apr 2012
    Location
    Texas, USA
    Posts
    515
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy and Paste data from multiple workbooks into one workbook

    Something might very well be going wrong (error handling might be skipping rows), but I don't have a multi-workbook setup like yours to do the debugging and testing. A solution like this is pretty specific and you'll need to do the debugging on your end to sniff out where the problems might be.
    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    If you can get it into Excel, imagination is the usual limitation.

  10. #30
    Board Regular
    Join Date
    Sep 2009
    Posts
    179
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy and Paste data from multiple workbooks into one workbook

      
    Quote Originally Posted by AFPathfinder View Post
    Something might very well be going wrong (error handling might be skipping rows), but I don't have a multi-workbook setup like yours to do the debugging and testing. A solution like this is pretty specific and you'll need to do the debugging on your end to sniff out where the problems might be.

    It's not skipping any rows, it goes each row after row and switches between both template and source workbook but doesn't select the copy range from the source workbook and also doesn't paste anything into the template workbook. I tried to check it a couple of times from my end but i am unable to figure what's going wrong. I even added the .Paste at the end of the Destination line but it doesn't seem to work. Also it's not updating the text "File Available. Data copied" in the column G but it doesn't update "File Not Available" text as well which means its not encountering any error then what else could be the problem.

    Code:
    For j = i To finalRow            
                    If Cells(j, 2).Value = sPath And Cells(j, 1).Value = sFileName Then
                        Range(masterWb.Range("D" & j).Value).Copy _
                            Destination:=templateWb.Sheets(masterWb.Range("E" & j).Value).Range(masterWb.Range("F" & j).Value).Paste
                        masterWb.Range("G" & j).Value = "File Available. Data Copied"
                        
                    End If
                    
                Next j
                Workbooks(sFileName).Close SaveChanges:=False

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
  •  

 

 
DMCA.com