Next Function in Array

  • Thread starter Thread starter DMJohnDeere
  • Start date Start date
D

DMJohnDeere

I have a macro that I am running in Excel which is (1) retrieving data from a
database, (2) making a copy of the worksheet (3), copy and pasting the
values, (4) and then repeating the same process over and over again.

At the beginning of the macro I am declaring an Array of 43 items and then I
am using a "Next" statement to cycle through the each item. It is working
just fine until it gets to the last item in the array. At that point it will
not make a copy of the worksheet and even if you cancel the macro and try to
do it manually it will not copy the worksheet. You can create a new blank
worksheet, but you can' copy one. Any idea what would be causing this. The
number or worksheets in the workbook at the time of the error is 48, which I
don't believe is the problem because I'm sure I've had files with more
worksheets than 48. Your help would be greatly appreciated.
 
Here is the code.

Sub AllCostCenters()
' Run all cost center SGAs

'COMPANY VARIABLES
Dim Company As Variant
A = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 38, 39,
40, 41, 42, 44, 58, 59, 60)
For Each Company In A

GoTo Begin:

Begin:

'Local currency
Sheets("DataLists").Select
Range("B8").Select
ActiveCell.FormulaR1C1 = Company

'Application.Goto Reference:="Retrieve1"
'Application.Run Macro:="EssMenuRetrieve"
'Application.Goto Reference:="Retrieve2"
'Application.Run Macro:="EssMenuRetrieve"

Sheets("Point in Time").Select
Sheets("Point in Time").Copy before:=Sheets("Input")
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Sheets("DataLists").Select
Range("B8").Select
Sheets("Point in Time (2)").Activate

If Sheets("DataLists").Range("B8").Value = "1" Then
ActiveSheet.Name = "All Cost Centers"
End If

If Sheets("DataLists").Range("B8").Value = "2" Then
ActiveSheet.Name = "105 UK Sales Development"
End If

If Sheets("DataLists").Range("B8").Value = "3" Then
ActiveSheet.Name = "106 UK Sales North"
End If

If Sheets("DataLists").Range("B8").Value = "4" Then
ActiveSheet.Name = "107 UK Sales South"
End If

If Sheets("DataLists").Range("B8").Value = "5" Then
ActiveSheet.Name = "108 UK Direct Sales"
End If

If Sheets("DataLists").Range("B8").Value = "6" Then
ActiveSheet.Name = "109 UK EPPS"
End If

If Sheets("DataLists").Range("B8").Value = "7" Then
ActiveSheet.Name = "110 Germany Sales"
End If

If Sheets("DataLists").Range("B8").Value = "8" Then
ActiveSheet.Name = "111 Germany Sales Develop"
End If

If Sheets("DataLists").Range("B8").Value = "9" Then
ActiveSheet.Name = "115 Spain Sales"
End If

If Sheets("DataLists").Range("B8").Value = "10" Then
ActiveSheet.Name = "120 Italy Sales"
End If

If Sheets("DataLists").Range("B8").Value = "11" Then
ActiveSheet.Name = "125 Portugal Sales"
End If

If Sheets("DataLists").Range("B8").Value = "12" Then
ActiveSheet.Name = "130 Iberica Sales Develop"
End If

If Sheets("DataLists").Range("B8").Value = "13" Then
ActiveSheet.Name = "140 European Marketing"
End If

If Sheets("DataLists").Range("B8").Value = "14" Then
ActiveSheet.Name = "205 UK Operations"
End If

If Sheets("DataLists").Range("B8").Value = "15" Then
ActiveSheet.Name = "206 UK Collections"
End If

If Sheets("DataLists").Range("B8").Value = "16" Then
ActiveSheet.Name = "207 UK Risk"
End If

If Sheets("DataLists").Range("B8").Value = "17" Then
ActiveSheet.Name = "210 Germany Operations"
End If

If Sheets("DataLists").Range("B8").Value = "18" Then
ActiveSheet.Name = "215 Spain Operations"
End If

If Sheets("DataLists").Range("B8").Value = "19" Then
ActiveSheet.Name = "300 Lux Finance"
End If

If Sheets("DataLists").Range("B8").Value = "20" Then
ActiveSheet.Name = "305 UK Finance"
End If

If Sheets("DataLists").Range("B8").Value = "21" Then
ActiveSheet.Name = "400 Lux IT"
End If

If Sheets("DataLists").Range("B8").Value = "22" Then
ActiveSheet.Name = "401 IT Development"
End If

If Sheets("DataLists").Range("B8").Value = "23" Then
ActiveSheet.Name = "402 ICDS"
End If

If Sheets("DataLists").Range("B8").Value = "24" Then
ActiveSheet.Name = "405 UK IT"
End If

If Sheets("DataLists").Range("B8").Value = "25" Then
ActiveSheet.Name = "500 Lux HR Services"
End If

If Sheets("DataLists").Range("B8").Value = "26" Then
ActiveSheet.Name = "505 UK HR Services"
End If

If Sheets("DataLists").Range("B8").Value = "27" Then
ActiveSheet.Name = "650 Legal"
End If

If Sheets("DataLists").Range("B8").Value = "28" Then
ActiveSheet.Name = "700 Wholesale Lux"
End If

If Sheets("DataLists").Range("B8").Value = "29" Then
ActiveSheet.Name = "701 Whlsl Inv Verif"
End If

If Sheets("DataLists").Range("B8").Value = "30" Then
ActiveSheet.Name = "702 Wholesale Germany"
End If

If Sheets("DataLists").Range("B8").Value = "31" Then
ActiveSheet.Name = "703 Wholesale UK"
End If

If Sheets("DataLists").Range("B8").Value = "32" Then
ActiveSheet.Name = "704 Wholesale Spain"
End If

If Sheets("DataLists").Range("B8").Value = "33" Then
ActiveSheet.Name = "705 Wholesale Italy"
End If

If Sheets("DataLists").Range("B8").Value = "34" Then
ActiveSheet.Name = "706 Wholesale France"
End If

If Sheets("DataLists").Range("B8").Value = "35" Then
ActiveSheet.Name = "800 Administration Lux"
End If

If Sheets("DataLists").Range("B8").Value = "38" Then
ActiveSheet.Name = "Sales"
End If

If Sheets("DataLists").Range("B8").Value = "39" Then
ActiveSheet.Name = "Operations"
End If

If Sheets("DataLists").Range("B8").Value = "40" Then
ActiveSheet.Name = "Finance and IT"
End If

If Sheets("DataLists").Range("B8").Value = "41" Then
ActiveSheet.Name = "Administration"
End If

If Sheets("DataLists").Range("B8").Value = "42" Then
ActiveSheet.Name = "HR Services"
End If

If Sheets("DataLists").Range("B8").Value = "44" Then
ActiveSheet.Name = "Wholesale"
End If

If Sheets("DataLists").Range("B8").Value = "58" Then
ActiveSheet.Name = "UK Cost Centers"
End If

If Sheets("DataLists").Range("B8").Value = "59" Then
ActiveSheet.Name = "LUX Cost Centers"
End If

If Sheets("DataLists").Range("B8").Value = "60" Then
ActiveSheet.Name = "Spain Cost Centers"
End If



'*********************************************************************


Next Company

Call SAVELUXTOWEB



End Sub
 
Change it to this:

Dim i As Long

A = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 38, 39,
40, 41, 42, 44, 58, 59, 60)

For i = 0 To UBound(A)

ActiveCell.FormulaR1C1 = A(i)

Next i


RBS
 
Before I attempt to simplify your code, is that really your code? By that I
mean, do you really have an array of the first 60 integers, or our those
numbers substitutes for text strings?
 
The numbers are substitutes for text strings. You can see below the
equivalent of what the integers represent. This is code that someone else
put together and has worked for some time, but for some reason this month it
does not work. It gets to the last item and then will not copy the active
worksheet. What I did find though is if I open a blank spreadsheet, close
it, and then resume the code it finishes fine.
 
I understand the numbers are substitutes for other text strings, I was just
looking for verification that the code you posted is, as shown, actually the
code you are using.
 
Back
Top