Consolidation of workbooks in the same file to a single sheet in a master

Thanks Pike, I think I have found what I need. Just need to customise, which I think is not a friday job! Brain dead from 2 weeks of designing, developing and training users....

Still batteling on. The 4 options don't seem to quite do it. Very frustrating! All I need it to do is to go to each file, select a certain sheet and copy 1 line to be pasted into a master sheet in the consolodation spreadsheet to create a list that will be totaled on the bottom line of the sheet. I must be missing something somewhere!

Hi Pike, I have it almost working with one tiny problem, that defeats the whole purpose of the spreadheets! What I need it to do is to find the next row without data and select the cell in columnA and paste the values. Currently it just pastes in row 2 (I know it will be something to do with the "Range ("A2:A" & LR):

Application.ScreenUpdating = False
Sheets("Form accS2").Select
Range("B2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Date = Range("B2").Value
PO = Range("B9").Value


Sheets("Form accS2").saveas Filename:="C:\Documents and Settings\Julie Anderson\My Documents\Dropbox\Beecham\Audi Central Coast WIP\Automation\" & Year(Now) & "-" & Month(Now) & "-" & Day(Now) & "_" & PO & ".xls"


Application.ScreenUpdating = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1

Sheets("Purchases").Select
Range("A2:I2").Copy

Workbooks.Open ("Draft Daily Tracking Spreadsheet.xlsm")
Sheets("purchases").Select
LR = Cells.Find("*", Cells(Rows.Count, Columns.Count), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A2:A" & LR).EntireRow.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Workbooks("Draft Daily Tracking Spreadsheet.xlsm").Save

Very pleased to say I have finally solved my problem. Just had a total blank about it, and it was a fairly easy fix in the end (I hope this can help others out there). Both files need to be in the same folder:

Sub dailyreport()
Application.ScreenUpdating = False
Sheets("Form accS2").Select
Range("B2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Date = Range("B2").Value
PO = Range("B9").Value


Sheets("Form accS2").saveas Filename:="C:\(File Path)" & Year(Now) & "-" & Month(Now) & "-" & Day(Now) & "_" & PO & ".xls"


Application.ScreenUpdating = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1

Sheets("Purchases").Select
Range("A2:I2").Copy

Workbooks.Open ("Draft Daily Tracking Spreadsheet.xlsm")
Sheets("purchases").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("Draft Daily Tracking Spreadsheet.xlsm").Save

End Sub