copy problem VBA

[plost33]

I ahve the following code that is erroring on my ".copy" line fo code. does anyone knwo why?

Sub CompleteForms()
Dim Vendor As String
Dim CurrentRow As String
Dim CurrentColumn As String
Dim PasteRangeName As String
Dim XXX As String
Dim Edate As String
    i = Sheets("data").Range("A2").End(xlDown).Row
    
     For Each cell In Sheets("data").Range("A2:R" & i)
        On Error Resume Next
        
        
        CurrentRow = Row
        Vendor = Sheets("Data").Range(A & CurrentRow)
   
        Sheets("Template").Copy After:=Sheets(2)
        Sheets("Template (2)").Name = Vendor
    
        CurrentColumn = Column
        PasteRangeName = Sheets("data").Range(CurrentColumn & "1").Value
        
        .Copy
        Sheets(Vendor).Range(PasteRangeName).PasteSpecial xlValue
        
        Edate = Sheets(Vendor).Range(Edate).Value
        XXX = Format(Edate, "[$-409]ddmmmyyyy;@")
        
        
        
    Next
    
     Folder = "C:\Users\tphythian\Documents\Vendor Evaluations"
     ThisWorkbook.Worksheets(Vendor).Copy
        With ActiveSheet.UsedRange
            .Value = .Value
        End With
        With ActiveWorkbook
            .SaveAs Folder & "\" & Vendor & " - " & XXX & ".xlsx"
            .Close
        End With
         
    
    Sheets(Vendor).Delete
    
   
End Sub

 

[plost33]

i am getting an error application-defined or object-defined error.

 

[Andrew Poulsom]

I hadn't spotted:

Dim CurrentColumn As String

That should be:

Dim CurrentColumn As Long

 

[plost33]

alright, so i got one more problem, as i see it...

Sub CompleteForms()
Dim Vendor As String
Dim Vendor1 As String
Dim CurrentRow As String
Dim CurrentColumn As Long
Dim PasteRangeName As String
Dim XXX As String
Dim Edate As String
    i = Sheets("data").Range("A2").End(xlDown).Row
    
     For Each cell In Sheets("data").Range("A2:R" & i)
        'On Error Resume Next
        
        Sheets("Template").Copy After:=Sheets(2)
        CurrentRow = cell.Row
        Vendor1 = Sheets("Data").Range("B" & CurrentRow)
        Vendor = Left(Vendor1, 12)
        Sheets("Template (2)").Name = Vendor
        
        CurrentColumn = cell.Column
        PasteRangeName = Sheets("data").Cells(1, CurrentColumn).Value
        
        cell.Copy
        Sheets(Vendor).Range(PasteRangeName).PasteSpecial xlValue
        
        
        If CurrentColumn = 18 Then
        
            Edate = Sheets(Vendor).Range(Edate).Value
            XXX = Format(Edate, "[$-409]ddmmmyyyy;@")
            
            Folder = "C:\Users\tphythian\Documents\Vendor Evaluations"
            ThisWorkbook.Worksheets(Vendor).Copy
            
                With ActiveSheet.UsedRange
                    .Value = .Value
                End With
                
                With ActiveWorkbook
                    .SaveAs Folder & "\" & Vendor & " - " & XXX & ".xlsx"
                    .Close
                End With
                
            Sheets(Vendor).Delete
                
       Else
       
    Next
        
       
End Sub

i am getting an error saying: "Next without For". i do have a for...is there a problem with my if statement?

 

[Andrew Poulsom]

Actually (despite what VBA says) you are missing an End If before Next.

 

[plost33]

andrew, i am getting ask if i want to delete each sheet when the following line of code runs:

Sheets(Vendor).Delete


how do i get that to just delete the sheet without asking me?

 

[Andrew Poulsom]

Try, before that line:

Application.DisplayAlerts = False