Copy spreadsheet when closing workbook to another closed workbook

[mahhogany]

Hi

I have two questions

I want to set up this process for auditing purposes.

I have a workbook 'Tracker' which contains a sheet called ' Tracker work' is linked to a data source and is refreshed each time it opens.

What I would like to do is to take a copy of that sheet and paste values it into another closed workbook (Tracker audit). I would like each sheet in that workbook to saved with the name and time it was pasted there.

When each sheet is saved I don't want it to overwrite what has been pasted there previously. If possible I would like the 'Tracker audit' workbook to to just retain the last 10 pasted sheets.

Is it possible to do this in VBA?

The second problem is similar but this time I only want to focus on a a row of cells. Again each time the Tracker workbook is closed I want to take a copy of that row and paste it into another area of the workbook, retaining the last 10 pastings.

Your help is much appreciated.

 

[John_w]

This should cover the first part of your request. Put the code in the ThisWorkbook module in the 'Tracker' workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        
    'Open 'Tracker audit' workbook (in same folder as Tracker workbook)
    With ThisWorkbook
        Workbooks.Open .Path & "\Tracker audit" & Mid(.Name, InStrRev(.Name, "."))
    End With
    
    With ActiveWorkbook
    
        'Add new sheet as first sheet to Tracker audit and name as current date and time
        .Sheets.Add before:=.Sheets(1)
        .Sheets(1).Name = Format(Now, "DD-MM-YYYY HHMMSS")
    
        'Copy cells from 'Tracker work' sheet and paste special to new sheet
        ThisWorkbook.Sheets("Tracker work").Cells.Copy
        .Sheets(1).Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        
        'Delete last sheet in Tracker audit workbook if it has more than 10 sheets
        If .Sheets.Count > 10 Then
            Application.DisplayAlerts = False       'Suppress delete sheets warning
            .Sheets(.Sheets.Count).Delete
            Application.DisplayAlerts = True
        End If
        
        'Save Tracker audit workbook
        .Close savechanges:=True
        
    End With
    
End Sub

The second problem is similar but this time I only want to focus on a a row of cells. Again each time the Tracker workbook is closed I want to take a copy of that row and paste it into another area of the workbook, retaining the last 10 pastings.

Please be more specific. Which row in which sheet do you want to copy and where do you want to copy it to (sheet, cell/row/column)? Should the copied row be pasted at the top or bottom of the area?

 

[mahhogany]

This should cover the first part of your request. Put the code in the ThisWorkbook module in the 'Tracker' workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        
    'Open 'Tracker audit' workbook (in same folder as Tracker workbook)
    With ThisWorkbook
        Workbooks.Open .Path & "\Tracker audit" & Mid(.Name, InStrRev(.Name, "."))
    End With
    
    With ActiveWorkbook
    
        'Add new sheet as first sheet to Tracker audit and name as current date and time
        .Sheets.Add before:=.Sheets(1)
        .Sheets(1).Name = Format(Now, "DD-MM-YYYY HHMMSS")
    
        'Copy cells from 'Tracker work' sheet and paste special to new sheet
        ThisWorkbook.Sheets("Tracker work").Cells.Copy
        .Sheets(1).Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        
        'Delete last sheet in Tracker audit workbook if it has more than 10 sheets
        If .Sheets.Count > 10 Then
            Application.DisplayAlerts = False       'Suppress delete sheets warning
            .Sheets(.Sheets.Count).Delete
            Application.DisplayAlerts = True
        End If
        
        'Save Tracker audit workbook
        .Close savechanges:=True
        
    End With
    
End Sub

Please be more specific. Which row in which sheet do you want to copy and where do you want to copy it to (sheet, cell/row/column)? Should the copied row be pasted at the top or bottom of the area?

Hi first of all, many thanks for the solution. I will try it once I get back to the office on Monday.

The second question is

I have rows B2:B8 in sheet 'Tracker work'

OnOpen or OnClose I want the information in these cells (B2:B8) to be copy and pasted automatically into Cells D2:B8 of the same sheet. Again retaining just the last 10 pastings.