Excel VBA - copy/paste special to another worksheet and descending rows

[TheRobster]

Hi all,

I have two worksheets in a workbook, one called Calculations and one called Results. Calculations has a bunch of equations for running some data analysis that iterates through data sets using VBA code and results from each iteration are always output in cells A1:Z1. Results from each iteration need to be stored in the worksheet called Results.

Basically I need some way of copying cells A1:Z1 from Calculations worksheet to Results but each paste operation has to be below the last, e.g.

iteration 1: copy/pastespecial values only A1:Z1 from Calculations to A1:Z1 in Results
iteration 2: copy/pastespecial values only A1:Z1 from Calculations to A2:Z2 in Results
iteration 2: copy/pastespecial values only A1:Z1 from Calculations to A3:Z3 in Results

Etc etc. This process continues for some amount of iterations that is user-defined (I have this bit sorted using a variable).

I imagine there's some combination of pastespecial and xlup commands that can do this but I've tried to write the code without success. Can anyone offer any help?

Thanks in advance.
-Rob

[kobie2402]

If you like, I could try to help. Can you share the file so that I can take a look at it for you? Or post a screenshot of what we are looking at?

[TheRobster]

Well I got something that works but it isn't very elegant:

Range("A1:Z1").Copy

With Sheets("Results")
    .Range("A99999").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End With

This is run with worksheet Simulation selected. However when pasting results to the Results worksheet it always selects it, then jumps back to worksheet Simulation. Is there anyway of stopping it doing this? It shouldn't have to select a worksheet to copy results to it surely?

[kobie2402]

What event handler initiates the function?

Is this what you are looking for??

Range("A1:Z1").Copy

With Sheets("Results")
    .Range("A99999").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End With
    Sheets("Results").Select