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]

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

So is the exact mapping here?
Are we going across rows, then down to the next column,
or down column and then to the next row?

I assume A1 is being matched to A1 on the second sheet, but what is going in B1 from the second sheet? The value from C1 or A2?

 

[Joe4]

Ok, assuming that you are going across (rows) before going down to the next line, try this code:

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
    dst.Cells(rw, "A") = src.Range("A1")
    dst.Cells(rw, "B") = src.Range("C1")
    dst.Cells(rw, "C") = src.Range("E1")
    dst.Cells(rw, "D") = src.Range("H1")
    dst.Cells(rw, "E") = src.Range("A2")
    dst.Cells(rw, "F") = src.Range("C2")
    dst.Cells(rw, "G") = src.Range("E2")
    dst.Cells(rw, "H") = src.Range("H2")
    dst.Cells(rw, "I") = src.Range("A3")
    dst.Cells(rw, "J") = src.Range("C3")
    dst.Cells(rw, "K") = src.Range("E3")
    dst.Cells(rw, "L") = src.Range("H3")
        
    Application.ScreenUpdating = True
    
End Sub

 

[posfog]

Hi All,
The data from the source sheet is all over different cells, so to make it easier i think what i will do is create a single line out of the view for end user that puts the source data in the same order of the destination sheet so for example the source data information will be on line A200 to CH200, This should me removing most of the source range like below do the trick??
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
dst.Cells(rw, "A") = src.Range("A200")


Application.ScreenUpdating = True

End Sub

 

[Joe4]

I am not really sure what you are saying/asking.
What you did will only populate the first cell (column A).

It doesn't really matter where the cells are on the sheet. What matters is whether or not you are copying the same columns each time.
If it is always the same columns, then just modify the code to reflect that, and you should be fine.

VBA is great for automation when there is predictability or consistency. If there is no rhyme or reason around which cells are being copied, I am not sure VBA is going to help much.

 

[posfog]

Hi There Joe4

I will have data on one single line reference A200 to CH200 and would like to be able to special copy values of the data contained in these cells to next available line on another sheet.

This macro will be assigned to a button as this task will be done multiple times hence i need the data to be collected on the second sheet.
Hope that makes sense?

Regards

 

[Joe4]

I will have data on one single line reference A200 to CH200 and would like to be able to special copy values of the data contained in these cells to next available line on another sheet.

Isn't that what we already did?

If the cell values you are copying from changed, that is no big deal. Just change them in the code. All the parts that look like this:
src.Range("A1")
is what you would need to change.

So, if the first cell you are copying from is really A200 and not A1, simply change this line:

dst.Cells(rw, "A") = src.Range("[B]A1[/B]")

to

dst.Cells(rw, "A") = src.Range("[B]A200[/B]")

and repeat for the other cells.

The rest of the code should be able to stay the same.

 

[posfog]

Ho Joe4
Fantastic that works a treat but i wondered if coding is able to paste the values as they are in the source sheet in the same way that you would special paste values only??

Regards

 

[Joe4]

i wondered if coding is able to paste the values as they are in the source sheet in the same way that you would special paste values only??

Not sure what you mean.
The code should just be pasting the values.
What exactly is it that you want it to do that it is not?

 

[posfog]

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?