0

Struggle with this code below.

What I am trying to achieve is I have a purchase order form which when one is generated I would like to be able to copy certain cells to a purchase order log on a different sheet.

Currently I have this code

Sub Range_PasteSpecial_Values1()

    Worksheets("Sheet1").Range("A1").Copy
    Worksheets("Sheet2").Range("A10").PasteSpecial Paste:=xlPasteValues

    Worksheets("Sheet1").Range("B1").Copy
    Worksheets("Sheet2").Range("B10").PasteSpecial Paste:=xlPasteValues

    Worksheets("Sheet1").Range("C1").Copy
    Worksheets("Sheet2").Range("C10").PasteSpecial Paste:=xlPasteValues

    Worksheets("Sheet1").Range("D1").Copy
    Worksheets("Sheet2").Range("D10").PasteSpecial Paste:=xlPasteValues

    Worksheets("Sheet1").Range("E1").Copy
    Worksheets("Sheet2").Range("E10").PasteSpecial Paste:=xlPasteValues

    Worksheets("Sheet1").Range("F1").Copy
    Worksheets("Sheet2").Range("F10").PasteSpecial Paste:=xlPasteValues

End Sub

Which works but it does copy these cells into the row below in my purchase orders log.

Hope this is clear and thanks for your help

Public Sub CopyRows() 
    Sheets("Sheet1").Select 
    ' Find the last row of data 
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row 
    ' Loop through each row 
    For x = 2 To FinalRow 
        ' Decide if to copy based on column D 
        ThisValue = Cells(x, 4).Value 
        If ThisValue = "A" Then 
            Cells(x, 1).Resize(1, 33).Copy 
            Sheets("SheetA").Select 
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 
            Cells(NextRow, 1).Select 
            ActiveSheet.Paste 
            Sheets("Sheet1").Select 
        ElseIf ThisValue = "B" Then 
            Cells(x, 1).Resize(1, 33).Copy 
            Sheets("SheetB").Select 
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 
            Cells(NextRow, 1).Select 
            ActiveSheet.Paste 
            Sheets("Sheet1").Select 
        End If 
    Next x 
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • 1
    your post is not clear, what are you trying to achieve ? can you post a screen-shot of the before and after ? – Shai Rado Sep 16 '19 at 17:47
  • 1
    You should try to [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Miles Fett Sep 16 '19 at 17:50

0 Answers0