• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Consolidate multiple excel files then delete the data from source files

L

Lizbeef

New Member
Hello
with the excellent help from an article on your website for consolidating multiple excel files I thought I had solved my problem - that is pull data from several source files into a master sheet then delete the data from source files and save them so that the next time I run the GetData code I am not pulling across duplicate info. So on my my Mac at home the attached files work. But to my disappointment when I use the same code at work on Windows the code opens source, copies data to clipboard but then does not copy to Master sheet and does not close and save source file. I really hope some one can maybe see why this is not working on Window 7/Excel 10.

The only difference I can see is that the file path is different as I use \ as separator in file path - and if I take out Selection.entrierow.delete it works in windows, it seems it does not like me changing source before data has been pasted.

Thank you in anticipation, I hope someone can help please. And apologies if I have not posted this correctly - I am new to this excellent excel source of information.
lizbeef
 

Attachments

  • Data Test save source file.xlsm
    42.6 KB · Views: 4
  • Test My Data.xlsm
    28.6 KB · Views: 2
Hi,

Welcome to the forum :)

It was a very simple mistake... you were deleting immediately after copying, and before pasting.
In windows, deleting something removes the previously copied data from the clipboard which makes it impossible to paste afterwards.
Can't say anything about MAC but it seems that the behavior is slightly different there.

Try:
Code: 
Public strFileName As String
Public currentWB As Workbook
Public dataWB As Workbook
Public strCopyRange As String

Sub GetData()
    Dim strWhereToCopy As String, strStartCellColName As String
    Dim strListSheet As String

    strListSheet = "List"

    On Error GoTo ErrH
    Sheets(strListSheet).Select
    Range("B2").Select

    'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
    Set currentWB = ActiveWorkbook
    Do While ActiveCell.Value <> ""
    
        strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
        strCopyRange = ActiveCell.Offset(0, 2) & ":" & ActiveCell.Offset(0, 3)
        strWhereToCopy = ActiveCell.Offset(0, 4).Value
        strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)
    
        Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=False
        Set dataWB = ActiveWorkbook
    
        Range(strCopyRange).Select
        Selection.Copy
    
    
        currentWB.Activate
        Sheets(strWhereToCopy).Select
        lastRow = LastRowInOneColumn(strStartCellColName)
        Cells(lastRow + 1, 1).Select
    
        Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
        Application.CutCopyMode = False
        dataWB.Sheets(1).Range(strCopyRange).Delete
        dataWB.Close True
    
    
    
    
    
        Sheets(strListSheet).Select
        ActiveCell.Offset(1, 0).Select
    Loop
    Exit Sub

ErrH:
    MsgBox "It seems some file was missing. The data copy operation is not complete."
    Exit Sub
End Sub

Public Function LastRowInOneColumn(col)
    'Find the last used row in a Column: column A in this example
    'http://www.rondebruin.nl/last.htm
    Dim lastRow As Long
    With ActiveSheet
    lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    End With
    LastRowInOneColumn = lastRow
End Function
 
Last edited:
PCosta87 said:
Hi,

Welcome to the forum :)

It was a very simple mistake... you were deleting immediately after copying, and before pasting.
In windows, deleting something removes the previously copied data from the clipboard which makes it impossible to paste afterwards.
Can't say anything about MAC but it seems that the behavior is slightly different there.

Try:
Code: 
Public strFileName As String
Public currentWB As Workbook
Public dataWB As Workbook
Public strCopyRange As String

Sub GetData()
    Dim strWhereToCopy As String, strStartCellColName As String
    Dim strListSheet As String

    strListSheet = "List"

    On Error GoTo ErrH
    Sheets(strListSheet).Select
    Range("B2").Select

    'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
    Set currentWB = ActiveWorkbook
    Do While ActiveCell.Value <> ""
  
        strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
        strCopyRange = ActiveCell.Offset(0, 2) & ":" & ActiveCell.Offset(0, 3)
        strWhereToCopy = ActiveCell.Offset(0, 4).Value
        strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)
  
        Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=False
        Set dataWB = ActiveWorkbook
  
        Range(strCopyRange).Select
        Selection.Copy
  
  
        currentWB.Activate
        Sheets(strWhereToCopy).Select
        lastRow = LastRowInOneColumn(strStartCellColName)
        Cells(lastRow + 1, 1).Select
  
        Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
        Application.CutCopyMode = False
        dataWB.Sheets(1).Range(strCopyRange).Delete
        dataWB.Close True
  
  
  
  
  
        Sheets(strListSheet).Select
        ActiveCell.Offset(1, 0).Select
    Loop
    Exit Sub

ErrH:
    MsgBox "It seems some file was missing. The data copy operation is not complete."
    Exit Sub
End Sub

Public Function LastRowInOneColumn(col)
    'Find the last used row in a Column: column A in this example
    'http://www.rondebruin.nl/last.htm
    Dim lastRow As Long
    With ActiveSheet
    lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    End With
    LastRowInOneColumn = lastRow
End Function
Click to expand...
PCosta87 said:
Hi,

Welcome to the forum :)

It was a very simple mistake... you were deleting immediately after copying, and before pasting.
In windows, deleting something removes the previously copied data from the clipboard which makes it impossible to paste afterwards.
Can't say anything about MAC but it seems that the behavior is slightly different there.

Try:
Code: 
Public strFileName As String
Public currentWB As Workbook
Public dataWB As Workbook
Public strCopyRange As String

Sub GetData()
    Dim strWhereToCopy As String, strStartCellColName As String
    Dim strListSheet As String

    strListSheet = "List"

    On Error GoTo ErrH
    Sheets(strListSheet).Select
    Range("B2").Select

    'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
    Set currentWB = ActiveWorkbook
    Do While ActiveCell.Value <> ""
   
        strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
        strCopyRange = ActiveCell.Offset(0, 2) & ":" & ActiveCell.Offset(0, 3)
        strWhereToCopy = ActiveCell.Offset(0, 4).Value
        strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)
   
        Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=False
        Set dataWB = ActiveWorkbook
   
        Range(strCopyRange).Select
        Selection.Copy
   
   
        currentWB.Activate
        Sheets(strWhereToCopy).Select
        lastRow = LastRowInOneColumn(strStartCellColName)
        Cells(lastRow + 1, 1).Select
   
        Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
        Application.CutCopyMode = False
        dataWB.Sheets(1).Range(strCopyRange).Delete
        dataWB.Close True
   
   
   
   
   
        Sheets(strListSheet).Select
        ActiveCell.Offset(1, 0).Select
    Loop
    Exit Sub

ErrH:
    MsgBox "It seems some file was missing. The data copy operation is not complete."
    Exit Sub
End Sub

Public Function LastRowInOneColumn(col)
    'Find the last used row in a Column: column A in this example
    'http://www.rondebruin.nl/last.htm
    Dim lastRow As Long
    With ActiveSheet
    lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    End With
    LastRowInOneColumn = lastRow
End Function
Click to expand...

Dear PCosta

When I found this excellent website it said become awesome in Excel - and this is awesome!! I cannot thank you enough, I think I might sign up to VBA classes here! I am really impressed by the way things are explained - and I think if I knew more of the why VBA works as well as the how I would get on faster!

Many thanks once again from a very happy Lizbeef! :):):):)
 
You must log in or register to reply here.
Back
Top