Open a file, paste data, save as

dannybland

New Member
Joined
Sep 12, 2014
Messages
31
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


Code:
    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 :)
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

dannybland

New Member
Joined
Sep 12, 2014
Messages
31
I've changed the code slightly to be more consistent

Code:
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

New Member
Joined
Sep 12, 2014
Messages
31
In interest of sharing - figured it out

I used

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

to get the date to feed correctly.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,843
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top