CODEWITHSUNDEEP
vbaexcel
Neil Capper
Neil Capper

Reputation: 49

Copy a user selected row to the next blank row on a new worksheet

I have a workbook with 2 worksheets. sheet 1 is open and sheet 2 is closed cases

i am trying to copy the values from a user selected row and paste these into the next blank row on the closed sheet.

I have a VBA which does this but I think it can be reduced, but dont know how to do it.

My current code is as follows. DRow is a calculated calue and works fine, Irow is gotten from a message box and again works fine

    Worksheets("Closed").Cells(DRow, 2).Value = Worksheets("Open").Cells(iRow, 2).Value
    Worksheets("Closed").Cells(DRow, 3).Value = Worksheets("Open").Cells(iRow, 3).Value
    Worksheets("Closed").Cells(DRow, 4).Value = Worksheets("Open").Cells(iRow, 4).Value
    Worksheets("Closed").Cells(DRow, 5).Value = Worksheets("Open").Cells(iRow, 5).Value
    Worksheets("Closed").Cells(DRow, 6).Value = Worksheets("Open").Cells(iRow, 6).Value
    Worksheets("Closed").Cells(DRow, 7).Value = Worksheets("Open").Cells(iRow, 7).Value
    Worksheets("Closed").Cells(DRow, 8).Value = Worksheets("Open").Cells(iRow, 8).Value
    Worksheets("Closed").Cells(DRow, 9).Value = Worksheets("Open").Cells(iRow, 9).Value

Is there a way to condense this into one or two lines

Upvotes: 2

Views: 61

Answers (1)

R3uK
R3uK

Reputation: 14547

Simply use .Resize :

Worksheets("Closed").Cells(DRow, 2).Resize(1,7).Value = _
    Worksheets("Open").Cells(iRow, 2).Resize(1,7).Value

Upvotes: 4

Related Questions