Copy and Paste to next empty row

[posfog]

Hi All,
I have a excel sheet with two tabs and want to be able to copy and paste data between the two tabs in the following scenario.


Tab 1 - User has entered numbers on following cells

A1 C1 E1 H1
A2 C2 E2 H2
A3 C3 E3 H3

User presses a but which contains macro which copies the data from Cells as above and pastes them into Tab 2 on the following cells

A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1

Process is repeated again in Tab 1 but cells are now copied to
A2 B2 C2 D2 E2 F2 G2 H2 I2 J2 K2 L2

Process is repeated again in Tab 1 but cells are now copied to
A3 B3 C3 D3 E3 F3 G3 H3 I3 J3 K3 L3

And so on.

Is this possible at all?

Regards

 

[Joe4]

For example i am using the Roundup formula but when that cell in question is copied across its showing a value of 0.5 not 1 as its shown in the source sheet?

It will copy whatever the value is being returned.
I am guessing your issue if with the formatting, not the value.
Check to see how the cell you are copying from is formatted.

 

[Joe4]

If you want to copy both the value and formatting, then we are going to need to change how we copy each value.
I added a little procedure that our main one will call that will do both the values and formatting:

Sub CopyToSheet2()

    Dim src As Worksheet
    Dim dst As Worksheet
    Dim rw As Long
    
    Application.ScreenUpdating = False
    
'   Set source and destination sheets
    Set src = Sheets("Sheet1")
    Set dst = Sheets("Sheet2")
    
'   Find next available row on destination sheet
    rw = dst.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
'   Populate values on destination sheet
    Call sCopy(src.Range("A1"), dst.Cells(rw, "A"))
    Call sCopy(src.Range("C1"), dst.Cells(rw, "B"))
    Call sCopy(src.Range("E1"), dst.Cells(rw, "C"))
    Call sCopy(src.Range("H1"), dst.Cells(rw, "D"))
    Call sCopy(src.Range("A2"), dst.Cells(rw, "E"))
    Call sCopy(src.Range("C2"), dst.Cells(rw, "F"))
    Call sCopy(src.Range("E2"), dst.Cells(rw, "G"))
    Call sCopy(src.Range("H2"), dst.Cells(rw, "H"))
    Call sCopy(src.Range("A3"), dst.Cells(rw, "I"))
    Call sCopy(src.Range("C3"), dst.Cells(rw, "J"))
    Call sCopy(src.Range("E3"), dst.Cells(rw, "K"))
    Call sCopy(src.Range("H3"), dst.Cells(rw, "L"))
        
    Application.ScreenUpdating = True
    
End Sub

Sub sCopy(sRng As Range, dRng As Range)
'   Copy data and formatting from one range to another
'       sRng = source range
'       dRng = destination range

'   Check to make sure that both ranges are exactly one cell
    If sRng.Count > 1 Or dRng.Count > 1 Then
        MsgBox "Both source range and destination range must be one single cell"
        Exit Sub
    End If

'   Copy cell value and formatting from source to destination
    sRng.Copy
    With dRng
        .PasteSpecial xlPasteFormats
        .PasteSpecial xlPasteValues
    End With

End Sub

You shouldn't ever have to change anything in the second procedure, just the first.