VBA Macro - sometimes it works, sometimes it doesn't

[EuanM28]

Hello!

I have a little issue here I am very confused with. I have coded a 4 subroutines that do the following:

1. Merge - Import specific worksheets from other workbook files and import them into a new one
2. Clean-up - Removes any unnecessary fields from both new worksheets
3. Model - Remodels one of the imported worksheets (Worksheet 1), including changing column arrangement and adding new columns with formatting and formulas for those columns.
4. Transform - Remodels the other imported worksheet (Worksheet 2), changes column header names, adds several new columns with formatting and formula's, a lot of VLOOKUPs from the last worksheet (Worksheet 1)

Now, all of my code works perfectly. However, the issue I am having is with the 4th and final subroutine (Transform). When implementing and testing my code, I would separate each subroutine to 4 separate buttons to click. The first 3 work exactly how I want them too. However, the 4th one seems to have an issue. All of the formatting works fine, but the VLOOKUPS and other formulas for newly added and formatted columns sometimes show their expected data and sometimes they do not (Instead they show blank cells).

When I gave my laptop a minute after it had finished the 3rd routine, the 4th and final button click would always work, showing the expected results. With this knowledge, I decided to add the following code at the end of my third subroutine and at the beginning of my 4th "Application.Wait (Now + TimeValue("00:20:00"))". I came to the conclusion that, oh! it must just need a few seconds to settle, as I am using VLOOKUPS etc to another worksheet that has JUST been updated and changed etc. However, this did not work. Simply running subroutines 1-3 and even giving a few seconds after the 3rd one loads to load the final one works.

Very confused, any ideas?

Note: The columns that I expect to have data just come up with BLANK cells, nothing in them. But, everything else the 4th subroutine does works perfectly.

This is my first ever question so apologies if anything is confused or worded badly!

Regards,
Euan

 

[steve the fish]

Its pretty much impossible to suggest why if we cant see the code.

 

[EuanM28]

Sub RemodelPSD() 'Remodel PSD - Used to remodel Planning System Data worksheet as changes need to be made in order to create PSL PO worksheet

Dim StartTime As Double 'Declaration of timer to identify length of runtime
StartTime = Timer

Call TurnOffCode 'Sets calculation to manual, screenupdats + events + animation + status bar + print coms is all turned off

'JT Key START

Sheets("PlanningSystemData").Columns("A:A").Insert Shift:=xlToRight 'New column for JT Key

lastRowJT2 = Range("A1").End(xlDown).Row 'Select all of row

Sheets("PlanningSystemData").Range("A2:A" & lastRowJT2).Formula = "=CONCATENATE(D2,E2)" 'Perform formula

With ThisWorkbook.Worksheets("PlanningSystemData").Range("A1")
     .Value = "JT Key" 'Create new column called "JT Key"
     .Font.Bold = True 'JT Key formatting - bold
     .Font.Size = 9 'JT Key formatting - font size
     .Font.Name = "Arial" 'JT Key formatting - font family
 _
    .Borders(xlEdgeBottom) _
    .LineStyle = XlLineStyle.xlContinuous 'JT Key formatting - Border bottom added
End With
            
'JT Key END

'Planning Date START
            
Sheets("PlanningSystemData").Select 'Format Planning date to correct format
    Columns("G:G").Select
    Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
lastRowPlanningDate = Range("G1").End(xlDown).Row
        
Sheets("PlanningSystemData").Range("G2:G" & lastRowPlanningDate).NumberFormat = "dd/mm/yyyy" 'change to day/month/year

'Planning Date END

'Order quantity(1000's) START

Sheets("PlanningSystemData").Columns("O:O").Insert Shift:=xlToRight 'New column for Order quantity(1000's)

lastRowOrderQuantity = Range("O1").End(xlDown).Row 'Select all of row

Sheets("PlanningSystemData").Range("O2:O" & lastRowOrderQuantity).Formula = "=N2*1000" 'Perform formula

Sheets("PlanningSystemData").Select 'Format Order quantity(1000's)
Columns("O:O").Select
Selection.Style = "Comma"
    Selection.NumberFormat = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-"

With ThisWorkbook.Worksheets("PlanningSystemData").Range("O1")
     .Value = "Order quantity(1000's)" 'Create new column called "Order quantity(1000's)"
     .Font.Bold = True 'Order quantity(1000's) formatting - bold
     .Font.Size = 9 'Order quantity(1000's) formatting - font size
     .Font.Name = "Arial" 'Order quantity(1000's) formatting - font family
 _
     .Borders(xlEdgeBottom) _
     .LineStyle = XlLineStyle.xlContinuous 'Order quantity(1000's) formatting - Border bottom added
End With

'Order quantity(1000's) END

'Paper delivery date START

Sheets("PlanningSystemData").Select 'Format Paper delivery date to correct format
    Columns("X:X").Select
    Selection.TextToColumns Destination:=Range("X1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
lastRowPaperDeliveryDate = Range("X1").End(xlDown).Row
        
Sheets("PlanningSystemData").Range("X2:X" & lastRowPaperDeliveryDate).NumberFormat = "dd/mm/yyyy" 'change to day/month/year

'Paper delivery date END

'Foil delivery date START

Sheets("PlanningSystemData").Select 'Forcmat Foil delivery date to correct format
    Columns("AA:AA").Select
    Selection.TextToColumns Destination:=Range("AA1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
lastRowFoilDeliveryDate = Range("AA1").End(xlDown).Row
        
Sheets("PlanningSystemData").Range("AA2:AA" & lastRowFoilDeliveryDate).NumberFormat = "dd/mm/yyyy" 'change to day/month/year

'Foil delivery date END

Sheets("PlanningSystemData").Name = "PlanningSystemDataLookup"

Call TurnOnCode 'Sets calculation to automatic, screenupdats + events + animation + status bar + print coms is all turned on

Debug.Print "RunTime for RemodelPSD is: " & Format((Timer - StartTime) / 86400, "hh:mm:ss.ms") 'Print out runtime for subroutine

End Sub

Sub CreatePOFile() 'Create PO File - Used to create PSL PO worksheet

Dim StartTime As Double 'Declaration of timer to identify length of runtime
StartTime = Timer

Call TurnOffCode 'Sets calculation to manual, screenupdats + events + animation + status bar + print coms is all turned off

Sheets("ShippingCalendarData").Name = "PlanningOutlookFile" 'Change worksheet name from "ShippingCalendarData" to "PlanningOutlookFile"
            
With ThisWorkbook.Worksheets("PlanningOutlookFile").Range("A1")
     .Value = "Customer" 'Formatting - capitalization
     .Range("B1").Value = "Customer Description" 'Formatting - capitalization
     .Range("C1").Value = "Delivery Date" 'Formatting - capitalization
     .Range("D1").Value = "Roc ID" 'Formatting - capitalization
     .Range("E1").Value = "Item Code" 'Formatting - capitalization
     .Range("F1").Value = "Item Reference" 'Formatting - capitalization
     .Range("G1").Value = "Item Description" 'Formatting - capitalization
     .Range("H1").Value = "Quantity" 'Formatting - capitalization
     .Range("I1").Value = "Order Year" 'Formatting - capitalization
     .Range("J1").Value = "Order Number" 'Formatting - capitalization
     .Range("K1").Value = "Job Ticket Year" 'Formatting - capitalization
     .Range("L1").Value = "Job Ticket Number" 'Formatting - capitalization
End With

'JT Key START

lastRowJT1 = Range("M1").End(xlDown).Row

Sheets("PlanningOutlookFile").Range("M2:M" & lastRowJT1).Formula = "=CONCATENATE(K2,L2)" 'Perform formula

With ThisWorkbook.Worksheets("PlanningOutlookFile").Range("M1")
     .Value = "JT Key" 'Create new column called "JT Key"
     .Font.Bold = True 'JT Key formatting - bold
     .Font.Size = 9 'JT Key formatting - font size
     .Font.Name = "Arial" 'JT Key formatting - font family
 _
     .Borders(xlEdgeBottom) _
     .LineStyle = XlLineStyle.xlContinuous 'JT Key formatting - Border bottom added
End With
            
'JT Key END

'Scheduling date START
            
lastRowSchedulingDate = Range("N1").End(xlDown).Row

Sheets("PlanningOutlookFile").Range("N2:N" & lastRowSchedulingDate).Formula = "=IFERROR(VLOOKUP(M2,PlanningSystemDataLookup!A:G,7,0),""Printed"")" 'Perform formula

With ThisWorkbook.Worksheets("PlanningOutlookFile").Range("N1")
     .Value = "Scheduling Date" 'Create new column called "Scheduling Date"
     .Font.Bold = True 'Scheduling Date formatting - bold
     .Font.Size = 9 'Scheduling Date formatting - font size
     .Font.Name = "Arial" 'Scheduling Date formatting - font family
 _
     .Borders(xlEdgeBottom) _
     .LineStyle = XlLineStyle.xlContinuous 'Scheduling Date formatting - Border bottom added
End With
            
Sheets("PlanningOutlookFile").Select 'Format Scheduling date to correct format
    Columns("N:N").Select
    Selection.TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
lastRowSchedulingDate = Range("N1").End(xlDown).Row
        
Sheets("PlanningOutlookFile").Range("N2:N" & lastRowSchedulingDate).NumberFormat = "dd/mm/yyyy" 'change to day/month/year

'Scheduling date END

'Scheduling Time START
            
lastRowSchedulingTime = Range("O1").End(xlDown).Row

Sheets("PlanningOutlookFile").Range("O2:O" & lastRowSchedulingTime).Formula = "=IFERROR(VLOOKUP(M2,PlanningSystemDataLookup!A:H,8,0),"" "")" 'Perform formula

With ThisWorkbook.Worksheets("PlanningOutlookFile").Range("O1")
     .Value = "Scheduling Time" 'Create new column called "Scheduling Time"
     .Font.Bold = True 'Scheduling Time formatting - bold
     .Font.Size = 9 'Scheduling Time formatting - font size
     .Font.Name = "Arial" 'Scheduling Time formatting - font family
 _
     .Borders(xlEdgeBottom) _
     .LineStyle = XlLineStyle.xlContinuous 'Scheduling Time formatting - Border bottom added
End With

'Scheduling Time END

'Order Sheets / LM START
            
lastRowSheets = Range("P1").End(xlDown).Row

Sheets("PlanningOutlookFile").Range("P2:P" & lastRowSheets).Formula = "=IFERROR(VLOOKUP(M2,PlanningSystemDataLookup!A:Q,15,0),"" "")" 'Perform formula

Sheets("PlanningOutlookFile").Select 'Format Sheets / LM
Columns("P:P").Select
Selection.Style = "Comma"
    Selection.NumberFormat = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-"

With ThisWorkbook.Worksheets("PlanningOutlookFile").Range("P1")
     .Value = "Sheets / LM" 'Create new column called "Sheets / LM"
     .Font.Bold = True 'Sheets / LM formatting - bold
     .Font.Size = 9 'Sheets / LM formatting - font size
     .Font.Name = "Arial" 'Sheets / LM formatting - font family
 _
     .Borders(xlEdgeBottom) _
     .LineStyle = XlLineStyle.xlContinuous 'Sheets / LM formatting - Border bottom added
End With

'Sheets / LM END

'Print Hours START

lastRowPrintHours = Range("Q1").End(xlDown).Row

Sheets("PlanningOutlookFile").Range("Q2:Q" & lastRowPrintHours).Formula = "=IFERROR(VLOOKUP(M2,PlanningSystemDataLookup!A:T,20,0),"" "")" 'Perform formula

With ThisWorkbook.Worksheets("PlanningOutlookFile").Range("Q1")
     .Value = "Print Hours" 'Create new column called "Print Hours"
     .Font.Bold = True 'Print Hours formatting - bold
     .Font.Size = 9 'Print Hours formatting - font size
     .Font.Name = "Arial" 'Print Hours formatting - font family
 _
     .Borders(xlEdgeBottom) _
     .LineStyle = XlLineStyle.xlContinuous 'Print Hours formatting - Border bottom added
End With

'Print Hours END

lastRowPress = Range("R1").End(xlDown).Row

Sheets("PlanningOutlookFile").Range("R2:R" & lastRowPress).Formula = "=IFERROR(VLOOKUP(M2,PlanningSystemDataLookup!A:C,3,0),"" "")" 'Perform formula

With ThisWorkbook.Worksheets("PlanningOutlookFile").Range("R1")
     .Value = "Press" 'Create new column called "Press"
     .Font.Bold = True 'Press formatting - bold
     .Font.Size = 9 'Press formatting - font size
     .Font.Name = "Arial" 'Press formatting - font family
 _
     .Borders(xlEdgeBottom) _
     .LineStyle = XlLineStyle.xlContinuous 'Press formatting - Border bottom added
End With

'Press END

'JT Quantity START

lastRowJTQuantity = Range("S1").End(xlDown).Row

Sheets("PlanningOutlookFile").Range("S2:S" & lastRowJTQuantity).Formula = "=IFERROR(VLOOKUP(M2,PlanningSystemDataLookup!A:O,15,0),"" "")" 'Perform formula

Sheets("PlanningOutlookFile").Select 'Format JT Quantity
Columns("S:S").Select
Selection.Style = "Comma"
    Selection.NumberFormat = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-"

With ThisWorkbook.Worksheets("PlanningOutlookFile").Range("S1")
     .Value = "JT Quantity" 'Create new column called "JT Quantity"
     .Font.Bold = True 'JT Quantity formatting - bold
     .Font.Size = 9 'JT Quantity formatting - font size
     .Font.Name = "Arial" 'JT Quantity formatting - font family
 _
     .Borders(xlEdgeBottom) _
     .LineStyle = XlLineStyle.xlContinuous 'JT Quantity formatting - Border bottom added
End With

'JT Quantity END

'Paper 1 code START

lastRowPaper1code = Range("T1").End(xlDown).Row

Sheets("PlanningOutlookFile").Range("T2:T" & lastRowPaper1code).Formula = "=IFERROR(VLOOKUP(M2,PlanningSystemDataLookup!A:V,22,0),"" "")" 'Perform formula

With ThisWorkbook.Worksheets("PlanningOutlookFile").Range("T1")
     .Value = "Paper 1 code" 'Create new column called "Paper 1 code"
     .Font.Bold = True 'Paper 1 code formatting - bold
     .Font.Size = 9 'Paper 1 code formatting - font size
     .Font.Name = "Arial" 'Paper 1 code formatting - font family
 _
     .Borders(xlEdgeBottom) _
     .LineStyle = XlLineStyle.xlContinuous 'Paper 1 code formatting - Border bottom added
End With

'Paper 1 code END

'Paper 1 description START

lastRowPaper1description = Range("U1").End(xlDown).Row

Sheets("PlanningOutlookFile").Range("U2:U" & lastRowPaper1description).Formula = "=IFERROR(VLOOKUP(M2,PlanningSystemDataLookup!A:W,23,0),"" "")" 'Perform formula

With ThisWorkbook.Worksheets("PlanningOutlookFile").Range("U1")
     .Value = "Paper 1 description" 'Create new column called "Paper 1 description"
     .Font.Bold = True 'Paper 1 description formatting - bold
     .Font.Size = 9 'Paper 1 description formatting - font size
     .Font.Name = "Arial" 'Paper 1 description formatting - font family
 _
     .Borders(xlEdgeBottom) _
     .LineStyle = XlLineStyle.xlContinuous 'Paper 1 description formatting - Border bottom added
End With

'Paper 1 description END

'Paper deliver date START
            
lastRowPaperDeliverDate = Range("V1").End(xlDown).Row

Sheets("PlanningOutlookFile").Range("V2:V" & lastRowPaperDeliverDate).Formula = "=IFERROR(VLOOKUP(M2,PlanningSystemDataLookup!A:X,24,0),"" "")" 'Perform formula

With ThisWorkbook.Worksheets("PlanningOutlookFile").Range("V1")
     .Value = "Paper deliver date" 'Create new column called "Paper deliver date"
     .Font.Bold = True 'Paper deliver date formatting - bold
     .Font.Size = 9 'Paper deliver date formatting - font size
     .Font.Name = "Arial" 'Paper deliver date formatting - font family
 _
     .Borders(xlEdgeBottom) _
     .LineStyle = XlLineStyle.xlContinuous 'Paper deliver date formatting - Border bottom added
End With
            
Sheets("PlanningOutlookFile").Select 'Paper deliver date to correct format
    Columns("V:V").Select
    Selection.TextToColumns Destination:=Range("V1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
lastRowPaperDeliverDate = Range("V1").End(xlDown).Row
        
Sheets("PlanningOutlookFile").Range("V2:V" & lastRowPaperDeliverDate).NumberFormat = "dd/mm/yyyy" 'change to day/month/year

'Paper deliver date END

'Foil 1 code START

lastRowFoil1Code = Range("W1").End(xlDown).Row

Sheets("PlanningOutlookFile").Range("W2:W" & lastRowFoil1Code).Formula = "=IFERROR(VLOOKUP(M2,PlanningSystemDataLookup!A:Y,25,0),"" "")" 'Perform formula

With ThisWorkbook.Worksheets("PlanningOutlookFile").Range("W1")
     .Value = "Foil 1 Code" 'Create new column called "Foil 1 Code"
     .Font.Bold = True 'Foil 1 Code formatting - bold
     .Font.Size = 9 'Foil 1 Code formatting - font size
     .Font.Name = "Arial" 'Foil 1 Code formatting - font family
 _
     .Borders(xlEdgeBottom) _
     .LineStyle = XlLineStyle.xlContinuous 'Foil 1 Code formatting - Border bottom added
End With

'Foil 1 code END

'Foil 1 description START

lastRowFoil1Description = Range("X1").End(xlDown).Row

Sheets("PlanningOutlookFile").Range("X2:X" & lastRowFoil1Description).Formula = "=IFERROR(VLOOKUP(M2,PlanningSystemDataLookup!A:Z,26,0),"" "")" 'Perform formula

With ThisWorkbook.Worksheets("PlanningOutlookFile").Range("X1")
     .Value = "Foil 1 Description" 'Create new column called "Foil 1 Description"
     .Font.Bold = True 'Foil 1 Description formatting - bold
     .Font.Size = 9 'Foil 1 Description formatting - font size
     .Font.Name = "Arial" 'Foil 1 Description formatting - font family
 _
     .Borders(xlEdgeBottom) _
     .LineStyle = XlLineStyle.xlContinuous 'Foil 1 Description formatting - Border bottom added
End With

'Foil 1 description END

'Foil delivery date START

lastRowFoilDeliverDate = Range("Y1").End(xlDown).Row

Sheets("PlanningOutlookFile").Range("Y2:Y" & lastRowFoilDeliverDate).Formula = "=IFERROR(VLOOKUP(M2,PlanningSystemDataLookup!A:AA,27,0),"" "")" 'Perform formula

With ThisWorkbook.Worksheets("PlanningOutlookFile").Range("Y1")
     .Value = "Foil deliver date" 'Create new column called "Foil deliver date"
     .Font.Bold = True 'Foil deliver date formatting - bold
     .Font.Size = 9 'Foil deliver date formatting - font size
     .Font.Name = "Arial" 'Foil deliver date formatting - font family
 _
     .Borders(xlEdgeBottom) _
     .LineStyle = XlLineStyle.xlContinuous 'Foil deliver date formatting - Border bottom added
End With
            
Sheets("PlanningOutlookFile").Select 'Foil deliver date to correct format
    Columns("Y:Y").Select
    Selection.TextToColumns Destination:=Range("Y1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
lastRowFoilDeliverDate = Range("Y1").End(xlDown).Row
        
Sheets("PlanningOutlookFile").Range("Y2:Y" & lastRowFoilDeliverDate).NumberFormat = "dd/mm/yyyy" 'change to day/month/year

'Foil delivery date END

'GAP START

lastRowGAP = Range("Z1").End(xlDown).Row

Sheets("PlanningOutlookFile").Range("Z2:Z" & lastRowGAP).Formula = "=C2-N2"

With ThisWorkbook.Worksheets("PlanningOutlookFile").Range("Z1")
     .Value = "GAP" 'Create new column called "GAP"
     .Font.Bold = True 'GAP formatting - bold
     .Font.Size = 9 'GAP formatting - font size
     .Font.Name = "Arial" 'GAP formatting - font family
 _
     .Borders(xlEdgeBottom) _
     .LineStyle = XlLineStyle.xlContinuous 'GAP formatting - Border bottom added
End With

'GAP END

'Delivery Comment START

lastRowDeliveryComment = Range("AA1").End(xlDown).Row

Sheets("PlanningOutlookFile").Range("AA2:AA" & lastRowDeliveryComment).Formula = "=IF(Z2<=0,""DELIVERY FAIL"",""PRINTING OK"")" 'Perform formula

With ThisWorkbook.Worksheets("PlanningOutlookFile").Range("AA1")
     .Value = "Delivery Comment" 'Create new column called "Delivery Comment"
     .Font.Bold = True 'Delivery Comment formatting - bold
     .Font.Size = 9 'Delivery Comment formatting - font size
     .Font.Name = "Arial" 'Delivery Comment formatting - font family
 _
     .Borders(xlEdgeBottom) _
     .LineStyle = XlLineStyle.xlContinuous 'Delivery Comment formatting - Border bottom added
End With

'Delivery Comment END

'Material Comment START

lastRowMaterialComment = Range("AB1").End(xlDown).Row

Sheets("PlanningOutlookFile").Range("AB2:AB" & lastRowMaterialComment).Formula = "=IF(OR(V2>N2,Y2>N2),""CHECK"", ""Material OK"")" 'Perform formula

With ThisWorkbook.Worksheets("PlanningOutlookFile").Range("AB1")
     .Value = "Material Comment" 'Create new column called "Material Comment"
     .Font.Bold = True 'Material Comment formatting - bold
     .Font.Size = 9 'Material Comment formatting - font size
     .Font.Name = "Arial" 'Material Comment formatting - font family
 _
     .Borders(xlEdgeBottom) _
     .LineStyle = XlLineStyle.xlContinuous 'Material Comment formatting - Border bottom added
End With

'Material Comment END

Call TurnOnCode 'Sets calculation to automatic, screenupdats + events + animation + status bar + print coms is all turned on

Debug.Print "RunTime for CreatePOFile is: " & Format((Timer - StartTime) / 86400, "hh:mm:ss.ms") 'Print out runtime for subroutine

End Sub

 

[EuanM28]

Its pretty much impossible to suggest why if we cant see the code.

Unsure about that, but I have added in the code, I did not know if I should add it in as this is my first question and the code is incredibly long. The code I have added is the 3rd n 4th subroutine. From CreatePOFile onwards, the VLOOKUPS from there sometimes show the results and sometimes do not (blank cells)

 

[steve the fish]

You have quite a lot of unqualified range references. eg:

lastRowJT1 = Range("M1").End(xlDown).Row

That will happen on the activesheet at the time so can obv produce variability. Qualify those ranges with a sheet.

 

[EuanM28]

You have quite a lot of unqualified range references. eg:

lastRowJT1 = Range("M1").End(xlDown).Row

That will happen on the activesheet at the time so can obv produce variability. Qualify those ranges with a sheet.

Yes, that is because this project imports worksheets where the columns range is never the same (everyday the files are downloaded, imported, so the range changes)

 

[steve the fish]

Say that again? I didnt understand that explanation. The following 3 lines together make no sense. Add a sheet reference to that range then try it again.

Sheets("PlanningSystemData").Columns("A:A").Insert Shift:=xlToRight 'New column for JT Key

lastRowJT2 = Range("A1").End(xlDown).Row 'Select all of row

Sheets("PlanningSystemData").Range("A2:A" & lastRowJT2).Formula = "=CONCATENATE(D2,E2)"

 

[EuanM28]

Say that again? I didnt understand that explanation. The following 3 lines together make no sense. Add a sheet reference to that range then try it again.

Sheets("PlanningSystemData").Columns("A:A").Insert Shift:=xlToRight 'New column for JT Key

lastRowJT2 = Range("A1").End(xlDown).Row 'Select all of row

Sheets("PlanningSystemData").Range("A2:A" & lastRowJT2).Formula = "=CONCATENATE(D2,E2)"

I am saying if I specify a range, for example A2:A676, then this would work IF my imported sheets had that specific range, but one day that range could be greater than 676. hence I have the second line of code. You are correct though, I should reference the sheet in that line. Ill let you know.

 

[EuanM28]

Say that again? I didnt understand that explanation. The following 3 lines together make no sense. Add a sheet reference to that range then try it again.

Sheets("PlanningSystemData").Columns("A:A").Insert Shift:=xlToRight 'New column for JT Key

lastRowJT2 = Range("A1").End(xlDown).Row 'Select all of row

Sheets("PlanningSystemData").Range("A2:A" & lastRowJT2).Formula = "=CONCATENATE(D2,E2)"

Awh that worked! Thank you so much. Why do you think it worked only some of the time beforehand?

 

[steve the fish]

Like i said if you just use Range (rather than for example Sheets("Sheet1").Range) then excel will use the active sheet at the time the line with Range executes. That may or may not be the sheet you need it to be. It can be variable depending on what sheet you was on when it all started. If you use buttons though they always execute from the same starting point so you wouldnt have experienced the same variability.