Macro for extracting data into a master excel file.

[[email protected]]

Hi

I've search around a bit for hint on creating such a macro but no luck =(
So I have an excel file that we add new data each day to that file. in column K, it has a date

So what I'm trying to do is to creat a macro that will copy and paste the entire ROW if the column K's date = today`s date.
And then it goes to the next row and look for the column K so on.

So it's like extracting data with a criteria.

Any idea how I should start ?

[xladept]

Hi Kengfak,

Something like this:

Please Login or Register  to view this content.

[[email protected]]

hi thx for the reply

I ran your code and an error occurs saying: For Each may only Iterate over a collection object or an array

[xladept]

Hi Kengfak,

Sorry - sloppy coding - try this:

Please Login or Register  to view this content.

[[email protected]]

Hi xladept,

When I run your code it highlight an error in this line---> Set Master = Worksheets("Master"): Set ws = ActiveSheet
Do I need to create a new sheet named "Master" in my workbook?
Or can you edit some code for me so that it paste those row into an existing file locate at C:/ ...../Master.xls ?

[xladept]

Hi Kengfak,

Change the name from "Master" to the actual name of your Master sheet. To paste I'd need to have the name of the sheet as well as the book.

[[email protected]]

by changing my excel file name to "Master" and having a sheet called also "Master"
it really work lol
Thank you sooooo much =)

can you explain to me this part in english lol can't seem to understand the code when written like that:

Please Login or Register  to view this content.

And if I ever want to to paste it into a new excel file in a different location how would I write?

[xladept]

Hi Kengfak,

For Each K will cause the program to loop through every range in the range specified

which is the entire populated K column (ws.Range("K2:K" & ws.Range("K" & Rows.Count).End(xlUp).row))

ws.Range("K" & Rows.Count).End(xlUp).row gives the last populated row in the K column

To paste elsewhere you need to specify the Worksheet and Range where it goes -

if the Worksheet is in a different book you would write:

Set MySheet=Workbooks(MyBookName in quotes).Worksheets(MySheetName in quotes)

[[email protected]]

I'm so glad to have you around
I printed your code and post it on my wall lol

so I'll come back to you if I can t figure something out =)

[xladept]

You're welcome - (you should mark the thread solved)

[[email protected]]

Hi again Adept =)

What if I want to add a new criteria before the criteria date?
say in colums L contains only "urgent" or blank.

So if colums L is "urgent" then copy row and paste it in master file And put the word "Done". else look at K'date if it is = to today's date

[xladept]

Hi Kengfak,

Use the offset to get the value in L since we're passing the values in Column K:

Please Login or Register  to view this content.

[[email protected]]

Hi adept,

Since I run your code on the sheet where we add new data daily. I have to make sure your code doesn t copy/paste twice.
that's why I want to change the "Urgent" to "Done" when the code is run in column L.

To give you more detail of my situation: If client want the data sooner then the due date (which is the column K) then we asked them to write urgent in column L. So that running your code will do the work and change back the urgent to done. But with the time passing. the Due date will eventually reach the date of today and trigger the criteria again right?

If I read your code correctly we will have issues ? right?

[xladept]

Hi Kengfak,

Yeah we have issues

Please Login or Register  to view this content.

[[email protected]]

I think it s perfect now =)

btw can you explain me this? can't grasp the meaning of it even after what u explains before lol:
I've learn that .Range(A:Z) in this form will be columA to columZ but you used .Range( & ) what will the "&" do here?

And have a great weekend Adept !
see you next week =)

[xladept]

Hi Kengfak,

The & is a string concatenater so Range("A" & r) if r is 27 is like writing Range("A27").

Next week it is! (Is this thread solved??)

[[email protected]]

Hi again Adept,

Now if we add a new criteria in ColumM where it is either PH or TE1
If it is PH, I want it to paste it in a sheet called "PH"
if it is TE1, I want it to paste it in sheet called "TE1"

Basically I now need to seperate those two seperately =(.

Also: K.Offset(0, 1) = "done"
Can we change the "done" to =date so that instead of writing "done" it will write today's date ?

[xladept]

Hi Kengfak,

Yes - just change the "done" to date. I'll look at the other issue tomorrow

Please Login or Register  to view this content.

[xladept]

Hi Kengfak,

Will there still be a "Master" or will that become "PH" and "TEI"??

If the latter then try this:

Please Login or Register  to view this content.

[[email protected]]

Hi Adept thank for your time in helping me.

There will not be a "sheet" called master anymore but one "sheet" called PH and the other sheet called "TE1"

Your code is perfect if it was only to copy paste in sheet called "master" How would you introduce it if it was to copy paste according in column M where it is either PH or TE1 and paste it in the right sheet ?

[xladept]

Hi Kengfak,

Did you try the code from post#19???

[[email protected]]

Hi adept,

the code from post#19 works. It paste in 2 sheet away from the active one. but how would you set in the new criteria of PH or TE1 now as mentionned in post#20 ?

[xladept]

Hi Kengfak,

Isn't K.Offset(0,2) either "PH" or "TE1"??

[[email protected]]

Hi Adept,

The code you privided me was good if it was to put that in sheet called "master" only. Since they added a new column M that identify each data from PH or from TE1, they wanted me to split those into different sheet according to either it was PH or TE1. so in cloumn K we have the date that will be sent when it is =today's date, in column L we have the "urgent" or blank cell And Column M the PH or TE1. it is to be paste in sheet according to either PH to sheet PH and TE1 to sheet TE1.

let me know if it need to be more clarified
thx in advance Adept

keng.

[xladept]

Hi Kengfak,

I guess that means that you tried it and it doesn't work?? Can you post a sample sheet?

[[email protected]]

Hi Kengfak,

I guess that means that you tried it and it doesn't work?? Can you post a sample sheet?

CWA.xls

Hi adept, there you go a sample of my workbook. I took out the master file already.
Just to let you know that there are some blank field because other department just didn't fill it out yet.
but the important one is in column K,L,M

[xladept]

Hi Kengfak,

Try this:

Please Login or Register  to view this content.

[[email protected]]

Hi adept, thank you so much i'm able to move on further now.

I just dont see how u were able to split them up. I dont see any "if=PH then paste it in sheet PH,or if =TE1 then" etc...
what part of the code did you write in VBA so that it were able to split them according to being PH or TE1 ?
is it the CStr ? I believe it is a formula in excel to convert the cell to string value...

can you explain a bit? =)

Keng.

[xladept]

Hi Kengfak,

You're welcome!

From post #23

(CStr(K.Offset(0, 2))) renders K.Offset(0,2) either "PH" or "TE1"