Link to home
Create AccountLog in
Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland

asked on

edit macro to copy and past only unique values

Hi Experts

How wouldyou edit the following macro to so when the data is copy across from file on c:\....it only copies across unique values....

(each workbook has one worksheet only) which has the same name as the master file - which is open when the macro is run....

Sub Conso()
 
    Dim wbDst As Workbook
    Dim wbSrc As Workbook
    Dim wsSrc As Worksheet
    Dim wsDst As Worksheet
    Dim rngDst As Range
    Dim strFilename As String
    Dim StrPath As String
   
    Application.DisplayAlerts = False
   
           'Update File Path To Suite Requirments - Test Path Only
           
           StrPath = "C:\OutlookAttachments\CMU Attachments\"
           
           Set wbDst = ThisWorkbook
           
           Set wsDst = wbDst.Worksheets("ABC")
               
           Set rngDst = wsDst.Range("M17")
               
                strFilename = Dir(StrPath & "*.xlsm")
               
                While strFilename <> ""
               
                If strFilename <> wbDst.Name Then
               
                    Set wbSrc = Workbooks.Open(StrPath & strFilename, UpdateLinks:=3)
                   
                    Set wsSrc = wbSrc.Worksheets("ABC")
                   
                    With wsSrc
                        .Range("M17", .Range("M" & .Rows.Count).End(xlUp).Offset(0, 31)).Copy rngDst
                    End With
                   
                    Set rngDst = wsDst.Range("M" & Rows.Count).End(xlUp).Offset(0)
                   
                wbSrc.Close
                   
                    'wsDst.Range("M17:AR17").Delete xlShiftUp
              End If
           
            strFilename = Dir()
           
     Wend
 
End Sub
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Just for clarification you range is in Col M ?
gowflow
Can you post some data as you are using diffeent ranges that need to see how it implicate
gowflow
Avatar of route217

ASKER

I can change this later on... I.e. column M


Data to,flow
So you need the whole range that is being copied to be unique for every column or only Column M to have its vlaue unique ?
gowflow
Every column.....in data range
waiting for data
gowflow
Uploading
Ok here are the files....

So open the four sample files from the c: drive and paste the data into the same worksheet master workbook....close file...

If data already exist.....msg you are about to duplicate the data as that has already been copied across....
Master-File-V2.xls
Apples.xls
Bananas.xls
grapes.xls
Oranges.xls
Everything ok gow flow
Sorry for time lag just woke up !
I saw the file but not sure that is what you requested. Your intial post talks about saving only unqie data where when I see the file posted I guess you want to update in Master-File-V2.xls the data with the other files like you want to copy the data in the separate files in each coresponding sheet to Master-file-V2 replacing the existing data

Am I correct ? If not pls clarify what is needed.
gowflow
Correct 100%
Further clarification if I take for example sheet Apples you hv Apples1 Apples2 Apples2 and then in columns you have the dates Feb12 ....

So what you want in fact is to locate in the workbook Apples the Sheet Apples and when we find this specific Item Apples1 to go update the Values under each and every date like we need to match also the dates and then replace the values. Right ??

If my understanding is correct then I have 2 questions:
1) What if there is no values for an existing date shall we keep the existing value ?
2) What if an item that was in the file is not in the new file we keep it there ?

gowflow
Correct - to first part

1. There well always be data
2. Well always be there no blanks
Sorry for this pause.

I need to know more issues
Does your sheets looks exactly like the one you posted ie
dates are in row 3
values starts at row 11 and lables in Col A
??

OR you have data above row 11 and dates not in row 3 ???

Also need to know will you have diffrent dates in you main worksheet then in the separate worksheets ???

like in main
1/2 2/2 3/2 4/2 5/2                  ...              5/3 6/3 7/3              ...

and individual worksheets
25/2 26/2 27/2 28/2 1/3 2/3 3/3 4/3 5/3 6/3 7/3

In other words in Master-File you would have the whole year say and in the individual only the part that is being updated like if we are in June
Master-File would have Jan to Dec
and individual only June

would this be a possibility or ALWAYS you will have same dates in both workbooks and same items in both workbooks ?

In other words I want to know do I go into the hastle of checking each and every item and finding it coresponding item in Master=File and update the cell with the new value or I go BOOM replace the sheet in masterfile with the sheet of the individual file ???


gowflow
Gow flow...

1. Yes - dates row 3
2. No diff dates
3. Nope to whole year.
4. Same dates
5. Go BOOM and replace
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Thanks gow flow - testing...
ok  fine
gowflow
Any news on youur testing ?
gowflow
Either busy or overlooked this one ... any news ?
gowflow
Apologies been ill for the last seven day...

Work prefect - sorry
Tks and hope your feeling much better now. Take care and pls do let me know in here if you need further help on any issue I will be glad to assist you.
gowflow