[Solved] Copy data from different files and past to master file based on date

Hello,

I have 2 files. Master & Data
BOth sheets have certain data based on daily date. What i need is,

When ever i open Master file, it checks last date data filled in master sheet, and fill the data in Master file taking from Data file upto last record.

Hope, i will get solution. Files are attached for reference.

Regards,
 
Hello,

I have 2 files. Master & Data
BOth sheets have certain data based on daily date. What i need is,

When ever i open Master file, it checks last date data filled in master sheet, and fill the data in Master file taking from Data file upto last record.

Hope, i will get solution. Files are attached for reference.

Regards,
Greetings,

First of all, thanks a lot for your query in this forum. Now coming to your question, it is difficult to respond to your problem without looking at your dataset. Although you mentioned that you already added the file, I can't see any file attached to your post from our point of view. It will be best to add or attach your file with your comment. So, we can help you out in this matter.
 
Dear,

I am trying to write my problem again in details;

1. Master File (Where data has to collected based on dates)
2. Production Sheet (From this sheet data has to collect)
3. R-301 (From this sheet data has to collect)
4. R-302 (From this sheet data has to collect)

Details:
"Master Sheet" Workbook has "Plant Status" worksheet which will collect data from all the other workbooks based on date.

Master Sheet.Plant Status.B = Production Report.ProductionSheet.G
Master Sheet.Plant Status.C = Production Report.ProductionSheet.F

Same as for R-301& R-302 Files...

If it is not easy to understand, just share me any example to collect selective data from more than 1 file and combine in 1 file based on date...

Regards,
Faisal
 

Attachments

  • Master Sheet.xlsm
    38.6 KB · Views: 2
  • Production Report.xlsx
    46.7 KB · Views: 3
  • R-301.xlsm
    295.1 KB · Views: 2
  • R-302.xlsm
    705.7 KB · Views: 2
Greetings mfaisal,
I think I understood your problem, and i thereby give you the solution file given below. This file will will check the last date in the A column and search for the data according to this date in other files that you have given and fill them accordingly. However there something need to address,
1. some of the data in the master sheet could not be traced in the 301 and 302 files. For example in the R-301 file i could not locate the size column data in the r-302 file,
1679399506252.png
I could not find the M/C(%) and the SA(%) values. In the place of SA(%), i placed the value of SFE,
1679399492910.png
Moreover, I found you R-301 and R-302 files are protected by password, which created a lot of issues. if you intend to receive more support from us, you need to unprotect your sheet or provide password. other than this, solution provide will be difficult for us.
Finally, we found only one date that actually present in all of the 3 source files(02/14/2023). So I placed this date in the sheet manually and then ran the code to test.all of the codes are saved in the workbook.
Last but not the least, you need to change the file location address in the code, if there is any confusion, let me know.
Hopefully this code came in handy for you.
 

Attachments

  • Master Sheet_original.xlsm
    46.7 KB · Views: 4
Last edited:
Dear,

I am really sorry for the incontinence caused by me.
Lets make it little simple, lets first try to get data only from 1 file. I am attaching the files
1. master file
2. Production.

Master file will get data from prduction file based on date difference which may be 1 day or more.

I tried to manage in copy the data but it is not pasting.

Can you please check

regards,


1679485907534.png
 

Attachments

  • Master Sheet.xlsm
    35.2 KB · Views: 4
  • Production.xlsx
    46.7 KB · Views: 3
Greetings mFaisal,
Thanks a lot for the follow up. I actuall given my solution based on your previous comment. Which will extract the information from the source files based on the date in the target sheeet. Now what i understood that you want range of data or all of the data available in the source sheet to be pasted in the target sheet. Like you have 11 march in your target sheet, and have data tlll 16 march in the source sheet. So basically you want data from 12 march to 16 march to be copied to the target sheet, am i right mFaisal.
Please let me know if my assumption, then I would work on your workbook.

Thanks and Regards,
Rubayed Razib
 
Hi Dears,

I need your support on following:-



I need to have a counter of days in Excel sheet, for example if text in A1 is “Normal” than the counter in B1 should start counting number of days based on date change and cell B1 should show number of days passed. If text in A1 is another text than “Normal” counter should stop and cell B1 should show “0” till the text is changed back to “Normal” in A1. If text is again changed to “Normal” in A1 counter shall resume counting from “0”.

1679902701320.png

Looking forward to get your reply soon.



Regards
 
Hi Dears,

I need your support on following:-



I need to have a counter of days in Excel sheet, for example if text in A1 is “Normal” than the counter in B1 should start counting number of days based on date change and cell B1 should show number of days passed. If text in A1 is another text than “Normal” counter should stop and cell B1 should show “0” till the text is changed back to “Normal” in A1. If text is again changed to “Normal” in A1 counter shall resume counting from “0”.

View attachment 153

Looking forward to get your reply soon.



Regards
Greetings,
I think I understand your problem,Below I am going to present a sample code which will help your cause.You have to place the code in the worksheet, not in the module(shown in the image below).
First, right click on the sheet1 name in the workbook as shown in the image.
And then click on the View Code.
1679927282249.png
Then in the code editor window, paste the following code as shown in the image.
1679927422434.png
Code:
Public carry As Date
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim start_date As Date
    Dim day_counter As Integer
    If Target.Address = "$A$1" Then
    If UCase(Target.Value) = "NORMAL" Then
carry = Date
Debug.Print carry
Else
 ActiveSheet.Cells(1, 2).Value = "0"
    End If
    End If
End Sub

Private Sub Worksheet_Activate()
If ActiveSheet.Cells(1, 1).Value = "Normal" Then
If Month(carry) = Month(Date) And Year(carry) = Year(Date) And Day(carry) = Day(Date) Then
  ActiveSheet.Cells(1, 2).Value = 0
  Debug.Print "Success"
 Else
 ActiveSheet.Cells(1, 2).Value = DateDiff("d", carry, current_date)
 dday = DateDiff("d", carry, current_date)
 Debug.Print dday
End If
End If
End Sub
Now how you use the code is crucial. There is subtle change of how you describe the problem. In this code, if you enter Normal in cell A1, then the code will start counting, and after couple of days when you open the worksheet again(say 10 day later), you will see the 10 in cell B1.
1679928242951.png
Now if you switch back to other word in the cell B1, then you will see the counter in cell B1 now set back to 0.
1679928270061.png
Now if you set it back to Normal, the counter will set to 0 again.Here one think need to be consider, you gotta select sheet2 and again swithc back to Sheet 1 in order to see the updated value. The coding is done is such a way that if you want to see updated value, entering value is not enogh, you need to reopen the Sheet1 sheet to see the change in counter. This is done in order to maintain dynamicity of the code.If you have nay confusion of this, feel free to comment, I will try my best.
1679928412444.png
Moreover, it will be best to add a button to execute the code instead of dynamically updating. So let me know if you can consider changing your requirements a bit, in that way it will be easy for you to understand and implement the code.
If you have any problem, let me know.
 

Attachments

  • Sample_Counter.xlsm
    17.1 KB · Views: 1
Greetings mFaisal,
Thanks a lot for the follow up. I actuall given my solution based on your previous comment. Which will extract the information from the source files based on the date in the target sheeet. Now what i understood that you want range of data or all of the data available in the source sheet to be pasted in the target sheet. Like you have 11 march in your target sheet, and have data tlll 16 march in the source sheet. So basically you want data from 12 march to 16 march to be copied to the target sheet, am i right mFaisal.
Please let me know if my assumption, then I would work on your workbook.

Thanks and Regards,
Rubayed Razib
Dear,
Yes u r right? i need the range of date to be copied; it may be 1 day or more. the file which i shared, it is working when i put the code in workbook Open trigger but if i want to run it manually by click button or direct from vb; it gives the error.

I hope, its clear.

regards,
 
Greetings,
Below I have given the code that will execute when you open your master file. and upon opening the file, it will take values from the porduction sheet file.
Before opening the file, the worksheet will look like this, Data till 3/11/23.
1679998201719.png
But we have data till 3/14/2023.
1679998316218.png
After we open the file, the information will be present in B and C column.
1679998423967.png
You can follow the following GIF to have an idea.
ezgif-4-169f6b4a25.gif

Another thing you need to take care of is that you need to change the file locations as shown in the code image below,Means the location of the master sheet and the production file.
1679999700726.png
Let me know if you have any issue.I also attached my two sheets, use them properly.
 

Attachments

  • Copy of Production Report1.xlsx
    46.9 KB · Views: 0
  • Master Sheet_original.xlsm
    48.5 KB · Views: 1

Online statistics

Members online
0
Guests online
3
Total visitors
3

Forum statistics

Threads
287
Messages
1,240
Members
508
Latest member
HaroldDyeme
Top