CODEWITHSUNDEEP
vbaexcel
C Jones
C Jones

Reputation: 135

excel macro code is skipping every 2nd line

the below code is to take an employee name, (Column A) andput the range ("A:J") of that row into a new sheet of that employee, if they dont have a sheet, then make one and ad the heading. However, it is skipping every second line, which is causing the line that it is scanning the name on, and the line it is copying from to be different (ie:Employees are going in the wrong sheets, and only 1/2 are getting moved) Any help would be great

Set rngEmpSales = wsSales.Range("A2", wsSales.Range("A" & Rows.Count).End(xlUp).Address)
Dim wsSales As Worksheet, wsDesSales As Worksheet
Set wsSales = ThisWorkbook.Sheets("Sales")
Dim SalesCount as Range
    For Each SalesCount In rngEmpSales
                On Error Resume Next
                Set wsDesSales = ThisWorkbook.Sheets(Trim(SalesCount.Value))
                On Error GoTo 0
                If wsDesSales Is Nothing Then
                   Set wsDesSales = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
                    wsDesSales.Name = SalesCount.Value
                End If
                SalesCount(1 - (SalesCount.Row - 1)).Range("A1:J1").Copy wsDesSales.Range("K2")
                SalesCount.Range("A" & SalesCount.Row & ":J" & SalesCount.Row).Copy wsDesSales.Range("K" & Rows.Count).End(xlUp).Offset(1, 0)
                Set wsDesSales = Nothing
            End If
    Next SalesCount

Upvotes: 2

Views: 610

Answers (2)

sam092
sam092

Reputation: 1335

You should use

wssales.Range("A" & SalesCount.Row & ":J" & SalesCount.Row) instead of SalesCount.Range("A" & SalesCount.Row & ":J" & SalesCount.Row)

and

wssales.Range("A1:J1").Copy instead of SalesCount(1 - (SalesCount.Row - 1)).Range("A1:J1").Copy

The reason is SalesCount itself is a range, when you apply another .Range after it, it will take the relative position.

e.g. Range("A2").Range("A1:J1") becomes Range("A2:J2") and Range("B2").Range("B2:K2") becomes Range("B2:K2")

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149297

Is this what you are trying? (UNTESTED)

Sub Sample()
    Dim wsSales As Worksheet, wsDesSales As Worksheet
    Dim rngEmpSales As Range, SalesCount As Range
    Dim shName As String
    Dim lRow As Long, i As Long

    Set wsSales = ThisWorkbook.Sheets("Sales")

    With wsSales
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        Set rngEmpSales = .Range("A2:A" & lRow)

        For i = 2 To lRow
            shName = Trim(.Range("A" & i).Value)
            On Error Resume Next
            Set wsDesSales = ThisWorkbook.Sheets(shName)
            On Error GoTo 0

            If wsDesSales Is Nothing Then
                Set wsDesSales = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
                wsDesSales.Name = shName
            End If

            .Range("A1:J1").Copy wsDesSales.Range("K2")
            .Range("A" & i & ":J" & i).Copy wsDesSales.Range("K" & _
                                            wsDesSales.Rows.Count).End(xlUp).Offset(1, 0)

            Set wsDesSales = Nothing
        Next i
    End With
End Sub

Upvotes: 3

Related Questions