VBA save as error & VBA Paste in UPPER CASE

[tcorcoran15]

I have created the following VBA macro in excel. The macro works fine until I get to the save as section at the end.

I have used the same code in other excel spreadsheet and its saves perfectly, although I get the following error message when I try to do it within this spreadsheet:

"Compile error:

Wrong number of arguments or invalid property assignment" It makes reference to the word format.

Is it something it doesn't like about the macro above?

Also, whilst I'm here I may as well get this completed haha.

Does anybody know how to paste as UPPER CASE within the VBA code. I have noted below which section I would like this to occur.

Any help is greatly appreciated.

Sub Format()
'
' Format Macro
'
    Sheets("Data Dump").Select
    Dim N As Long
        N = Cells(1, 1).End(xlDown).Row
        Range("D1:D" & N).Select
    Selection.Copy
    Sheets("Customers").Select
    Range("A1:B1").Select
    ActiveSheet.Paste
    
    ActiveSheet.Range("$A$1:$A$10000").RemoveDuplicates Columns:=1, Header:=xlNo
    ActiveSheet.Range("$B$1:$B$10000").RemoveDuplicates Columns:=1, Header:=xlNo
    
     'With ThisWorkbook
        '.Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Run"
    'End With
    
    With Sheets("Customers")
        Set Rng = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        For Each R In Rng
            'Code to follow
            
    Sheets("Customers").Select
        Set customer = .Range("B2")
   
    
    Sheets("Data Dump").Select
    ActiveSheet.Range("$A$1:$BU$15001").AutoFilter Field:=4, Criteria1:=customer
    
    Range("A1:BU500").Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste 'This is where I would like to paste as UPPER CASE
    
     Set wh = Worksheets(ActiveSheet.Name)
            ActiveSheet.Name = wh.Range("Q2").Value
            
    Sheets("Customers").Select
    Range("B2").Delete
    
    Next R
    
    Cells.Select
    Selection.Delete Shift:=xlUp
    
    
    Sheets("Data Dump").Select
    Selection.AutoFilter
    
    
        
    End With
    
    Application.DisplayAlerts = False
    Sheets("Data Dump").Delete
    Sheets("Customers").Delete
    Application.DisplayAlerts = True


    End Sub
    
    Sub SaveAs()


        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs ("X:\Company\File\File - Live\Reports\Credit Control Files\Contracts File " & Format(Now(), "DD-MMM-YYYY hh mm") & ".xlsx")
        Application.DisplayAlerts = True
        
    End Sub

 

[footoo]

For the UPPER CASE bit :

ActiveSheet.Paste 'This is where I would like to paste as UPPER CASE
[A1:BU500] = [INDEX(UPPER(A1:BU500),)]

 

[tcorcoran15]

That's great thanks for your help!

 

[footoo]

For the SAVEAS bit, try :

ActiveWorkbook.SaveAs "X:\Company\File\File - Live\Reports\Credit Control Files\Contracts File " & Format(Now(), "DD-MMM-YYYY hh mm") & ".xlsx", FileFormat:=51

 

[tcorcoran15]

The error has appeared again, really doesn't like the format part. When I put the same save as code in other spreadsheets it works perfectly which makes me believe it doesn't like something in the macro above.

Is there a way to disable the macro above during that stage of the VBA?

 

[footoo]

Name the macro something other than Format.
(Your save macro is calling the Format macro.)

 

[tcorcoran15]

Now I feel stupid haha! Thanks a lot for your help!