Copy specific rows to other sheet

[LadyAlina]

Hi,

Sorry if I'm asking a super easy question. From sheet Details I need to copy next rows each time I click like for example - A5,B5,C5 to the next empty row in destination sheet. From Invoice I want to copy fixed rows in next avilable rows in the destination sheet. Below is the code that I have however it copies the fixed rows from Details sheet. Also it should paste values instead of formulae. Please help with this one.

Private Sub CommandButton1_Click()Application.ScreenUpdating = False
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim DestRow As Long
    Set ws1 = Sheets("Details")
    Set ws2 = Sheets("Inv")
    Set ws3 = Sheets("Reg")
    DestRow = ws3.Cells(Rows.Count, "A").End(xlUp).Row + 1
    ws1.Range("A4").copy ws3.Range("A" & DestRow)
    ws1.Range("B4").copy ws3.Range("D" & DestRow)
    ws1.Range("C4").copy ws3.Range("G" & DestRow)
    ws2.Range("B13").copy ws3.Range("N" & DestRow)
    ws2.Range("H13").copy ws3.Range("L" & DestRow)
    ws2.Range("I28").copy ws3.Range("J" & DestRow)
    ws2.Range("H15").copy ws3.Range("K" & DestRow)
Application.ScreenUpdating = True
End Sub

[Luuk]

to paste valuess change:

ws1.Range("A4").Copy ws3.Range("A" & DestRow)

to:

ws1.Range("A4").Copy 
ws3.Range("A" & DestRow).PasteSpecial xlPasteValues

[LadyAlina]

Thanks Luuk, could you also answer two of my questions please

1. See below

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
    Dim DestRow As Long
    On Error Resume Next
    Set ws1 = Sheets("Customer")
    Set ws2 = Sheets("Invoice")
    Set ws3 = Sheets("Inv_Register_Commissions")
    Set ws4 = Sheets("Inv")
    DestRow = ws3.Cells(Rows.Count, "A").End(xlUp).Row + 1
    DestRow = ws4.Cells(Rows.Count, "A").End(xlUp).Row + 1
    ws1.Range("A4").copy ------------------- I want to copy next row each time I click like A5, then A6
    ws3.Range("A" & DestRow).PasteSpecial xlPasteValues
    ws1.Range("B4").copy ------------------- I want to copy next row each time I click like B5, then B6
    ws3.Range("D" & DestRow).PasteSpecial xlPasteValues
    ws1.Range("C4").copy ------------------- I want to copy next row each time I click like C5, then C6
    ws3.Range("G" & DestRow).PasteSpecial xlPasteValues
    ws2.Range("B13").copy ------------------- However this is fixed row that i want to copy
    ws3.Range("N" & DestRow).PasteSpecial xlPasteValues
    ws2.Range("H13").copy ------------------- However this is fixed row that i want to copy
    ws3.Range("L" & DestRow).PasteSpecial xlPasteValues
    ws2.Range("I28").copy ------------------- However this is fixed row that i want to copy
    ws3.Range("J" & DestRow).PasteSpecial xlPasteValues
    ws2.Range("H15").copy ------------------- However this is fixed row that i want to copy
    ws3.Range("K" & DestRow).PasteSpecial xlPasteValues
    ws2.Range("B13").copy ------------------- However this is fixed row that i want to copy
 Application.ScreenUpdating = True
End Sub

2. From sheet "Inv" I want to copy specific rows and paste in specific rows based on one cell value. For example: If cell value = D/22/E then A2 will be pasted in H13 "DestSheet" , B2 will be pasted in G4 "DestSheet" and C2 will be pasted in D33 "DestSheet" . See the table below

Customer Name Address Phone # Ref#
A ABC 99999 A/99/A
B DEF 88888 D/22/E
C XYZ 77777 R/44/W
D UVW 66666 E/55/E


I know this can be done using the above formula but I don't know how to put the criteria of cell value.


Hope to get an answer soon.

[Luuk]

If you have an Excel file with this:

  a    b    c
   1    2    3
   4    5    6
   7    8    9

(Cell "A2" contains: 1; Cell "C4" contains 9 )

Than the output of this formula:
Range("B1").End (XlDown).Row is 4
Because, if you start on "B1", go down to the End, you will be on row 4.

In your code:
Cells(rows.Coun t,"B").End(xlUp ).Row
This starts at cell "B1048576" (because Rows.count=1048 576) end goes up to the first cell which has content ("B4"), and returns its rownumber.

I hope this helps with your problem ;-)

[LadyAlina]

Hi Luuk,

Thanks for the help but I'm so sorry I'm very new to vba coding and didn't really get to which question did you reply. Could you please answer me?

[Luuk]

Sorry for my "super easy answer" to your "super easy question"

I thought you could invest time in trying to solve the problem with some more effort from your site.

I don't have the time, to give more details, because:
I don't understand (fully) your problem (besides not knowing how to code in vba)

[LadyAlina]

Thanks for your quick reply... I already solved my problem. Its' no use answering if you didn't really understand the problem. Thanks anyway...

[twinnyfo]

LadyAlina,

Would you be willing to post your solution, just so others searching this forum might benefit from your efforts?