Thread: Copy and Paste non-contiguo data to another sheet

I need some help on how to make a storage button.

So basically, theres a sheet in the spreadsheet called "Invoice" and it has all the details like Costs, Customer ID, Product ID"

I need this information so when you press the button it gets automatically entered into a "invoice database" page so in a row it has like Customer ID, Product ID, Costs and it gets put under the columns it falls under.

You could do this with a Macro, but the tricky thing is making it so when you press the button again it goes onto the next row. Otherwise it will just keep over-writing the first row.

So making it when you press the button it, writes to the row. Then if you press again, it writes the row below it.

Anyone know how to do that? If you wish to speak on MSN just pm me

Thanks!

Check out this code, change the range to meet your requirements

Sub CopyCode()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim s As Range, z As Range, c As Range
    Dim x As Integer, y As Integer

    Set ws1 = Worksheets("Sheet1")    'determins sheet to copy from
    Set ws2 = Worksheets("Sheet2")    'determons sheet to paste to
    Set s = ws1.Range("A1,B3,C4,D4")    'determins range to copy

    x = 0    'first cell to paste to is "ws2.Range("A65536").End(xlUp).Offset(1, 0)" then changes as the loop progresses
    y = 1

    For Each c In s.Cells

        Set z = ws2.Range("A65536").End(xlUp).Offset(y, x)    'finds 1st empty cell after last filled cell

        c.Copy Destination:=z

        x = x + 1
        y = 0

    Next c

End Sub

Here is a very simple example, but it should work in your case

Click the button, the ranges will be copied to the next sheet

Change the A65536 to a D65536