Last Row LR8 = Sheets("Cost Sources").Cells(Rows.Count, "A").End(xlUp).Row?

[gheyman]

This code is should only be loading data in column AE down to Row 3 (when I ran the code there was only data in column A in row 3 - but its pasting the data in Range B in rows 3 and 4)

Here is the two lines in question

LR8 = Sheets("Cost Sources").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Cost Sources").Range("AE3:AE" & LR8).Value = Range("B1")

Sub CostSources_Template()
'
    Application.ScreenUpdating = False
    
'********************
'Run Query Refresh
'*******************

Sheets("PQ Cost Source").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False

'
' Clear Tem_Table
'
    Dim CLR2 As Long
'
        CLR2 = Sheets("Tempory Table CS").Cells(Rows.Count, "A").End(xlUp).Row
        If CLR2 > 2 Then Sheets("Tempory Table CS").Range("A2:BH" & CLR2).ClearContents
'
'Move data from PQ Cost Source
         Sheets("PQ Cost Source").ListObjects("PQ_Cost_Source").DataBodyRange.Copy Sheets("Tempory Table CS").Range("A2")
'
'Remove Duplicates in the PPMaterialName Column, MaterialCostSourceName Column and the Revision Column
    Sheets("Tempory Table CS").Select
    ActiveSheet.Range("Temp_TableCS[#All]").RemoveDuplicates Columns:=Array(30, 33, 35), _
        Header:=xlYes
'
'******************************
'******************************
'**Move the data to PP Template
'******************************
'******************************
'
'*********************************************
'Cost Sources gets loaded to a Product Library
'**********************************************
'
'FIRST CHECK THAT THERE IS A VALID PRODUCT LIBRARY SELECTED
'
If Sheets("Selected Product Library").Range("A2").Value = "" Then
        MsgBox "You must go to Step 2 and either select an existing Product Library or Create a New Product Library."
        Exit Sub

Else


'Unhide Template
        Sheets("Cost Sources").Visible = True

'First clear any exisiting data on the Templare

'
        Dim CLR8 As Long
        Dim LR8 As Long
'
        'Clear existing data if any on Materials Input
        Sheets("Cost Sources").Select
        CLR8 = Sheets("Cost Sources").Cells(Rows.Count, "A").End(xlUp).Row
        If CLR8 > 2 Then Sheets("Cost Sources").Range("A3:AD" & CLR8).ClearContents
'
'Move Data from PQ Material Assoc Costs to Material Assoc Costs
'
    Sheets("Cost Sources").Range("B1").Value = Sheets("Selected Product Library").Range("A2")
'
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("PPMaterialName").DataBodyRange.Copy Sheets("Cost Sources").Range("A3")
        LR8 = Sheets("Cost Sources").Cells(Rows.Count, "A").End(xlUp).Row
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("Material Type").DataBodyRange.Copy Sheets("Cost Sources").Range("B3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("Category").DataBodyRange.Copy Sheets("Cost Sources").Range("C3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("MaterialCostSourceName").DataBodyRange.Copy Sheets("Cost Sources").Range("D3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("Revision").DataBodyRange.Copy Sheets("Cost Sources").Range("E3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("From Date").DataBodyRange.Copy Sheets("Cost Sources").Range("F3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("To Date").DataBodyRange.Copy Sheets("Cost Sources").Range("G3")
    Sheets("Cost Sources").Range("H3:H" & LR8).Value = Sheets("Selected Product Library").Range("A2")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("Vendor Name").DataBodyRange.Copy Sheets("Cost Sources").Range("I3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("LeadTime").DataBodyRange.Copy Sheets("Cost Sources").Range("J3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("MinBuyQty").DataBodyRange.Copy Sheets("Cost Sources").Range("K3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("MaterialCostSourceComments").DataBodyRange.Copy Sheets("Cost Sources").Range("L3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("EscalationBaseDate").DataBodyRange.Copy Sheets("Cost Sources").Range("M3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("EscalationID").DataBodyRange.Copy Sheets("Cost Sources").Range("N3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("Subcatagory").DataBodyRange.Copy Sheets("Cost Sources").Range("P3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("Created by").DataBodyRange.Copy Sheets("Cost Sources").Range("W3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("Created").DataBodyRange.Copy Sheets("Cost Sources").Range("X3")
    Sheets("Cost Sources").Range("X3:X" & LR8).NumberFormat = "m/d/yyyy"
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("[MF] VendorID").DataBodyRange.Copy Sheets("Cost Sources").Range("Y3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("Path/Location").DataBodyRange.Copy Sheets("Cost Sources").Range("AC3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("[MF] Vendor Quote ID").DataBodyRange.Copy Sheets("Cost Sources").Range("AD3")
    Sheets("Cost Sources").Range("AE3:AE" & LR8).Value = Range("B1")
'
'
End If
End Sub

Sub CostSourceDetails_Template()
'
    Application.ScreenUpdating = False

'*************************************************
'Qury Refreshed in CostSources_Template step above
'*************************************************

Sheets("PQ Cost Source").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False


' Clear Tem_Table
'
    Dim CLR2 As Long
'
        CLR2 = Sheets("Tempory Table CS").Cells(Rows.Count, "A").End(xlUp).Row
        If CLR2 > 2 Then Sheets("Tempory Table CS").Range("A2:BM" & CLR2).ClearContents
'
'Move data from PQ Cost Source
         Sheets("PQ Cost Source").ListObjects("PQ_Cost_Source").DataBodyRange.Copy Sheets("Tempory Table CS").Range("A2")
'
'Remove Duplicates in the PPMaterialName Column, MaterialCostSourceName Column and the Revision Column
    Sheets("Tempory Table CS").Select
    ActiveSheet.Range("Temp_TableCS[#All]").RemoveDuplicates Columns:=Array(30, 33, 35, 36, 37, 40, 50, 51, 52, 54, 55, 56), _
        Header:=xlYes
'
'******************************
'******************************
'**Move the data to PP Template
'******************************
'******************************
'
        
'**COST SOURCE DETAILS

'Unhide Template
Sheets("Cost Source Details").Visible = True

'First clear any exisiting data on the Templare

Application.ScreenUpdating = False

        Dim CLR9 As Long
        Dim LR9 As Long

        'Clear existing data if any on Materials Input
        Sheets("Cost Source Details").Select
            CLR9 = Sheets("Cost Source Details").Cells(Rows.Count, "A").End(xlUp).Row
            If CLR9 > 2 Then Sheets("Cost Source Details").Range("A3:I" & CLR9).ClearContents

'Move Data from PQ Material Assoc Costs to Material Assoc Costs
    
    Sheets("Cost Source Details").Range("B1").Value = Sheets("Selected Product Library").Range("A2")
'
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("PPMaterialName").DataBodyRange.Copy Sheets("Cost Source Details").Range("A3")
        LR9 = Sheets("Cost Source Details").Cells(Rows.Count, "A").End(xlUp).Row
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("Material Type").DataBodyRange.Copy Sheets("Cost Source Details").Range("B3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("Category").DataBodyRange.Copy Sheets("Cost Source Details").Range("C3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("MaterialCostSourceName").DataBodyRange.Copy Sheets("Cost Source Details").Range("D3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("Revision").DataBodyRange.Copy Sheets("Cost Source Details").Range("E3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("FromQty").DataBodyRange.Copy Sheets("Cost Source Details").Range("F3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("ToQty").DataBodyRange.Copy Sheets("Cost Source Details").Range("G3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("UnitCost").DataBodyRange.Copy Sheets("Cost Source Details").Range("H3")
    Sheets("Tempory Table CS").ListObjects("Temp_TableCS").ListColumns("MaterialCostSourceDetailCommentsALL").DataBodyRange.Copy Sheets("Cost Source Details").Range("I3")
'
'
        
End Sub

Why does this think row 4 is the Last Row (LR9)?
Any help is appreciated.

 

[Joe4]

If you add this line of code, what does the Message Box return?

LR8 = Sheets("Cost Sources").Cells(Rows.Count, "A").End(xlUp).Row
MsgBox LR8