Copy paste data from 2 sheet to one sheet in same workbook

[lady_alina]

Hi,

I have a 3 sheeter excel, what I want is to copy fixed rows from one sheet named: Inv and copy next row each time I click the button from sheet: Details and paste values to specified cells in sheet: Reg. The code I have at present does that however it pastes the formula and not the values and fixed rows from Customer sheet from where I want next row on each click. Please help me to get the right code. Here is my code given below:

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

 

[JLGWhiz]

See if this does what you want.

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).PasteSpecial xlPasteValues
    ws1.Range("B4").copy 
    ws3.Range("D" & DestRow).PasteSpecial xlPasteValues
    ws1.Range("C4").copy 
    ws3.Range("G" & DestRow).PasteSpecial xlPasteValues
    ws2.Range("B13").copy 
    ws3.Range("N" & DestRow).PasteSpecial xlPasteValues
    ws2.Range("H13").copy 
    ws3.Range("L" & DestRow).PasteSpecial xlPasteValues
    ws2.Range("I28").copy 
    ws3.Range("J" & DestRow).PasteSpecial xlPasteValues
    ws2.Range("H15").copy 
    ws3.Range("K" & DestRow).PasteSpecial xlPasteValues
Application.ScreenUpdating = True
End Sub

 

[lady_alina]

Thanks JLGWhiz it helped in pasting the values, however from sheet "Details" I need next rows and from Sheet "Inv" I need to copy fixed rows each time I click the command button. Can you please help with this as well. I had written this in my original question also but may be I wasn't very clear sorry for that.

 

[JLGWhiz]

Thanks JLGWhiz it helped in pasting the values, however from sheet "Details" I need next rows and from Sheet "Inv" I need to copy fixed rows each time I click the command button. Can you please help with this as well. I had written this in my original question also but may be I wasn't very clear sorry for that.

If you mean that you want to advance the source row each time you click the button, I am not sure that can be done. A procedure could be written so that it would continue in a loop to copy cells from those same columns and increment the row by one each time. But you would need to provide some kind of criteria that could be used to limit the number of times it copies to only what is needed. If you want to copy until the source column is blank, that would be OK. That would be something that can be coded.

 

[lady_alina]

Thanks for your reply and sorry for not making my concept very clear. what I want is to fixed rows for copying in sheet Inv and fixed columns but not rows. Check the code below may be it'll explain you more.

Private Sub CommandButton1_Click()Application.ScreenUpdating = FalseDim 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                                                     ---------------------on next click it should copy A5 then A6
    ws3.Range("A" & DestRow).PasteSpecial xlPasteValues
    ws1.Range("B4").copy                                                    ---------------------on next click it should copy B5 then B6
    ws3.Range("D" & DestRow).PasteSpecial xlPasteValues
    ws1.Range("C4").copy                                                    ---------------------on next click it should copy C5 then C6
    ws3.Range("G" & DestRow).PasteSpecial xlPasteValues
    ws2.Range("B13").copy                                                    ---------------------this should be fixed rows for copying
    ws3.Range("N" & DestRow).PasteSpecial xlPasteValues
    ws2.Range("H13").copy                                                    ---------------------this should be fixed rows for copying
    ws3.Range("L" & DestRow).PasteSpecial xlPasteValues
    ws2.Range("I28").copy                                                    ---------------------this should be fixed rows for copying
    ws3.Range("J" & DestRow).PasteSpecial xlPasteValues
    ws2.Range("H15").copy                                                    ---------------------this should be fixed rows for copying
    ws3.Range("K" & DestRow).PasteSpecial xlPasteValues
Application.ScreenUpdating = True
End Sub

However all should be pasted in the next blank available row in Reg Sheet

 

[lady_alina]

Can anybody reply to the above question and to this new question please, I really need help on this one. Apart from the above question I need one more help:

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer Name[/TD]
[TD]Address[/TD]
[TD]Phone #[/TD]
[TD]Ref#[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]ABC[/TD]
[TD]99999[/TD]
[TD]A/99/A[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]DEF[/TD]
[TD]88888[/TD]
[TD]D/22/E[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]XYZ[/TD]
[TD]77777[/TD]
[TD]R/44/W[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]UVW[/TD]
[TD]66666[/TD]
[TD]E/55/E[/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[lady_alina]

No reply??