CODEWITHSUNDEEP
excelvba
Justrin
Justrin

Reputation: 35

copy row to new worksheet to next available row

How would you copy a row and paste it to the next available row to another worksheet. Currently the code below only pastes according to the entry row of the mastersheet.

Sub ShowMonth()

     Dim k As Long
  
     For k = 2 To 9999

     Cells(k, 14).Value = Month(Cells(k, 1).Value)
     
   
  Next k

End Sub

    Private Sub Move()
        Dim MonthNo As Range
        Dim lastrow, j As Long
        
        Set MonthNo = Worksheets("MasterSheet").Range("N2:N9999")
        lastrow = Worksheets("MasterSheet").Cells(Rows.Count, "A").End(xlUp).Row
    
        For j = 2 To 9999
            If MonthNo(j) = 1 Then
                lastrow = lastrow + 1
                MonthNo(j).Rows.EntireRow.Copy Destination:=Worksheets("Jan").Range("A" & lastrow)
            ElseIf MonthNo(j) = 2 Then
                lastrow = lastrow + 1
                MonthNo(j).Rows.EntireRow.Copy Destination:=Worksheets("Feb").Range("A" & lastrow)    
            End If
        Next
    End Sub

Upvotes: 1

Views: 78

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

I have commented the code so that you should not have a problem understanding the code.

Option Explicit

Private Sub Move()
    Dim wsLrow As Long, lastrow As Long, i As Long
    Dim ws As Worksheet
    Dim wsDest As String
    
    On Error GoTo Whoa
    
    '~~> Set your worksheet
    Set ws = Worksheets("MasterSheet")
    
    With ws
        '~~> Find the last row in Col N
        wsLrow = .Cells(.Rows.Count, "N").End(xlUp).Row
        
        '~~> Loop through the cells in Col N
        For i = 2 To wsLrow
            Select Case .Range("N" & i).Value2
                Case 1: wsDest = "Jan"
                Case 2: wsDest = "Feb"
                Case 3: wsDest = "Mar"
                '
                ' And so on. Add more if applicable
                '
            End Select
            
            If wsDest <> "" Then
                With Worksheets(wsDest)
                    '~~> Find the row in the destination worksheet to copy
                    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                    '~~> Copy the row from MasterSheet to relevant sheet
                    ws.Rows(i).Copy Destination:=.Rows(lastrow)
                End With
                wsDest = ""
            End If
        Next i
    End With
    
LetsContinue:
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Upvotes: 1

Related Questions