Open a file, paste data, save as

[dannybland]

Hi,


I need to copy some data every month, paste it into a spreadsheet and then save it as a new file with updated date

    Sub InvoiceBackup()


    Sheets("ASM001").Select
    
    Range("A1048576").End(xlUp).Select
    If ActiveCell.Row = 5 Then Exit Sub
    
    Range( _
        ActiveCell.End(xlUp).Offset(1, 14), _
        ActiveCell.Offset(0, 0)).Select
   
    Selection.Copy
    'opening workbook
    Workbooks.Open Filename:="H:\Finance\CBF\Invoices\Monthly Invoicing Summary\" & Year(Date) & "\ASM\" & (Now(mm) - 1) & Now(yy) & " ASM CBF Reg Summary.xlsx"
    
    Range("A5").Select
    Range( _
        ActiveCell.End(xlDown).Offset(1, 14), _
        ActiveCell.Offset(0, 0)).Select
    Selection.Delete
    Range("A6").Select
    Selection.Paste
    
    
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "TODAY()"
    






    'below code for saving and closing the workbook
    Workbooks("H:\Finance\CBF\Invoices\Monthly Invoicing Summary\" & Year(Date) & "\ASM\" & Now(mm) & Now(yy) & " ASM CBF Reg Summary.xlsx").Activate
    ActiveWorkbook.Save
    ActiveWorkbook.Close
        
        
    End Sub

The date is in the form mmyy but we will be opening last months file, deleting data from A6 down, and then pasting in new data, updating B3 and saving as this months date.

I can't get the date to work, please help

 

[dannybland]

I've changed the code slightly to be more consistent

Sub Test()


    
    Dim wksht As Worksheet
    Dim rng As Range


    Set wksht = Sheets("Birse CH")
    Set rng = wksht.Range("A5")
    If Not IsEmpty(rng.Offset(1, 0)) Then
        Set rng = Range(rng.End(xlDown).Offset(0, 14), rng.Offset(1, 0))
    Selection.Copy
    
    
    End If
End Sub

And hopefully have the copying to a new file still within the IF statement so that if there is no data, nothing happens.

 

[dannybland]

In interest of sharing - figured it out

I used

Format(DateSerial(Year(Now), Month(Now) - 1, Day(Now)), "mmyy")

to get the date to feed correctly.