VBA code help - several actions in one code + declaring and using a variable

*** I solved this issue! Thanks to anyone that may have looked and pondered here. ***


Hi all!

I have been struggling with this code for a few hours now.. it seems like I cannot get it to work at all.

The code itself is probably not the best, as I've written is by piecing together my own logic + some things I've picked up from various forums.

Here is the code, followed by an explanation of what I want to do with it:
Code:

Please Login or Register  to view this content.

Explanation:
There is a button on sheet 1 that will activate this code. Once activated, it will search Sheets 9, 10, and finally 11 for a value in cell E2 (only one of the three sheets will have a value in it), and rename sheet 6 with that value. If no values are found, it will rename the sheet with "#Error#". So far, it renames the sheet to "#Error#" regardless if there is entries in one of the 3 sheets' E2 cells or not.

From here, it will go to the sheet named "PartPoQty" (sheet 5) and refresh the pivot table there (was working fine for me, but stopped working as well).

Finally, it will go to the first sheet, name "PalletLabel". Here is where I'm having the biggest issues. On this sheet in cell B1 is a value that tells me how many pallet labels I need (example, 25). This number will change every time the code is run because this workbook is for sales invoices, and different information will be put in to the spreadsheet whenever it is used, which will result in this number being calculated differently every time. It will always be a non-zero integer between 1-35ish. What I'm trying to do with the last few lines of code is have Excel autofill the value in cell A2 (so that it increases by one each cell.. works manually) for x number of cells, where x = cell B1's value.

Example, if B1 = 25 = number of pallet labels I need, A2 will be filled down 24 rows to create 25 unique pallet labels (PPP1000001, PPP1000002, ..03, ..04, etc).


It should be worth mentioning that although the first part, ie checking for values amidst the last 3 sheets and renaming sheet 6, is not a necessity but more of a luxury. The most important thing is the last part, ie filling down column a x times.

If it were at all possible, too, actually.. the value for cell A2 that gets filled is a value in the format of PPPxxxxxx, where each x is a number. This is copied over from another workbook, which we will call "Pallet#s". Is there any way to import this number from the last entry in Column H of that workbook into cell A2, then use it to fill down?

Thank you all for your time.
Anthrax

Edit: Cross-posted on

HTML Code: 

Excelfox for higher traffic. This is urgent- work related, and will be monitored actively.

Why are you referring to ActiveSheet throughout the code and not using the variable Ws?

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Cross Post: http://www.excelfox.com/forum/f2/vba...variable-1400/