route217
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).Off set(0, 31)).Copy rngDst
End With
Set rngDst = wsDst.Range("M" & Rows.Count).End(xlUp).Offs et(0)
wbSrc.Close
'wsDst.Range("M17:AR17").D elete xlShiftUp
End If
strFilename = Dir()
Wend
End Sub
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
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).Off
End With
Set rngDst = wsDst.Range("M" & Rows.Count).End(xlUp).Offs
wbSrc.Close
'wsDst.Range("M17:AR17").D
End If
strFilename = Dir()
Wend
End Sub
Can you post some data as you are using diffeent ranges that need to see how it implicate
gowflow
gowflow
ASKER
I can change this later on... I.e. column M
Data to,flow
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
gowflow
ASKER
Every column.....in data range
waiting for data
gowflow
gowflow
ASKER
Uploading
ASKER
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
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
ASKER
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
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
ASKER
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
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
ASKER
Correct - to first part
1. There well always be data
2. Well always be there no blanks
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
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
ASKER
Gow flow...
1. Yes - dates row 3
2. No diff dates
3. Nope to whole year.
4. Same dates
5. Go BOOM and replace
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thanks gow flow - testing...
ok fine
gowflow
gowflow
Any news on youur testing ?
gowflow
gowflow
Either busy or overlooked this one ... any news ?
gowflow
gowflow
ASKER
Apologies been ill for the last seven day...
Work prefect - sorry
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
gowflow
gowflow