Code running different in PrivateSub Workbook_Open

[YairM]

Hi All!

This forum has been awesome for getting me started on VBA, I'm not a programmer and I had to pick this up now because the company that I'm working for has a really outdated system and we have to do a lot of Excel manipulation to get the files the way we need them. I appreciate any help that can be offered!

Can anyone explain to me why there's a difference with VBA code behavior from within a Private Sub Workbook_Open()?

I wrote some code that works perfectly when I run it "manually" (by running the macro) but once I put into Private Sub Workbook_Open() it starts crashing!!
This isn't the first time this has happened to me and I thought this might be a good place to investigate why this might be happening.
this is the statement that crashes this macro:

Sheets("CellNumbers").Select

which as you can see is a pretty straight forward statement, obviously the workbook that is open and active has that sheet inside of it and it's not hidden or protected, just a regular sheet

the error message is: "Run-time error '1104': Select method of worksheet class failed"

I think I'm missing something in general knowledge to figure this out. So again, can someone please explain the difference between running a macro manually and through Workbook_Open??

Thanks very much in advanced for any help!!

 

[hippiehacker]

can you post the entire code the line should be fine maybe something before happen to cause this problem.
Which excel versuion you are using?

 

[YairM]

Hi,

This is my code, it's pretty robust and I'm sure you can find a lot of things I'm not doing as efficient as possible, as I said I'm not a programmer. As I said this code has been working for me for the past two months when I run it manually the problems begin when it's moved into the Private Sub Workbook_Open. I'm using Excel 2007
Please note that I have marked with * some of the information as it's a security risk to publish it online.
Thanks so much for your help!

Private Sub Workbook_Open()
Sheets("Data").Select
Application.Workbooks.Open ("\\********\d*****\THALL.csv")
Workbooks("THALL.csv").Activate
Columns("A:A").Select
Selection.NumberFormat = "0.00"
Selection.NumberFormat = "0.0"
Selection.NumberFormat = "0"
Workbooks("THALL.csv").ActiveSheet.Protect Password:="thr123"
Workbooks("ALLDATA.xlsm").Activate
Application.DisplayAlerts = False
Columns("R:R").Select
Selection.NumberFormat = "General"
Sheets("Cellnumbers").Select
Workbooks("ALLDATA.xlsm").ActiveSheet.Unprotect Password:="yair123"
Range("A1").Select
r = ActiveCell.Value
x = ActiveCell.Value
Sheets("Data").Select
Range("A" & (r + 1)).Select
Application.Workbooks.Open ("\\*******\*******\backup\" & (Format(Date - 1, "yyyymmdd")) & ".csv ")
Range("A1").Select
Do Until IsEmpty(ActiveCell.Offset(1, 0)) = True
ActiveCell.Offset(1, 0).Select
b = ActiveCell.Row
Loop
If ActiveCell = "TLR" Then
ActiveCell.EntireRow.Delete
b = b - 1
End If
Range("A2:O" & b).Copy
Workbooks("ALLDATA.xlsm").Activate
ActiveSheet.Paste
Range("P" & r + 1).Select
Do While IsEmpty(ActiveCell.Offset(0, -15)) = False
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],THALL.csv!C1:C11,2,0)"
ActiveCell.Offset(0, 1) = "=VLOOKUP(RC[-8],THALL.csv!C1:C11,3,0)"
ActiveCell.Offset(0, 2) = "=VLOOKUP(RC[-9],THALL.csv!C1:C11,4,0)"
ActiveCell.Offset(0, 3) = "=VLOOKUP(RC[-10],THALL.csv!C1:C11,5,0)"
If ActiveCell.Offset(0, -5) < 0 Then
If ActiveCell.Offset(0, -1) < 0 Then
qty = ActiveCell.Offset(0, -5).Value
net = ActiveCell.Offset(0, -1).Value
ActiveCell.Offset(0, 4).FormulaR1C1 = (qty * (-1)) * net
net = ActiveCell.Offset(0, 4).Value
Else
qty = ActiveCell.Offset(0, -5).Value
net = ActiveCell.Offset(0, -1).Value
ActiveCell.Offset(0, 4).FormulaR1C1 = qty * net
net = ActiveCell.Offset(0, 4).Value
End If
Else
qty = ActiveCell.Offset(0, -5).Value
net = ActiveCell.Offset(0, -1).Value
ActiveCell.Offset(0, 4).FormulaR1C1 = qty * net
net = ActiveCell.Offset(0, 4).Value
End If
ActiveCell.Offset(0, 6).FormulaR1C1 = net / 1.1
ActiveCell.Offset(0, 5).FormulaR1C1 = "=ROUND(RC[1],2)"

ActiveCell.Offset(0, -11).FormulaR1C1 = (Date - 1)
r = ActiveCell.Row
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(0, -15).Select
Application.Workbooks.Open ("\\193.1.1.108\*******\backup\" & (Format(Date - 1, "yyyymmdd")) & ".csv ")
ActiveWorkbook.Close savechanges:=False
Application.Workbooks.Open ("\\193.1.1.108\*******\backup\ExportInstore\website_instore_orders_" & (Format(Date, "d_m_yyyy")) & "_1_30_0.csv ")
Range("A1").Select
Do Until IsEmpty(ActiveCell.Offset(1, 0)) = True
ActiveCell.Offset(1, 0).Select
b = ActiveCell.Row
Loop
Range("A2:O" & b).Copy
Workbooks("ALLDATA.xlsm").Activate
ActiveSheet.Paste
Range("P" & r + 1).Select
Do While IsEmpty(ActiveCell.Offset(0, -15)) = False
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],THALL.csv!C1:C11,2,0)"
ActiveCell.Offset(0, 1) = "=VLOOKUP(RC[-8],THALL.csv!C1:C11,3,0)"
ActiveCell.Offset(0, 2) = "=VLOOKUP(RC[-9],THALL.csv!C1:C11,4,0)"
ActiveCell.Offset(0, 3) = "=VLOOKUP(RC[-10],THALL.csv!C1:C11,5,0)"
If ActiveCell.Offset(0, -5) < 0 Then
If ActiveCell.Offset(0, -1) < 0 Then
qty = ActiveCell.Offset(0, -5).Value
net = ActiveCell.Offset(0, -1).Value
ActiveCell.Offset(0, 4).FormulaR1C1 = (qty * (-1)) * net
net = ActiveCell.Offset(0, 4).Value
Else
qty = ActiveCell.Offset(0, -5).Value
net = ActiveCell.Offset(0, -1).Value
ActiveCell.Offset(0, 4).FormulaR1C1 = qty * net
net = ActiveCell.Offset(0, 4).Value
End If
Else
qty = ActiveCell.Offset(0, -5).Value
net = ActiveCell.Offset(0, -1).Value
ActiveCell.Offset(0, 4).FormulaR1C1 = qty * net
net = ActiveCell.Offset(0, 4).Value
End If
ActiveCell.Offset(0, 6).FormulaR1C1 = net / 1.1
ActiveCell.Offset(0, 5).FormulaR1C1 = "=ROUND(RC[1],2)"
ActiveCell.Offset(0, -11).FormulaR1C1 = (Date - 1)
r = ActiveCell.Row
ActiveCell.Offset(1, 0).Select
Loop
Application.Workbooks.Open ("\\193.1.1.108\*******\backup\ExportInstore\website_instore_orders_" & (Format(Date, "d_m_yyyy")) & "_1_30_0.csv ")
ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
Sheets("Cellnumbers").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = r
Workbooks("ALLDATA.xlsm").ActiveSheet.Protect Password:="yair123"
Sheets("Data").Select
Columns("I:I").Select
Selection.NumberFormat = "0.00"
Selection.NumberFormat = "0.0"
Selection.NumberFormat = "0"
Columns("R:R").Select
Selection.NumberFormat = "@"

'Workbooks("THALL.csv").Activate
'Workbooks("THALL.csv").ActiveSheet.Unprotect Password:="thr123"
'ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Master_Tables\ALLDATA.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\ALLDATA.xlsm", FileFormat:=52
Application.Calculation = xlCalculateManual
'ThisWorkbook.Save
'Application.Quit
Range("A" & r - (r - x - 1)).Select
Application.Workbooks.Open ("*******\Yair\*******_Cube\Stores\ALLDATA2101.xlsm")
Sheets("CellNumbers").Select
Range("A1").Select
v2101 = ActiveCell.Value
Sheets("DATA").Select
Range("A" & v2101).Select
Workbooks("ALLDATA.xlsm").Activate
Do While IsEmpty(ActiveCell.Value) = False
If ActiveCell.Value = 2101 Then
ActiveCell.EntireRow.Copy
Workbooks("ALLDATA2101.xlsm").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
v2101 = ActiveCell.Row
Workbooks("ALLDATA.xlsm").Activate
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Workbooks("ALLDATA2101.xlsm").Activate
Sheets("CellNumbers").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = v2101
Sheets("DATA").Select
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Stores\ALLDATA2101.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Master_Tables\ALLDATA2101.xlsm", FileFormat:=52
ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
Range("A" & r - (r - x - 1)).Select
Application.Workbooks.Open ("*******\Yair\*******_Cube\Stores\ALLDATA2107.xlsm")
Sheets("CellNumbers").Select
Range("A1").Select
v2107 = ActiveCell.Value
Sheets("DATA").Select
Range("A" & v2107).Select
Workbooks("ALLDATA.xlsm").Activate
Do While IsEmpty(ActiveCell.Value) = False
If ActiveCell.Value = 2107 Then
ActiveCell.EntireRow.Copy
Workbooks("ALLDATA2107.xlsm").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
v2107 = ActiveCell.Row
Workbooks("ALLDATA.xlsm").Activate
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Workbooks("ALLDATA2107.xlsm").Activate
Sheets("CellNumbers").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = v2107
Sheets("DATA").Select
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Stores\ALLDATA2107.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Master_Tables\ALLDATA2107.xlsm", FileFormat:=52
ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
Range("A" & r - (r - x - 1)).Select
Application.Workbooks.Open ("*******\Yair\*******_Cube\Stores\ALLDATA2105.xlsm")
Sheets("CellNumbers").Select
Range("A1").Select
v2105 = ActiveCell.Value
Sheets("DATA").Select
Range("A" & v2105).Select
Workbooks("ALLDATA.xlsm").Activate
Do While IsEmpty(ActiveCell.Value) = False
If ActiveCell.Value = 2105 Then
ActiveCell.EntireRow.Copy
Workbooks("ALLDATA2105.xlsm").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
v2105 = ActiveCell.Row
Workbooks("ALLDATA.xlsm").Activate
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Workbooks("ALLDATA2105.xlsm").Activate
Sheets("CellNumbers").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = v2105
Sheets("DATA").Select
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Stores\ALLDATA2105.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Master_Tables\ALLDATA2105.xlsm", FileFormat:=52
ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
Range("A" & r - (r - x - 1)).Select
Application.Workbooks.Open ("*******\Yair\*******_Cube\Stores\ALLDATA2113.xlsm")
Sheets("CellNumbers").Select
Range("A1").Select
v2113 = ActiveCell.Value
Sheets("DATA").Select
Range("A" & v2113).Select
Workbooks("ALLDATA.xlsm").Activate
Do While IsEmpty(ActiveCell.Value) = False
If ActiveCell.Value = 2113 Then
ActiveCell.EntireRow.Copy
Workbooks("ALLDATA2113.xlsm").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
v2113 = ActiveCell.Row
Workbooks("ALLDATA.xlsm").Activate
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Workbooks("ALLDATA2113.xlsm").Activate
Sheets("CellNumbers").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = v2113
Sheets("DATA").Select
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Stores\ALLDATA2113.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Master_Tables\ALLDATA2113.xlsm", FileFormat:=52
ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
Range("A" & r - (r - x - 1)).Select
Application.Workbooks.Open ("*******\Yair\*******_Cube\Stores\ALLDATA2112.xlsm")
Sheets("CellNumbers").Select
Range("A1").Select
v2112 = ActiveCell.Value
Sheets("DATA").Select
Range("A" & v2112).Select
Workbooks("ALLDATA.xlsm").Activate
Do While IsEmpty(ActiveCell.Value) = False
If ActiveCell.Value = 2112 Then
ActiveCell.EntireRow.Copy
Workbooks("ALLDATA2112.xlsm").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
v2112 = ActiveCell.Row
Workbooks("ALLDATA.xlsm").Activate
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Workbooks("ALLDATA2112.xlsm").Activate
Sheets("CellNumbers").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = v2112
Sheets("DATA").Select
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Stores\ALLDATA2112.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Master_Tables\ALLDATA2112.xlsm", FileFormat:=52
ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
Range("A" & r - (r - x - 1)).Select
Application.Workbooks.Open ("*******\Yair\*******_Cube\Stores\ALLDATA2117.xlsm")
Sheets("CellNumbers").Select
Range("A1").Select
v2117 = ActiveCell.Value
Sheets("DATA").Select
Range("A" & v2117).Select
Workbooks("ALLDATA.xlsm").Activate
Do While IsEmpty(ActiveCell.Value) = False
If ActiveCell.Value = 2117 Then
ActiveCell.EntireRow.Copy
Workbooks("ALLDATA2117.xlsm").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
v2117 = ActiveCell.Row
Workbooks("ALLDATA.xlsm").Activate
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Workbooks("ALLDATA2117.xlsm").Activate
Sheets("CellNumbers").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = v2117
Sheets("DATA").Select
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Stores\ALLDATA2117.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Master_Tables\ALLDATA2117.xlsm", FileFormat:=52
ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
Range("A" & r - (r - x - 1)).Select
Application.Workbooks.Open ("*******\Yair\*******_Cube\Stores\ALLDATA0031.xlsm")
Sheets("CellNumbers").Select
Range("A1").Select
v0031 = ActiveCell.Value
Sheets("DATA").Select
Range("A" & v0031).Select
Workbooks("ALLDATA.xlsm").Activate
Do While IsEmpty(ActiveCell.Value) = False
If ActiveCell.Value = 31 Then
ActiveCell.EntireRow.Copy
Workbooks("ALLDATA0031.xlsm").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
v0031 = ActiveCell.Row
Workbooks("ALLDATA.xlsm").Activate
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Workbooks("ALLDATA0031.xlsm").Activate
Sheets("CellNumbers").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = v0031
Sheets("DATA").Select
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Stores\ALLDATA0031.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Master_Tables\ALLDATA0031.xlsm", FileFormat:=52
ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
Range("A" & r - (r - x - 1)).Select
Application.Workbooks.Open ("*******\Yair\*******_Cube\Stores\ALLDATA3201.xlsm")
Sheets("CellNumbers").Select
Range("A1").Select
v3201 = ActiveCell.Value
Sheets("DATA").Select
Range("A" & v3201).Select
Workbooks("ALLDATA.xlsm").Activate
Do While IsEmpty(ActiveCell.Value) = False
If ActiveCell.Value = 3201 Then
ActiveCell.EntireRow.Copy
Workbooks("ALLDATA3201.xlsm").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
v3201 = ActiveCell.Row
Workbooks("ALLDATA.xlsm").Activate
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Workbooks("ALLDATA3201.xlsm").Activate
Sheets("CellNumbers").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = v3201
Sheets("DATA").Select
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Stores\ALLDATA3201.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Master_Tables\ALLDATA3201.xlsm", FileFormat:=52
ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
Range("A" & r - (r - x - 1)).Select
Application.Workbooks.Open ("*******\Yair\*******_Cube\Stores\ALLDATA3206.xlsm")
Sheets("CellNumbers").Select
Range("A1").Select
v3206 = ActiveCell.Value
Sheets("DATA").Select
Range("A" & v3206).Select
Workbooks("ALLDATA.xlsm").Activate
Do While IsEmpty(ActiveCell.Value) = False
If ActiveCell.Value = 3206 Then
ActiveCell.EntireRow.Copy
Workbooks("ALLDATA3206.xlsm").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
v3206 = ActiveCell.Row
Workbooks("ALLDATA.xlsm").Activate
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Workbooks("ALLDATA3206.xlsm").Activate
Sheets("CellNumbers").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = v3206
Sheets("DATA").Select
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Stores\ALLDATA3206.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Master_Tables\ALLDATA3206.xlsm", FileFormat:=52
ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
Range("A" & r - (r - x - 1)).Select
Application.Workbooks.Open ("*******\Yair\*******_Cube\Stores\ALLDATA3208.xlsm")
Sheets("CellNumbers").Select
Range("A1").Select
v3208 = ActiveCell.Value
Sheets("DATA").Select
Range("A" & v3208).Select
Workbooks("ALLDATA.xlsm").Activate
Do While IsEmpty(ActiveCell.Value) = False
If ActiveCell.Value = 3208 Then
ActiveCell.EntireRow.Copy
Workbooks("ALLDATA3208.xlsm").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
v3208 = ActiveCell.Row
Workbooks("ALLDATA.xlsm").Activate
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Workbooks("ALLDATA3208.xlsm").Activate
Sheets("CellNumbers").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = v3208
Sheets("DATA").Select
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Stores\ALLDATA3208.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Master_Tables\ALLDATA3208.xlsm", FileFormat:=52
ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
Range("A" & r - (r - x - 1)).Select
Application.Workbooks.Open ("*******\Yair\*******_Cube\Stores\ALLDATA4101.xlsm")
Sheets("CellNumbers").Select
Range("A1").Select
v4101 = ActiveCell.Value
Sheets("DATA").Select
Range("A" & v4101).Select
Workbooks("ALLDATA.xlsm").Activate
Do While IsEmpty(ActiveCell.Value) = False
If ActiveCell.Value = 4101 Then
ActiveCell.EntireRow.Copy
Workbooks("ALLDATA4101.xlsm").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
v4101 = ActiveCell.Row
Workbooks("ALLDATA.xlsm").Activate
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Workbooks("ALLDATA4101.xlsm").Activate
Sheets("CellNumbers").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = v4101
Sheets("DATA").Select
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Stores\ALLDATA4101.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Master_Tables\ALLDATA4101.xlsm", FileFormat:=52
ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
Range("A" & r - (r - x - 1)).Select
Application.Workbooks.Open ("*******\Yair\*******_Cube\Stores\ALLDATA4110.xlsm")
Sheets("CellNumbers").Select
Range("A1").Select
v4110 = ActiveCell.Value
Sheets("DATA").Select
Range("A" & v4110).Select
Workbooks("ALLDATA.xlsm").Activate
Do While IsEmpty(ActiveCell.Value) = False
If ActiveCell.Value = 4110 Then
ActiveCell.EntireRow.Copy
Workbooks("ALLDATA4110.xlsm").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
v4110 = ActiveCell.Row
Workbooks("ALLDATA.xlsm").Activate
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Workbooks("ALLDATA4110.xlsm").Activate
Sheets("CellNumbers").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = v4110
Sheets("DATA").Select
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Stores\ALLDATA4110.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Master_Tables\ALLDATA4110.xlsm", FileFormat:=52
ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
Range("A" & r - (r - x - 1)).Select
Application.Workbooks.Open ("*******\Yair\*******_Cube\Stores\ALLDATA5101.xlsm")
Sheets("CellNumbers").Select
Range("A1").Select
v5101 = ActiveCell.Value
Sheets("DATA").Select
Range("A" & v5101).Select
Workbooks("ALLDATA.xlsm").Activate
Do While IsEmpty(ActiveCell.Value) = False
If ActiveCell.Value = 5101 Then
ActiveCell.EntireRow.Copy
Workbooks("ALLDATA5101.xlsm").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
v5101 = ActiveCell.Row
Workbooks("ALLDATA.xlsm").Activate
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Workbooks("ALLDATA5101.xlsm").Activate
Sheets("CellNumbers").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = v5101
Sheets("DATA").Select
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Stores\ALLDATA5101.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Master_Tables\ALLDATA5101.xlsm", FileFormat:=52
ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
Range("A" & r - (r - x - 1)).Select
Application.Workbooks.Open ("*******\Yair\*******_Cube\Stores\ALLDATA6101.xlsm")
Sheets("CellNumbers").Select
Range("A1").Select
v6101 = ActiveCell.Value
Sheets("DATA").Select
Range("A" & v6101).Select
Workbooks("ALLDATA.xlsm").Activate
Do While IsEmpty(ActiveCell.Value) = False
If ActiveCell.Value = 6101 Then
ActiveCell.EntireRow.Copy
Workbooks("ALLDATA6101.xlsm").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
v6101 = ActiveCell.Row
Workbooks("ALLDATA.xlsm").Activate
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Workbooks("ALLDATA6101.xlsm").Activate
Sheets("CellNumbers").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = v6101
Sheets("DATA").Select
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Stores\ALLDATA6101.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Master_Tables\ALLDATA6101.xlsm", FileFormat:=52
ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
Range("A" & r - (r - x - 1)).Select
Application.Workbooks.Open ("*******\Yair\*******_Cube\Stores\ALLDATA6104.xlsm")
Sheets("CellNumbers").Select
Range("A1").Select
v6104 = ActiveCell.Value
Sheets("DATA").Select
Range("A" & v6104).Select
Workbooks("ALLDATA.xlsm").Activate
Do While IsEmpty(ActiveCell.Value) = False
If ActiveCell.Value = 6104 Then
ActiveCell.EntireRow.Copy
Workbooks("ALLDATA6104.xlsm").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
v6104 = ActiveCell.Row
Workbooks("ALLDATA.xlsm").Activate
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Workbooks("ALLDATA6104.xlsm").Activate
Sheets("CellNumbers").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = v6104
Sheets("DATA").Select
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Stores\ALLDATA6104.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Master_Tables\ALLDATA6104.xlsm", FileFormat:=52
ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
Range("A" & r - (r - x - 1)).Select
Application.Workbooks.Open ("*******\Yair\*******_Cube\Stores\ALLDATA6103.xlsm")
Sheets("CellNumbers").Select
Range("A1").Select
v6103 = ActiveCell.Value
Sheets("DATA").Select
Range("A" & v6103).Select
Workbooks("ALLDATA.xlsm").Activate
Do While IsEmpty(ActiveCell.Value) = False
If ActiveCell.Value = 6103 Then
ActiveCell.EntireRow.Copy
Workbooks("ALLDATA6103.xlsm").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
v6103 = ActiveCell.Row
Workbooks("ALLDATA.xlsm").Activate
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Workbooks("ALLDATA6103.xlsm").Activate
Sheets("CellNumbers").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = v6103
Sheets("DATA").Select
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Stores\ALLDATA6103.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Master_Tables\ALLDATA6103.xlsm", FileFormat:=52
ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
Range("A" & r - (r - x - 1)).Select
Application.Workbooks.Open ("*******\Yair\*******_Cube\Stores\ALLDATA6102.xlsm")
Sheets("CellNumbers").Select
Range("A1").Select
v6102 = ActiveCell.Value
Sheets("DATA").Select
Range("A" & v6102).Select
Workbooks("ALLDATA.xlsm").Activate
Do While IsEmpty(ActiveCell.Value) = False
If ActiveCell.Value = 6102 Then
ActiveCell.EntireRow.Copy
Workbooks("ALLDATA6102.xlsm").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
v6102 = ActiveCell.Row
Workbooks("ALLDATA.xlsm").Activate
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Workbooks("ALLDATA6102.xlsm").Activate
Sheets("CellNumbers").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = v6102
Sheets("DATA").Select
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Stores\ALLDATA6102.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Master_Tables\ALLDATA6102.xlsm", FileFormat:=52
ActiveWorkbook.Close savechanges:=False
Workbooks("ALLDATA.xlsm").Activate
Range("A" & r - (r - x - 1)).Select
ActiveWorkbook.SaveAs "*******\Yair\*******_Cube\Lauren\ALLDATAL.xlsm", FileFormat:=52
Application.Calculation = xlCalculationAutomatic
Workbooks("THALL.csv").Activate
Workbooks("THALL.csv").ActiveSheet.Unprotect Password:="thr123"
ActiveWorkbook.Close savechanges:=False
'Application.Quit


End Sub