Macro Code Only Pastes Once Need to repeat

[realniceguy5000]

Hello,

I Have a couple problems with this Macro code:
1.
I have 2 sheets (Data) & (Store) When I run the macro it will create a new sheet(wks) and Copy information from (Data) sheet cells A1 and Pastes to my (store) sheet However It will only paste the information from cell A1 I want the script to keep coping and pasting and stop when it hits a blank cell.
Here is what I have so far...

2. Problems with this line of code below: "Sheets("Store").Range("A1").PasteSpecial Paste:=xlPasteValues"
When I change the name of the sheet from "Store" to "wks" the script will not run correcly however I thought the script already knew that the new sheet was named "wks"

Can someone help explain some?

Please Login or Register  to view this content.

Thank You for any help...

PS Thanks shg for all your help with my last project...

[royUK]

There is no need to Select anything, nor to Loop in this code. I'm also not sure why you are adding a worksheet. If I follow your code correctly this should do what you want

Please Login or Register  to view this content.