If condition formula can support in macro?
- Thread starter bhasoriya
- Start date
NARAYANK991
Excel Ninja
Hi Kaushik ,
Use the complete address given ; copy and paste it in your browser and go , instead of clicking on the link.
Narayan
Use the complete address given ; copy and paste it in your browser and go , instead of clicking on the link.
Narayan
Hi Narayan,
Thank you for your instruction. Now I am able to download the workbooks.
Hi Bhasoriya,
I have seen all the workbooks but need to have some clarification from you.
In all three workbooks(Resource A, Resource B, and Resource C)you have mentioned that "If resouce has filled his personal sheet even single row or can be thousand, data should be update by date" where you want to construct the code with IF logic.
I am not able to understand your aforementioned statement entirely.
If resource A starts filling the data from A19 onwards(from last available row) and say till 1000 of rows, what you want to be updated by 'date' here?
Can you plz clarify this?
I am sorry if I fail to understand something which you might have already explained clearly.
@Narayan: If you have understood the requirement (needless to say), please share your thoughts.
Kaushik
Thank you for your instruction. Now I am able to download the workbooks.
Hi Bhasoriya,
I have seen all the workbooks but need to have some clarification from you.
In all three workbooks(Resource A, Resource B, and Resource C)you have mentioned that "If resouce has filled his personal sheet even single row or can be thousand, data should be update by date" where you want to construct the code with IF logic.
I am not able to understand your aforementioned statement entirely.
If resource A starts filling the data from A19 onwards(from last available row) and say till 1000 of rows, what you want to be updated by 'date' here?
Can you plz clarify this?
I am sorry if I fail to understand something which you might have already explained clearly.
@Narayan: If you have understood the requirement (needless to say), please share your thoughts.
Kaushik
Hello All, Sorry for inconvenient,
Let me explain.
In continuation of
http://chandoo.org/forums/topic/is-it-possible-to-collect-data-from-other-sheets-to-in-to-one-sheet
(There are 10 people are filling one data sheet individually, At end of the day they are placing them sheet in one common folder(share folder). After that i have to copy all data from individual sheet and have to paste in one MASTER SHEET.
Is there any way to do some automation can collect data from all sheet and paste(place) in MASTER SHEET.)
____________________________________________________________________________
Resource - A has filled his sheet what ever work he has done on 12-oct-11. It is from A4 to A18. On next day (13-Oct-11) he will fill the sheet from A20 to ...(may be A50 depends on work)or (he may be absent, so he will not fill the data) Resource - B & C are also following same process as mention for Resource - A.
There is a sheet named as "Resource Daily report" In this sheet i have to paste all latest date data from Resource - A,B,C. In "Resource Daily report" should be update from latest data from next to last filled.
There is one more sheet as "Resource Daily report". It is macro enable workbook. Can also experiment with it. you can delete the all data from A4 to A48. and press Cntrl+L.
this can collect all data from all Resource sheet. But on next day the old data should leave as it is and new data should be update in "Resource Daily report"
Hope so from this experiment, you can get an idea what i exact want.
kaushik03 & NARAYANK991 thanks a lot for response.
Let me explain.
In continuation of
http://chandoo.org/forums/topic/is-it-possible-to-collect-data-from-other-sheets-to-in-to-one-sheet
(There are 10 people are filling one data sheet individually, At end of the day they are placing them sheet in one common folder(share folder). After that i have to copy all data from individual sheet and have to paste in one MASTER SHEET.
Is there any way to do some automation can collect data from all sheet and paste(place) in MASTER SHEET.)
____________________________________________________________________________
Resource - A has filled his sheet what ever work he has done on 12-oct-11. It is from A4 to A18. On next day (13-Oct-11) he will fill the sheet from A20 to ...(may be A50 depends on work)or (he may be absent, so he will not fill the data) Resource - B & C are also following same process as mention for Resource - A.
There is a sheet named as "Resource Daily report" In this sheet i have to paste all latest date data from Resource - A,B,C. In "Resource Daily report" should be update from latest data from next to last filled.
There is one more sheet as "Resource Daily report". It is macro enable workbook. Can also experiment with it. you can delete the all data from A4 to A48. and press Cntrl+L.
this can collect all data from all Resource sheet. But on next day the old data should leave as it is and new data should be update in "Resource Daily report"
Hope so from this experiment, you can get an idea what i exact want.
kaushik03 & NARAYANK991 thanks a lot for response.
sgmpatnaik
Active Member
Hello Mr. Bhasoriya
I have small doubt that is which data is entered by the 10 peoples about your Resource is they doing work in the same office or you collecting the data from different offices
With Regards
SP
I have small doubt that is which data is entered by the 10 peoples about your Resource is they doing work in the same office or you collecting the data from different offices
With Regards
SP
sgmpatnaik
Active Member
Thank Q Bhasoriya
Thanks for your information i will give some idea with my knowledge tom.
With Regards
SP
Thanks for your information i will give some idea with my knowledge tom.
With Regards
SP
sgmpatnaik
Active Member
Hello Bhasoriya,
Sorry for my late replay
i create one file as per my knowledge, i think sure it will help
in this file i create the user id and passwords after accessing the id and pass one user Form will display in that UserForm you have to enter your data and in that UserForm when you Press the Cancel Button then Another UserForm will display for accessing the Specified Sheets ok
main user Id is - admin and password is pass
for the users id is user1 and Password is pass
password is same for all ids you can change that from DashBoard Sheet
the file link is given below:
https://dl.dropbox.com/u/75654703/For_Basoriya.xlsm
if any mistakes pls inform
With Regards
SP
Sorry for my late replay
i create one file as per my knowledge, i think sure it will help
in this file i create the user id and passwords after accessing the id and pass one user Form will display in that UserForm you have to enter your data and in that UserForm when you Press the Cancel Button then Another UserForm will display for accessing the Specified Sheets ok
main user Id is - admin and password is pass
for the users id is user1 and Password is pass
password is same for all ids you can change that from DashBoard Sheet
the file link is given below:
https://dl.dropbox.com/u/75654703/For_Basoriya.xlsm
if any mistakes pls inform
With Regards
SP
sgmpatnaik
Active Member
i forget to inform you another thing that is share this workbook directly to all your system using with Share WorkBooka nd create shortcut in all desktop systems
Hello sgmpatnaik,
Your efforts are excellent. Salute them, But from last 3 yrs we are using only simple sheet filling technique. Resources are filling them sheet on daily bases and i am merging them data in to one sheet. My concern was only that if i get some code which can merge data on daily bases which sheet is only updated, rest sheet which is not updated then macro will skip them. If you have any idea then pls post it.
Otherwise we have to update with new technique.
Thanks a lot sgmpatnaik, Salute u..
Your efforts are excellent. Salute them, But from last 3 yrs we are using only simple sheet filling technique. Resources are filling them sheet on daily bases and i am merging them data in to one sheet. My concern was only that if i get some code which can merge data on daily bases which sheet is only updated, rest sheet which is not updated then macro will skip them. If you have any idea then pls post it.
Otherwise we have to update with new technique.
Thanks a lot sgmpatnaik, Salute u..
sgmpatnaik
Active Member
Hello Bhasoriya
Thanks for your kind Words
can u upload a sample file with that file easily i can understand i will try to solve the problem ( I can understand easily with that example how can your doing the data to merge)
With Regards
SP
Thanks for your kind Words
can u upload a sample file with that file easily i can understand i will try to solve the problem ( I can understand easily with that example how can your doing the data to merge)
With Regards
SP
sgmpatnaik
Active Member
Hi Bhasoriya
i Think you didn't check my book correctly, As per your request i create that file as a single format you not need to Merge or Copy the ResourceA,B,C, Because when the users are going to start enter the data then the data is automatically insert in your Resource Daily Report (Which is that is a Sheet2) after that you can use the sort option in that sheet as u like
If still it is not solve your problem please inform
With Regards
SP
i Think you didn't check my book correctly, As per your request i create that file as a single format you not need to Merge or Copy the ResourceA,B,C, Because when the users are going to start enter the data then the data is automatically insert in your Resource Daily Report (Which is that is a Sheet2) after that you can use the sort option in that sheet as u like
If still it is not solve your problem please inform
With Regards
SP
Hello sgmpatnaik,
Yes, I have checked your sheet properly, it is unbeatable. but generally practice is Resource are copying old data, paste it after one blank cell, change the date, change the order numbers, batch name and size.. and start to create advrt. after that they will fill the time one by one that how much they spent.
Your efforts are excellent but it is bit time consuming. Your sheet has make me free from data merging, but for Resources it is time consuming. Sorry i am expecting more from you, but could you please check my first uploaded "Resource Daily report.xlsm" I have created macro init. Just check that macro. I am stuck..
Thanks a tone.. sgmpatnaik
Yes, I have checked your sheet properly, it is unbeatable. but generally practice is Resource are copying old data, paste it after one blank cell, change the date, change the order numbers, batch name and size.. and start to create advrt. after that they will fill the time one by one that how much they spent.
Your efforts are excellent but it is bit time consuming. Your sheet has make me free from data merging, but for Resources it is time consuming. Sorry i am expecting more from you, but could you please check my first uploaded "Resource Daily report.xlsm" I have created macro init. Just check that macro. I am stuck..
Thanks a tone.. sgmpatnaik
sgmpatnaik
Active Member
Hello Bhasoriya
at finally i made some changes on your command which was copying the ResourceA,B,C, Sheets. You did a great Job on your file i just little bit change and add some codes
you are going to try the copy and paste option in your file and you don't want to copy the sheet every time and past it in the Resource Daily Report Write, when you are using the Macro Short Cut key then the old data is deleted write
Then Please Try this code and inform us (NOTE TAKE A BACK UP OF YOUR FILE FIRST AND DELETE THE MODULE AND PASTE THE CODE IN WORKBOOK CODE)
Private Sub WorkBook_Open()
Range("A4").Select
ChDir "D:EXCELDATA MERGE"
Workbooks.Open Filename:="D:EXCELDATA MERGEResource - A.xlsx"
ActiveWindow.SmallScroll Down:=-12
Range("A4:N3000").Select
Selection.Copy
ActiveWindow.ActivateNext
ActiveWindow.SmallScroll Down:=-3
ActiveSheet.Paste
Workbooks.Open Filename:="D:EXCELDATA MERGEResource - B.xlsx"
Range("A4:N3000").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ActivateNext
Range("A3001").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=15
Workbooks.Open Filename:="D:EXCELDATA MERGEResource - C.xlsx"
ActiveWindow.SmallScroll Down:=-9
Range("A4:N3000").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ActivateNext
ActiveWindow.SmallScroll Down:=6
Range("A6002").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A4:N10040").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A4:A6018" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A3:N10040")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=27
Range("A3").Select
ActiveWorkbook.SaveAs Filename:= _
"D:EXCELDATA MERGEResource Daily report.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
MsgBox " Data Updated"
End Sub
at finally i made some changes on your command which was copying the ResourceA,B,C, Sheets. You did a great Job on your file i just little bit change and add some codes
you are going to try the copy and paste option in your file and you don't want to copy the sheet every time and past it in the Resource Daily Report Write, when you are using the Macro Short Cut key then the old data is deleted write
Then Please Try this code and inform us (NOTE TAKE A BACK UP OF YOUR FILE FIRST AND DELETE THE MODULE AND PASTE THE CODE IN WORKBOOK CODE)
Private Sub WorkBook_Open()
Range("A4").Select
ChDir "D:EXCELDATA MERGE"
Workbooks.Open Filename:="D:EXCELDATA MERGEResource - A.xlsx"
ActiveWindow.SmallScroll Down:=-12
Range("A4:N3000").Select
Selection.Copy
ActiveWindow.ActivateNext
ActiveWindow.SmallScroll Down:=-3
ActiveSheet.Paste
Workbooks.Open Filename:="D:EXCELDATA MERGEResource - B.xlsx"
Range("A4:N3000").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ActivateNext
Range("A3001").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=15
Workbooks.Open Filename:="D:EXCELDATA MERGEResource - C.xlsx"
ActiveWindow.SmallScroll Down:=-9
Range("A4:N3000").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ActivateNext
ActiveWindow.SmallScroll Down:=6
Range("A6002").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A4:N10040").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A4:A6018" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A3:N10040")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=27
Range("A3").Select
ActiveWorkbook.SaveAs Filename:= _
"D:EXCELDATA MERGEResource Daily report.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
MsgBox " Data Updated"
End Sub