VBA to copy all data from the last sheet of one workbook into a specific worksheet of master file

[Leeward904]

Hello all i apologize if this has been asked previously i wasn't having any luck finding the right macro for my need.

As a project manager i have to provide a "Contract Status Report" once a month to my customer and my supervisor fills out a daily production form, a new work sheet is added for each day, and it is always located in the same location for each job. At the end of the month i need to pull the latest daily production form and include it in my "Contract Status Report" and i have my project master file set up to retrieve the labor hours report, material cost report, apply the correct rates to the various cost elements, and the file even uses the labor report to create a "employee roster" for all employees that have worked on the job. The last this thing i need to do is write a macro to have the master file go to a cell referenced location, find the .xls file title "DCR", open it (or not it makes no difference to me), copy all data from from the last worksheet in the file (the supervisor has a bad habit of naming the sheets different names each time), copy the latest daily report to the worksheet in the master file title "Progress Report", and finally close the DCR without saving (unless it didnt open in the first place).

That was really wordy so this might make it easier:
1. Macro assigned to "button" on master file- Click
2. Master file goes to cell referenced (on master file worksheet "Data" Range("A27")) location to locate file titled "DCR.xls"
3. Find the last worksheet in DCR file and copies all data
4. paste DCR worksheet data to worksheet in master file titled "Progress Report" (preferably keeping the DCR file formatting)
5. close without saving DCR file

I am new to VBA and any help is greatly appreciated!!!

 

[ManiacB]

Here is a possible solution

Sub upd()
Dim DCSWB As Workbook, Mas As Workbook
Dim path As String, File As String, pathAndFileName As String
Dim cnt As Long

Set Mas = ThisWorkbook
path = Sheets("Data").[A27].Value
File = "DCR.xlsx"
pathAndFileName = path & "\" & File
Set DCSWB = Application.Workbooks.Open(pathAndFileName, ReadOnly:=True)
cnt = DCSWB.Sheets.Count
DCSWB.Sheets(cnt).UsedRange.Copy
Mas.Sheets("Progress Report").Paste
DCSWB.Close False
End Sub

 

[Leeward904]

It worked!!!

Thank you for the help this will save a massive amount of time!

Is there a way to add a piece that clears the existing data from "Progress Report" and then paste the new "DCR" data? That way i do not have to manually clear the worksheet prior to importing the new data?

 

[ManiacB]

Add this line after Set Mas = Thisworkbook

Mas.Sheets("Progress Report").UsedRange.Delete

 

[Leeward904]

Eureka!

you are a beast! thank you for your help!!

 

[VanSummers]

Add this line after Set Mas = Thisworkbook

Mas.Sheets("Progress Report").UsedRange.Delete

I'm working on a similar problem for my project. I've created a logbook in Excel where every day has its own sheet. I'm trying to create a Macro that will allow users to hit a button at the top of the sheet and it will generate a new sheet with the same format as the preceding one. I'd like the macro to automatically correctly update the date in the sheet (and ideally the tab would be renamed to match that date). I need some information to carry over from the previous day (like parts on order, for example) but other information to be cleared so that new entries can be made.

How can I code the VBA to make this work?