copy and paste values in another sheet

[Roller]

Hello all,

I've been spinning around on this issue for 2 days now but I just can't seem to get it right. I have a worksheet where users input data, click on radio buttons and select data in named ranges. Based on all of this, formulas record the results in the range z1:af1 in that same sheet.

I plan on having a button that the user can click on to copy and paste values of the range above into another worksheet ("Compiled Results") and clear the input variables (basically resetting the worksheet for another day's work). Each time the values are pasted from "Evaluation Tab", it should populate the next row down. Lastly, I will use pivot tables to extract the info I need.

The portion I cannot get to work is to copy and paste values into the next row of "Compiled Results" sheet. Is there a simple way to make this happen? Thanks a million for any help!

 

[Norie]

Try this.

Worksheets("Evaluation tab").Range("Z1:AF1").Copy

Worksheets.Range("A"&Rows.Count).End(xlup).Offset(1).PasteSpecial xlPasteValues

 

[Roller]

Thanks for the response. I tried this and got a compile error: Method or data member not found. Looking at this, I tried to add a line to select the "Compiled Results" tab then tried to paste the results but got the same error. Don't we have to tell the code where to paste the data?

 

[Norie]

A typo, of course you need to provide the destination.

Replace Worksheets with Worksheets("Compiled Data") on the 2nd line.

So that would be:

Worksheets("Evaluation tab").Range("Z1:AF1").Copy
 
Worksheets("Compiled Data").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
 
Application.CutCopyMode = False

 

[Roller]

Thanks so much!! This did work. With my limited knowledge of VBA code, I used

Worksheet("Compiled Data").select

to try and select the destination worksheet. I do not have a good method for tracking changes between all my edits, so sometimes its difficult to remember exactly what works and what doesn't. Hopefully others can learn from my shortcomings, but nonetheless, I appreciate your help. Take care.

Roller