• Ask a Question
150
Insert Image Size must be less than < 5MB.
    Ask a Question
    Cancel
    150
    More answer You can create 5 answer(s).
      Ask a Poll
      Cancel
      Top Contributor

      I want to copy selected cells and rows to another sheet in a row with VBA

      I have made a Bill format on which I want to bill items to customers. After billing I want to copy Invoice number,date,customer address and row of items filled to another sheet  for data compilation .

      I am attaching the excel sheet on which you can find a buttom “new invoice” to clear the current bill and add new customer details.

      My question  is how can i copy Invoice number,date,customer address and row of items filled to another sheet in a single row.  Each bill to be updated on the bottom of the previous data.

       

      Plesae help

      Regards

      Krishnan

       

      Asked by Krishnan on February 9, 2018 in VBA: Macros.
      24 Answers
      Expert

      Hi , here is one way you can move data from sheet 1 to the bottom row in sheet 2.

      RE: I want to copy selected cells and rows to another sheet in a row  with VBA

       

      Sub MoveToSheet2()
      Dim ws1 As Worksheet
      Dim ws2 As Worksheet
      Dim rng As Range
      Set ws1 = Worksheets(“Sheet1”)
      Set ws2 = Worksheets(“Sheet2”)
      Set rng = ws1.Range(“A2:F2”)
      Application.ScreenUpdating = False
      rng.Copy Destination:=ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
      rng.ClearContents
      End Sub

      Answered by Hyside2 on February 9, 2018..
      Expert

      Hi Krishnan,  Please try this.

      RE: I want to copy selected cells and rows to another sheet in a row  with VBA

      Sub Move2Sheet2()
      Dim ws1 As Worksheet
      Dim ws2 As Worksheet
      Dim adr As Range
      Dim WH As Range
      Dim x As Range
      Dim rw As Long
      Dim rng As Range
      Dim i As Integer

      Set ws1 = Worksheets(“Sheet1”)
      Set ws2 = Worksheets(“Sheet2”)
      Set adr = ws1.[C1]
      Set WH = ws1.[B2]
      Set x = ws1.Range(“I:I”)
      rw = Application.WorksheetFunction.CountA(x) + 2
      Set rng = ws1.Range(“A4:I” & rw)

      Application.ScreenUpdating = False
      ws2.Activate
      adr.Copy Destination:=ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
      WH.Copy Destination:=ws2.Cells(Rows.Count, 2).End(xlUp).Offset(1)
      rng.Copy Destination:=ws2.Cells(Rows.Count, 5).End(xlUp).Offset(1, -2)
      Cells(Rows.Count, 1).End(xlUp).Select
      If ActiveCell.Offset(1, 4) = “” Then GoTo Line1
      ws2.Cells(Rows.Count, 5).End(xlUp).Offset(0, -4).Select

      For i = 1 To 2
      Range(Selection, Selection.End(xlUp)).FillDown
      ActiveCell.Offset(0, 1).Select
      Next i
      Line1:
      ws1.Activate
      adr.ClearContents
      WH.ClearContents
      rng.ClearContents
      [C1].Select
      End Sub

       

      Answered by Hyside2 on February 10, 2018..
      Top Contributor

      Thank for the reply . But he real problem is different cells and row are to be arranged in a row in sheet 2

      I am giving the model of the invoice with highted in yellow the fields to be copiedin sheet2 and next invoice to be updated on bottom of first Invoice

      Please help

      Kuttikkattukara women’s Associa 32AAACG1395D1Z3
      Vadakkal Estate, Pathalam Eloor Diwans Road,Ernakulam 682016 GST NO : 32AAACG1395D1Z3
      Kochi 683504
      Customer address shipping Addres O C  No 3 New Invoice

      OC Date 10-02-2018
      Select W/H ZYFB1S
      Discription Qty BASIC PRICE DISCOUNT RATE % TAXABLE AMT GST % CGST SGST Amount
      #N/A #N/A #N/A #N/A 0.00 0 0
      #N/A #N/A #N/A 0
      #N/A #N/A #N/A 0
      #N/A #N/A #N/A 0
      #N/A #N/A #N/A 0
      #N/A #N/A #N/A 0
      #N/A #N/A #N/A 0
      #N/A #N/A #N/A 0
      TOTAL Amt 0.00
      Mode of Payment Cash Advance
      Balance 0.00
      Decleartion Received in as and where condition For Godrej & Boyce Mfg Co Ltd
      Certified that all the particulars shown i the above Tax Invoice are true and correct in all respects and the goods on whi
      charged and collected are in accordance with the provision of KVAT ACT 2003 and the rules made thereunder. It is als
      my/our Registration under KVAT Act 2003 is not subject to any suspension /cancellation and it is valid as on the date Customer signature Authorised Signatory
      Answered by Krishnan on February 10, 2018..
      Top Contributor

      Sorry to reply lately. Thanks for the info. But i have few more doubts

      When I tried the above, Following  list only coming

      the address is not coming. Is it a merged cell ?

      Please give me your email id so that I will forward the excel

      Regards

      Krishnan

      3 14-02-2018 30161803SD00025 #N/A #N/A #N/A #N/A 12038.28 -72% -2519.64 -2519.64 6999.00
      30161803SD00181 #N/A #N/A #N/A #N/A 5040.00 28% 980 980 7000.00
      30161803SD00248 #N/A #N/A #N/A #N/A 1440.00 28% 280 280 2000.00
      30161803SD00172 #N/A #N/A #N/A 0
      Answered by Krishnan on February 14, 2018..
      • Hi Krishnan

        Cell C1 (Address) is not merged.   There are no merged cells in sheet1 or sheet2. Data comes from sheet1 and is copied to sheet2 as shown below by the arrows.

        RE: I want to copy selected cells and rows to another sheet in a row  with VBA

        on February 23, 2018.
      Cancel
      Add comment
      Top Contributor

      Thank u very much sir. But I have some more issues as follows.

      First time when i run the macro following listing has come without Date and W/H. But on second time is appears on line number column.

      I have entered first four lines, but it took 5 lines (one with blank). and on second time macro it is replaced with next set of records

      You may also see that description coulmn is auto field retirves value using vlookup. But it is showing as n/a

      I give below the macro. Please help to find out exactly where is the problem

      Regards

      Krishnan

      Sub Macro2()

      Dim ws1 As Worksheet
      Dim ws2 As Worksheet
      Dim oc As Range
      Dim ocd As Range
      Dim adr As Range
      Dim WH As Range
      Dim x As Range
      Dim rw As Long
      Dim rng As Range
      Dim i As Integer
      Set ws1 = Worksheets(“zyfb1s bill”)
      Set ws2 = Worksheets(“Sheet2”)
      Set oc = ws1.[invbno]
      Set adr = ws1.[adr]
      Set ocd = ws1.[ocdt]
      Set WH = ws1.[B9]
      Set x = ws1.Range(“k:k”)
      rw = Application.WorksheetFunction.CountA(x) + 5
      Set rng = ws1.Range(“a11:k” & rw)
      Application.ScreenUpdating = False
      ws2.Activate
      adr.Copy Destination:=ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
      oc.Copy Destination:=ws2.Cells(Rows.Count, 2).End(xlUp).Offset(1)
      ocd.Copy Destination:=ws2.Cells(Rows.Count, 3).End(xlUp).Offset(1)
      WH.Copy Destination:=ws2.Cells(Rows.Count, 4).End(xlUp).Offset(1)
      rng.Copy Destination:=ws2.Cells(Rows.Count, 5).End(xlUp).Offset(1, -2)
      Cells(Rows.Count, 1).End(xlUp).Select
      If ActiveCell.Offset(1, 11) = “” Then GoTo Line1
      ws2.Cells(Rows.Count, 5).End(xlUp).Offset(0, -4).Select
      For i = 1 To 2
      Range(Selection, Selection.End(xlUp)).FillDown
      ActiveCell.Offset(0, 1).Select
      Next i
      Line1:
      ws1.Activate
      ‘adr.ClearContents
      ‘WH.ClearContents
      ‘rng.ClearContents
      [C1].Select
      End Sub
          Discription Qty BASIC PRICE DISCOUNT RATE % TAXABLE AMT GST % CGST SGST Amount
      Godrej & Boyce Mfg co Ltd, Malankara building, Palayam Trivandrum 3 1 30161803SD00024 #N/A #N/A #N/A #N/A 14400.00 28% 2800 2800 20000.00
      Godrej & Boyce Mfg co Ltd, Malankara building, Palayam Trivandrum 3 2 239-STK001-STSMIRROR07 #N/A 1 #N/A #N/A 72.00 28% 14 14 100.00
      Godrej & Boyce Mfg co Ltd, Malankara building, Palayam Trivandrum 3 3 30161803SD00171 #N/A #N/A #N/A #N/A 5040.00 28% 980 980 7000.00
      Godrej & Boyce Mfg co Ltd, Malankara building, Palayam Trivandrum
      3
      4 30161803SD00176 #N/A #N/A #N/A #N/A 720.00 28% 140 140 1000.00
      Godrej & Boyce Mfg co Ltd, Malankara building, Palayam Trivandrum 3 28-02-2018 ZYFB1S #N/A #N/A #N/A #N/A 0.00 0 0
      Godrej & Boyce Mfg co Ltd, Malankara building, Palayam Trivandrum 3 1 30161803SD00024 #N/A #N/A #N/A #N/A 14400.00 28% 2800 2800 20000.00
      Godrej & Boyce Mfg co Ltd, Malankara building, Palayam Trivandrum 3 2 239-STK001-STSMIRROR07 #N/A 1 #N/A #N/A 72.00 28% 14 14 100.00
      Godrej & Boyce Mfg co Ltd, Malankara building, Palayam Trivandrum 3 3 30161803SD00171 #N/A #N/A #N/A #N/A 5040.00 28% 980 980 7000.00
      Godrej & Boyce Mfg co Ltd, Malankara building, Palayam Trivandrum
      3
      4 30161803SD00176 #N/A #N/A #N/A #N/A 720.00 28% 140 140 1000.00
      Godrej & Boyce Mfg co Ltd, Malankara building, Palayam Trivandrum 3   Scrap items – 394.4437 0 0 #DIV/0! 0.00 0 0
      Answered by Krishnan on February 28, 2018..
      Cancel
      Add comment
      Top Contributor

      RE: I want to copy selected cells and rows to another sheet in a row  with VBA

      Thanks for your kind reply.

      I have uploaded the image of the main sheet and result sheets.

      The problem still exists, like on more row is copy on first time macro is run. Also on second time  Rows are not correctly captured.

       

      Please help

      Sub Macro2()

      Dim ws1 As Worksheet
      Dim ws2 As Worksheet
      Dim oc As Range
      Dim ocd As Range
      Dim adr As Range
      Dim WH As Range
      Dim x As Range
      Dim rw As Long
      Dim rng As Range
      Dim i As Integer
      Set ws1 = Worksheets(“zyfb1s bill”)
      Set ws2 = Worksheets(“Sheet2”)
      Set oc = ws1.[k7]
      Set adr = ws1.[c7]
      Set ocd = ws1.[k8]
      Set WH = ws1.[B9]
      Set x = ws1.Range(“k:k”)
      rw = Application.WorksheetFunction.CountA(x) + 5
      Set rng = ws1.Range(“a11:k” & rw)
      Application.ScreenUpdating = False
      ws2.Activate
      adr.Copy Destination:=ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
      oc.Copy Destination:=ws2.Cells(Rows.Count, 2).End(xlUp).Offset(1)
      ocd.Copy Destination:=ws2.Cells(Rows.Count, 3).End(xlUp).Offset(1)
      WH.Copy Destination:=ws2.Cells(Rows.Count, 4).End(xlUp).Offset(1)
      rng.Copy Destination:=ws2.Cells(Rows.Count, 7).End(xlUp).Offset(1, -2)
      Cells(Rows.Count, 1).End(xlUp).Select
      If ActiveCell.Offset(1, 11) = “” Then GoTo Line1
      ws2.Cells(Rows.Count, 7).End(xlUp).Offset(0, -4).Select
      For i = 1 To 2
      Range(Selection, Selection.End(xlUp)).FillDown
      ActiveCell.Offset(0, 1).Select
      Next i
      Line1:
      ws1.Activate
      ‘adr.ClearContents
      ‘WH.ClearContents
      ‘rng.ClearContents
      [C1].Select

      End Sub

              ORDER CUM CHALLAN        
      Godrej & Boyce Mfg Co Ltd Godrej & Boyce Mfg Co Ltd
      Kuttikkattukara women’s Association Hall (Exhibition Hall) 32AAACG1395D1Z3
      Vadakkal Estate, Pathalam Eloor Diwans Road,Ernakulam 682016 GST NO : 32AAACG1395D1Z3
      Kochi 683504
        Godrej & Boyce Mfg Co Ltd Malankara Building complex,Palayam, trivandrum     O C No 3
                Date 3/8/2018
      Select W/H ZYFB1S    
          Discription Qty BASIC PRICE DISCOUNT RATE % TAXABLE AMT GST % CGST SGST Amount
      238-STK001-FUDEHRXXBLKSXXXX Scrap items – Dezire Head Rest Black Cushion 1 10000 100.00 7200.00 28% 1400 1400 10000.00
      30161803SD00172 Scrap items – SLINE 2DR LKR+DRWR Bd R Ivy 1 9545 94.29 7380.00 18% 810 810 9000.00
        #N/A #N/A #N/A 0.00 0 0
        #N/A #N/A #N/A 0
        #N/A #N/A #N/A 0
        #N/A #N/A #N/A 0
        #N/A #N/A #N/A 0
        #N/A #N/A #N/A 0
        TOTAL Amt 19000.00
      Mode of Payment Cash         Advance  
              Balance 19000.00
      Decleartion Received in as and where condition For Godrej & Boyce Mfg Co Ltd
      Certified that all the particulars shown i the above Tax Invoice are true and correct in all respects and the goods on which the Tax
      charged and collected are in accordance with the provision of KVAT ACT 2003 and the rules made thereunder. It is also Certified that
      my/our Registration under KVAT Act 2003 is not subject to any suspension /cancellation and it is valid as on the date of this bill Customer signature Authorised Signatory
              ORDER CUM CHALLAN        
      Godrej & Boyce Mfg Co Ltd Godrej & Boyce Mfg Co Ltd
      Kuttikkattukara women’s Association Hall (Exhibition Hall) 32AAACG1395D1Z3
      Vadakkal Estate, Pathalam Eloor Diwans Road,Ernakulam 682016 GST NO : 32AAACG1395D1Z3
      Kochi 683504
        Godrej & Boyce Mfg Co Ltd Malankara Building complex,Palayam, trivandrum     O C No 3
                Date 3/8/2018
      Select W/H ZYFB1S    
          Discription Qty BASIC PRICE DISCOUNT RATE % TAXABLE AMT GST % CGST SGST Amount
      238-STK001-FUDEHRXXBLKSXXXX Scrap items – Dezire Head Rest Black Cushion 1 10000 100.00 7200.00 28% 1400 1400 10000.00
      30161803SD00172 Scrap items – SLINE 2DR LKR+DRWR Bd R Ivy 1 9545 94.29 7380.00 18% 810 810 9000.00
        #N/A #N/A #N/A 0.00 0 0
        #N/A #N/A #N/A 0
        #N/A #N/A #N/A 0
        #N/A #N/A #N/A 0
        #N/A #N/A #N/A 0
        #N/A #N/A #N/A 0
        TOTAL Amt 19000.00
      Mode of Payment Cash         Advance  
              Balance 19000.00
      Decleartion Received in as and where condition For Godrej & Boyce Mfg Co Ltd
      Certified that all the particulars shown i the above Tax Invoice are true and correct in all respects and the goods on which the Tax
      charged and collected are in accordance with the provision of KVAT ACT 2003 and the rules made thereunder. It is also Certified that
      my/our Registration under KVAT Act 2003 is not subject to any suspension /cancellation and it is valid as on the date of this bill Customer signature Authorised Signatory
      Answered by Krishnan on March 8, 2018..
      Expert

      I have changed the KEY column from “I” to “C” (Description) All items must have a description for the macro to move the data from sheet1 to sheet2.  If this does not work. change “C” in Set x = ws1.Range(“C:C”) to a column that always has data to the last row.

      Sub Move2Sheet2()

      Dim ws1 As Worksheet
      Dim ws2 As Worksheet
      Dim adr As Range
      Dim WH As Range
      Dim x As Range
      Dim rw As Long
      Dim rng As Range
      Dim i As Integer
      Set ws1 = Worksheets("Sheet1")
      Set ws2 = Worksheets("Sheet2")
      Set adr = ws1.[C1]
      Set WH = ws1.[B2]
      Set x = ws1.Range("C:C") ' this is the only line I changed
      rw = Application.WorksheetFunction.CountA(x) + 2
      Set rng = ws1.Range("A4:I" & rw)
      Application.ScreenUpdating = False
      ws2.Activate
      adr.Copy Destination:=ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
      WH.Copy Destination:=ws2.Cells(Rows.Count, 2).End(xlUp).Offset(1)
      rng.Copy Destination:=ws2.Cells(Rows.Count, 5).End(xlUp).Offset(1, -2)
      Cells(Rows.Count, 1).End(xlUp).Select
      If ActiveCell.Offset(1, 4) = "" Then GoTo Line1
      ws2.Cells(Rows.Count, 5).End(xlUp).Offset(0, -4).Select
      For i = 1 To 2
      Range(Selection, Selection.End(xlUp)).FillDown
      ActiveCell.Offset(0, 1).Select
      Next i
      Line1:
      ws1.Activate
      adr.ClearContents
      WH.ClearContents
      rng.ClearContents
      [C1].Select
      

      End Sub

      Answered by Hyside2 on March 8, 2018..
      Top Contributor

      Thanks for the reply sir

      I am attaching the sheet 2 on which I tried 3 entries. As you may see that first time  All data  comes correctly. but address  and oc number date are not repeating for subsequent rows.

      Also you can see the test2 address is coming on row 2 of sheet2. but data are appending correctly

      Please help to correct the loop filling all  rows with address and oc numbers. Also why second set is o f records are overlapping first one

      Regards Krishnan

       

      Giving below he  the macro used

      Sub Macro2()

      Dim ws1 As Worksheet
      Dim ws2 As Worksheet
      Dim oc As Range
      Dim ocd As Range
      Dim adr As Range
      Dim WH As Range
      Dim x As Range
      Dim rw As Long
      Dim rng As Range
      Dim mp As Range
      Dim i As Integer
      Set ws1 = Worksheets(“zyfb1s bill”)
      Set ws2 = Worksheets(“Sheet2”)
      Set oc = ws1.[k7]
      Set adr = ws1.[c7]
      Set ocd = ws1.[k8]
      Set WH = ws1.[B9]
      Set mp = ws1.[d20]
      Set x = ws1.Range(“k:k”)
      rw = Application.WorksheetFunction.CountA(x) + 4
      Set rng = ws1.Range(“A11:k” & rw)
      Application.ScreenUpdating = False
      ws2.Activate
      adr.Copy Destination:=ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
      oc.Copy Destination:=ws2.Cells(Rows.Count, 2).End(xlUp).Offset(1)
      ocd.Copy Destination:=ws2.Cells(Rows.Count, 3).End(xlUp).Offset(1)
      WH.Copy Destination:=ws2.Cells(Rows.Count, 4).End(xlUp).Offset(1)
      rng.Copy Destination:=ws2.Cells(Rows.Count, 7).End(xlUp).Offset(1, -2)
      Cells(Rows.Count, 1).End(xlUp).Select
      If ActiveCell.Offset(1, 11) = “” Then GoTo Line1
      ws2.Cells(Rows.Count, 11).End(xlUp).Offset(0, -1).Select
      For i = 1 To 2
      Range(Selection, Selection.End(xlUp)).FillDown
      ActiveCell.Offset(0, 1).Select
      Next i
      Line1:
      ws1.Activate
      adr.ClearContents
      ‘WH.ClearContents
      ‘rng.ClearContents
      [C1].Select

      End Sub

       

       

       

      RE: I want to copy selected cells and rows to another sheet in a row  with VBA

       

      Answered by Krishnan on March 11, 2018..
      Expert

      Hello Krishnan, I hope this helps.

      RE: I want to copy selected cells and rows to another sheet in a row  with VBA

      Sub Macro3()
      Dim ws1 As Worksheet
      Dim ws2 As Worksheet
      Dim adr As Range
      Dim WH As Range
      Dim GST As Range
      Dim OC As Range
      Dim DT As Range
      Dim con As Range
      Set ws1 = Worksheets(“zyfb1s bill”)
      Set ws2 = Worksheets(“Sheet2”)
      Set con = ws1.[A11:K18]
      Set adr = ws1.[C7]
      Set WH = ws1.[B9]
      Set GST = ws1.[K5]
      Set OC = ws1.[K7]
      Set DT = ws1.[K8]
      Application.ScreenUpdating = False
      ws2.Activate
      con.Copy Destination:=ws2.Cells(Rows.Count, 8).End(xlUp).Offset(1, -2)
      [a1].Select
      adr.Copy Destination:=ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
      ActiveCell.Offset(0, 7).End(xlDown).Offset(0, -7).Select
      Range(Selection, Selection.End(xlUp)).Select
      Selection.FillDown
      [a1].Select
      WH.Copy Destination:=ws2.Cells(Rows.Count, 2).End(xlUp).Offset(1)
      ActiveCell.Offset(0, 7).End(xlDown).Offset(0, -6).Select
      Range(Selection, Selection.End(xlUp)).Select
      Selection.FillDown
      [a1].Select
      GST.Copy Destination:=ws2.Cells(Rows.Count, 3).End(xlUp).Offset(1)
      ActiveCell.Offset(0, 7).End(xlDown).Offset(0, -5).Select
      Range(Selection, Selection.End(xlUp)).Select
      Selection.FillDown
      [a1].Select
      OC.Copy Destination:=ws2.Cells(Rows.Count, 4).End(xlUp).Offset(1)
      ActiveCell.Offset(0, 7).End(xlDown).Offset(0, -4).Select
      Range(Selection, Selection.End(xlUp)).Select
      Selection.FillDown
      [a1].Select
      DT.Copy Destination:=ws2.Cells(Rows.Count, 5).End(xlUp).Offset(1)
      ActiveCell.Offset(0, 7).End(xlDown).Offset(0, -3).Select
      Range(Selection, Selection.End(xlUp)).Select
      Selection.FillDown
      [a1].Select

      Range(“A:P”).Select
      Selection.columns.AutoFit
      Selection.ClearFormats

      Range(“A:A”).Select
      With Selection
      .ColumnWidth = 25
      .HorizontalAlignment = xlGeneral
      .VerticalAlignment = xlBottom
      .WrapText = True
      .Orientation = 0
      .AddIndent = False
      .IndentLevel = 0
      .ShrinkToFit = False
      .ReadingOrder = xlContext
      .MergeCells = False
      End With
      Range(“E:E”).Select
      Selection.NumberFormat = “m/d/yyyy”
      Application.CutCopyMode = False
      [a1].Select
      ws1.Activate
      [a1].Select
      End Sub

      Answered by Hyside2 on March 11, 2018..
      Top Contributor

      Dear Sir

      Thanks for the help. but the result I have got is same. First five columns are not getting filled

      If you can forward the excel u have used to my  rkntvm@gmail.com It will be of great help

       

      Regards

      Krishnan

      RE: I want to copy selected cells and rows to another sheet in a row  with VBA

       

      Answered by Krishnan on March 13, 2018..
      Top Contributor

      I have managed to get the details. but the values are not getting pasted due to v lookup function on the cells

      Please correct the lines for filling sheet2 with values and not copying cells with formula

      Sub Macro2()

      Dim ws1 As Worksheet
      Dim ws2 As Worksheet
      Dim oc As Range
      Dim ocd As Range
      Dim adr As Range
      Dim WH As Range
      Dim x As Range
      Dim rw As Long
      Dim rng As Range
      Dim fin As Range
      Dim i As Integer
      Set ws1 = Worksheets(“zyfb1s bill”)
      Set ws2 = Worksheets(“Sheet2”)
      Set oc = ws1.[invbno]
      Set adr = ws1.[adr]
      Set ocd = ws1.[ocdt]
      Set WH = ws1.[B9]
      Set fin = ws1.[d19]
      Set x = ws1.Range(“k:k”)
      rw = Application.WorksheetFunction.CountA(x) + 4
      Set rng = ws1.Range(“a11:k” & rw)
      Application.ScreenUpdating = False
      ws2.Activate
      adr.Copy Destination:=ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
      oc.Copy Destination:=ws2.Cells(Rows.Count, 2).End(xlUp).Offset(1)
      ocd.Copy Destination:=ws2.Cells(Rows.Count, 3).End(xlUp).Offset(1)
      WH.Copy Destination:=ws2.Cells(Rows.Count, 4).End(xlUp).Offset(1)
      rng.Copy Destination:=ws2.Cells(Rows.Count, 5).End(xlUp).Offset(1, -2)
      Cells(Rows.Count, 1).End(xlUp).Select
      If ActiveCell.Offset(0, 11) = “” Then GoTo Line1
      ws2.Cells(Rows.Count, 5).End(xlUp).Offset(0, -4).Select
      For i = 1 To 2
      Range(Selection, Selection.End(xlUp)).FillDown
      ActiveCell.Offset(0, 1).Select
      Next i
      Line1:
      ws1.Activate
      ‘adr.ClearContents
      ‘WH.ClearContents
      ‘rng.ClearContents
      [C1].Select
      End Sub

      RE: I want to copy selected cells and rows to another sheet in a row  with VBA

      Answered by Krishnan on March 22, 2018..
      Top Contributor

      RE: I want to copy selected cells and rows to another sheet in a row  with VBA

      Answered by Krishnan on March 22, 2018..