VBA script problem - Copy and Paste Special data from one sheet to another.

Dear all,

I would like to ask you for your assistance with the script I was given by another member of this forum. Long story short, I have a simple Excel database where I keep the records of all the sales made by our salespeople. In order to make the process or entering details about each sale made I have developed a simple UserForm which opens by pressing command button on the main Excel sheet i.e. "Data".

Here's a print screen of the actual UserForm:

User_form_Example.jpg

Sorry for the language, it is Russian, but it gives you some idea of what I am talking about.


Anyway, I have a problem with one VBA script which is meant to allocate all the data from the main worksheet "Data" to other 5 worksheets (which are named after our sales people), so that by clicking on a particular worksheet I would have a clear idea how many and what type of products that particular sales person has sold.

Here's the actual script:

Sub CopyPaste()

Dim i As Long, LR As Long, NR As Long, nome As String, sh As Worksheet, ws As Worksheet
Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
    With ws
        If .Name <> "Data" And .Name <> "Statistika" Then
        .Range("C2:AG" & Rows.Count).ClearContents
        End If
    End With
    
Next ws

    With Sheets("Data")
        LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
        
        For i = 2 To LR
            If Trim(.Range("G" & i).Value) <> "" Then
                nome = Trim(.Range("G" & i).Value)
                If Not Evaluate("ISREF('" & nome & "'!C1)") Then
                Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = nome
                End If
                .Rows(i).Copy
                Worksheets(nome).Range("C" & Rows.Count).End(xlUp)(2).PasteSpecial (xlPasteValuesAndNumberFormats)
            End If
        Next i
    Application.CutCopyMode = 0
    
    End With
    
        For Each sh In ThisWorkbook.Worksheets
            Application.DisplayAlerts = 0
            If InStr((sh.Name), "Sheet") > 0 Then sh.Delete
            sh.Columns("C:AG").EntireColumn.AutoFit
            
        Next
        
    Application.ScreenUpdating = True
    Application.DisplayAlerts = 1

End Sub

If I leave it as it is and run the script, it automatically says that there is a problem with the following line of code:

 Worksheets(nome).Range("C" & Rows.Count).End(xlUp)(2).PasteSpecial (xlPasteValuesAndNumberFormats)

However, if I change this line of code to the following (as shown below) it seems to work but to a certain extent.

 Worksheets(nome).Range("[bold]A[/bold]" & Rows.Count).End(xlUp)(2).PasteSpecial (xlPasteValuesAndNumberFormats)

The main problem is that whenever I run the script it automatically pastes the data (which has been entered though the UserForm) on top of the heading in a particular worksheet (depending on the name of the sales person which has been chosen in the UserForm), which is represented in Column G.


I am very sorry for the vague description of the problem, but I sincerely hope that it makes some sense and that it will allow you to help me out with this problem.


Thank you very much in advance. Any suggestion will be highly appreciated.


Sincerely,

Aleksandr K.