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

 

[Andrew Poulsom]

You have an unqualified reference (nothing before the dot). Should it be?

cell.Copy

 

[plost33]

what i am trying to do is copy the current cell that the loop has selected.

What the entire code is trying to do is take data within a sheet named "data" and populate a form, sheet named "Template". Each row on "data" will populate the "form". the form then is saved in the folder specified in the code. then the sheet is deleted. and the code should restart on the next row on "Data".

 

[Andrew Poulsom]

Did you try what I suggested?

 

[plost33]

Yes . thanks Andrew. that did get the code to run without error. however the code is not working as desired. the "currentrow= row" is not working. I think i maybe have my loop incorrect. I am trying to have it start on cell A2 and copy that data and paste in into the newly added sheet. the value it copies from A2 should be pasted into the new sheet into a named range which is the same as the column heading, value in A1. after pasting that value in the new sheet it should then move to B2..then C2...ect..thru R2. then the code should save the sheet in the folder specified then delete the sheet. Then finally go to Row 3, A3 and go through R3..then delete the sheet and move on to Row 4...ect..all the way through row "i".

 

[Andrew Poulsom]

I expect you need cell.Column and cell.Row.

 

[plost33]

okay i think i about have it working. is there any way to change the following two lines of code:

Vendor1 = Sheets("Data").Range("B" & CurrentRow)
Vendor = Left(Vendor1, 20)



what i want to do is change Vendor to a formula that will return the Vendor name from the left until the last character. for some reason my vendor names have alot of spaces after their last character, and that is erroring because it makes some of the names longer than 3q characters. I am hoping there is a way to write the formual to say from left to the last chracter excluding spaces....perhaps i need to use "right" instead of left?

 

[Andrew Poulsom]

You can use the Trim function. Example:

Sub Test()
    Dim Vendor1 As String
    Vendor1 = "Some Vendor           "
    Vendor1 = Trim(Vendor1)
    MsgBox Vendor1 & " now has " & Len(Vendor1) & " characters."
End Sub

 

[plost33]

okay here is what i have so far:

Sub CompleteForms()
Dim Vendor As String
Dim Vendor1 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
        
        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").Range("R1C" & CurrentColumn).Value
        
        cell.Copy
        Sheets(Vendor).Range(PasteRangeName).PasteSpecial xlValue
        
    Next
        
        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
        
   
End Sub

the first part that is not working is the PasteRangeName...it is not assigning a value to the variable. Say if CurrentColumn is 1 then it should assign the value in cell A1 or R1C1 to to the variable PasteRangeName...it is not working. do you see the problem?

 

[Andrew Poulsom]

You can use the Cells property:

PasteRangeName = Sheets("data").Cells(1, CurrentColumn).Value