CODEWITHSUNDEEP
vbaexcel
user3762389
user3762389

Reputation: 51

Printing data in a specifc cell in Excel VBA

The following code works fine. However I need to paste the data starting from a specific row in a column. Here as per the code I can paste the data in column 'C' but if I want to start the pasting from 'C5' for instance how do I change the coding? Thanks for the help. 

Private Sub CommandButton1_Click()


    Dim sh As Worksheet
    Dim rData As Range, rCell As Range, rFrom As Range, rTo As Range
    Dim lRightCol As Long
    Dim ass As Integer

    ass = 4

    With ActiveSheet
        lRightCol = .Range("C3").Column
        Set rData = .Range(.Range("A3"), .Range("D" & .Rows.Count).End(xlUp))
        For Each rCell In rData
            Set sh = Nothing
            On Error Resume Next
            Set sh = Worksheets(rCell.Value)
            On Error GoTo 0
            If Not sh Is Nothing Then
                Set rFrom = rCell.Resize(1, lRightCol)
                With sh
                    Set rTo = .Range("C" & .Rows.Count).End(xlUp).Offset(1).Resize(1, lRightCol)
                End With
                rFrom.Copy rTo
            End If
        Next rCell
    End With


End Sub

I tried to change the code to following but didn't work.

 Set rTo = .Range("C5" & .Rows.Count).End(xlUp).Offset(1).Resize(1, lRightCol)

Upvotes: 1

Views: 112

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

As I mentioned in comments. you can use:

Set rTo = .Range("C" & Application.Max(5,.Cells(.Rows.Count,"C").End(xlUp).Row + 1))

Upvotes: 1

Related Questions