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

[Anthrax]

*** 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.

I have also posted this information on

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:

Sub RNDD()
'
' RNDD Macro
'

'
    Dim Ws As Worksheet
    Dim i
        i = Worksheets("PalletLabel").Cells("B2").Value
    
    Set Ws = Sheets(9)
    ActiveSheet.Range("E2").Select
    If Not IsEmpty(ActiveCell.Value) Then Sheets(6).Name = Cells("E2").Value Else
    Set Ws = Sheets(10)
    ActiveSheet.Range("E2").Select
    If Not IsEmpty(ActiveCell.Value) Then Sheets(6).Name = Cells("E2").Value Else
    Set Ws = Sheets(11)
    ActiveSheet.Range("E2").Select
    If Not IsEmpty(ActiveCell.Value) Then Sheets(6).Name = Cells("E2").Value Else
    Sheets(6).Name = "#Error#"
    Set Ws = Sheets(5)
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Set Ws = Sheets(1)
    Range("A2").Select
    Range("A2").AutoFill Destination:=Range("A2:A" & i), Type:=xlFillDefault
       
End Sub

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

http://www.excelforum.com/excel-programming-vba-macros/949789-vba-code-help-several-actions-in-one-code-declaring-and-using-a-variable.html

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