Remove Replication of Data when Copied to Dump File

[Xcelnewbie]

A friend of mine created dump file where all the data saved from individual trackers will be stored automatically. However, i noticed that there are times when the data being transferred/saved to the dump file gets replicated 4 times. So even if we only hit the "save" button in the individual tracker ones, 4 data will be saved in the dump. Below is the code my friend gave me...please check and let me know what must be removed.

Sub DumpFiles()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim ws As Worksheet
    Dim Dws As Worksheet
    Set ws = Sheets("Raw Data")
    Dim lrow As Long
    Dim Dlrow As Long ''used for the dump sheet
    ''''
    ''last row in users worksheet
    ''We will in this test copy the second row( not the header row )
    ''' to the last row up to Column L'
    ''' the last row code
    lrow = ws.Range("B65536").End(xlUp).Row '''for the users list
    ''' now copy the data to the dump sheet ''
    '' we also need to know the row to copy the data to in the Dump sheet
    '' Need to make sure Dump Workbook is open ''
    ''' You need to change to the actual dump file
    '' You also need to change the file path
    If Not WorkbookOpen("Productivity Tracker Dump.xlsm") Then
        '''''''''''''''''  This is my path need to update to yours '''
     
        Application.Workbooks.Open("\\Desktop\Productivity Tracker Dump.xlsm", UpdateLinks:=3) _
        .Activate
     
    Else
     
        Workbooks("Productivity Tracker Dump.xlsm").Activate
     
    End If
    Set Dws = Workbooks("Productivity Tracker Dump.xlsm").Sheets("Raw Data")
    Dlrow = Dws.Range("B65536").End(xlUp).Row + 1 ''we add 1 because its the next empty row
    '' this should copy the data
    ws.Range("A6:m" & lrow).Copy Destination:=Dws.Range("A" & Dlrow)
    '' Now we are done ''
    ActiveWorkbook.Close savechanges:=True ''we close the shared workbook and save changes
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub
 
 
Function WorkbookOpen(WorkBookName As String) As Boolean
    ' returns TRUE if the workbook is open
    WorkbookOpen = False
    On Error Goto WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

 

[SirJB7]

Hi, Xcelnewbie!

Not having checked the rest of the code, the point resides here:

'' this should copy the data
ws.Range("A6:m" & lrow).Copy Destination:=Dws.Range("A" & Dlrow)
'' Now we are done ''

But without a sample file or a detailed description of the data layout that would permit to discern what is duplicated and what not, it isn't safe to suggest you a solution. Sample file should be with replicated data, i f possible.

Regards!

 

[Xcelnewbie]

Thank you for looking into this SirJB7.
Attached is the individual tracker where the data are coming from and the dump file where they should be copied too once they save.

The data must be present in both raw data sheet (individual and dump files) after clicking the save button.

 

[Smallman]

OK I think I am following what you are trying to do. You are opening the Dump file and copying the data to the Template sheet.

Fair amount of code to do such as simple task, there is even a custom function in the mixing pot.

Lets get this streamlined a bit. Firstly can you remove the links from the Dump file? Links are just bad full stop. Never linked - never will.


Anyways here is all the code I think you need, using an unlinked workbook.

Sub OpenEx()
Dim owb As Workbook
 
Set owb = Workbooks.Open("C:\Users\HYMC\Excel\Helping\ADel\Productivity Tracker Dump.xlsm")
Sheets("Raw Data").Range("B6:m" & Cells(Rows.Count, 2).End(xlUp).Row).Copy
thisworkbook.Sheets("Raw Data").Range("B65536").End(xlUp)(2).PasteSpecial xlPasteValues
owb.Close False 'Close opened workbook don't save
End Sub

Obvo change the path to suit and why are you saving the file you are opening. I made the assumption you don't need to save the opened file.

Take care

Smallman

 

[Xcelnewbie]

Thanks for the codes Smallman. I tired it but i'm not sure why it didn't work.

According to my friend the link needs to be added since there will be more than 8 people whose data will be saved here simultaneously while they are working on their individual trackers.

 

[Smallman]

Just take the links out and test it. Go ahead do it now.....

After you remove the links you will notice how smoothly the code runs. Now work out how to run it with your links in. I tried these;

Workbooks.Open("\\Desktop\Productivity Tracker Dump.xlsm",3)

and

Application.DisplayAlerts = False

and

Application.AskToUpdateLinks = False

and nothing worked. Links really do get the the road of VB and XL performing optimally and you start to get an appreciation of why you might want to steer clear of them.

These may however work at your end as I do not have access to the linked files at your end.

I am a purist and I will not link workbooks.

Take care

Smallman

 

[lohithsriram]

Hi,

Are you using excel 2010? I experienced the file link issues in past and what I have found out was that to install latest hotfix for office version. There were some file link issues with the office products initially and later hotfixes helped to resolve those. Not sure if you are running with one of that version but recommended you download the latest cumulative hotfix package and install it either for 2007 or 2010.

http://support.microsoft.com/kb/2800779

 

[Xcelnewbie]

im just wondering, if i remove the link, how would the data from different productivity files be transferred to the dump file? i mean, how would the file know where to copy the data automatically?

 

[Smallman]

Xcelnewbie

I get the impression you are trying to design a mechanism without knowing fully what you want from the output. If you have many files which is what I suspect, then you will have to iterate through each of the files in a specific folder and push each files contents into the Template workbook. This is very possible.

If you just want to push one file in, then the process I have given you does this most effectively and efficiently.

Take care

Smallman