Copy/Paste Special Macro

So in words, each time the macro runs it copies A1:A49 from sheet "% s" into the next empty column of Peak Statistics starting at row 9 pasting as values only?

Correct. I don't think this poses a problem, but there are also "spacer cells" (empty cells) on sheet "%s".

I tried your code, same result. I verified that it did in fact copy the data as I could paste it after running the macro, but the macro itself does not paste anything. Below is an example of what it "should" paste from cells A1:A49 from sheet "%s":

0%
0%
0%

49%
46%
46%


464230
485672
485230

0
0
0
0
0
0
0
1
712
868
541
164
146
57
125
100


1.2%
4.2%
0.6%
0.6%
0.0%
3.9%
2.6%
0.9%
1.3%


4.0%
4.4%
9.3%
1.5%
0.0%
0.1%
0.0%

Thanks for the help here.

Trying that code, I receive the following error:

"Subscript out of range"

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook. Let's look at this together.

Sorry, not sure what you mean here.

Tried the latest code, no error this time but not data shows on the Peak Statistics sheet in the columns after running.

Maybe there's some stray garbage in row 9. Look way off to the right, see if it's pasting the data into columns you're not checking.

I double checked, nothing there unfortunately. Something weird though, I did a search on everything in the workbook for a 0% and it did not return the 0%'s in the "%s" sheet...

If it would make it easier, I can email you a copy of the spreadsheet with my macros?

Alright, here is the xls with the macros included (I renamed your sub to PasteSpecial).

If you were to go through the whole process, this is what I do:

1. Open xls, go to Web Query sheet
2. alt+f8 to open macros (or ctrl+q to run macro), run Sheet3.WebQueryMacro
3. Sit back and let it do it's thing.

The WebQueryMacro basically goes out and pulls the stats from internal sites on statistics every hour (manually run the macro every hour) and copies data from specified cells in the Web Query sheet to the %s sheet. It then calls the PasteSpecial macro which, as you know, tries to take the values on %s sheet and paste them into Peak Statistics.

Note - If you run the WebQueryMacro, it is going to delete what is currently there (it wipes the sheet before running) because you will not be able to contact my local sites. I mainly included it so you could check it out if need be.
While I have been testing this with you, I have just run the PasteSpecial macro from the %s sheet.

Sorry if this complicates things; the only thing I am having trouble with is pasting the data from %s to Peak Statistics, everything else is beautiful.

Thanks again for the help.

......

After your post, I tried it from another PC and it does work fine...whenever I run it from my pc, I get no data in the Peak Statistics sheet....I'll look into this pc and see what is going on...

I hope you realise that refreshing a Webquery doesn't trigger an event in the workbook.

Every time I run the WebQuery Macro, it should access the site listed and copy the information from that site into the Web Query sheet. This data can update constantly on the site. I know that it does not update what I see in the sheet until I query the site again.

Gotcha. Is there a way to allow the macro to "pause" before calling the PasteSpecial sub?

Sorry, my last post was a little confusing. You are correct, I can get your Macro to paste the data every time. My question to that is; is there something different I should do to the Sheet3.WebQueryMacro to get it to call that macro? That way once the query is finished it will execute the next macro as well.

Alright. Thanks to both of you for the help; been great! I'd consider this issue resolved.