Select Method of Range Class Failed

Hi,

I'm trying to write a code such that when the user presses the button GRAPH, it graphs the data in the file for each column
Capture.PNG

I run into a debug error on this line:

Range(Cells(1, NCol), Cells(LastRow, NCol)).Select

Full code:

Private Sub cmdGraph_Click()

    gFolderpath.Text = chkLastChar(gFolderpath.Text)
    ChDir gFolderpath.Text
    Workbooks.Open Filename:=gFolderpath.Text & gFilename
    
    
    Dim nLeft As Long, nTop As Long
    Dim strChrt As String
    
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
        
    nLeft = 20: nTop = 0
        
    For NCol = 1 To LastCol
            
        Range(Cells(1, NCol), Cells(LastRow, NCol)).Select
        ActiveSheet.Shapes.AddChart2(227, xlLine).Select
        ActiveChart.SetSourceData Source:=Range(Cells(1, NCol), Cells(LastRow, NCol))
        
        strChrt = Trim(Replace(ActiveChart.Name, ActiveSheet.Name, ""))
            
        ActiveSheet.Shapes(strChrt).Left = nLeft
        ActiveSheet.Shapes(strChrt).Top = nTop
            
        '~~> Increment the next `Top` placement for the chart
        nTop = nTop + ActiveSheet.Shapes(strChrt).Height + 20
            
    Next NCol
    
End Sub

It's weird because I wrote this code before and it worked before. Now I'm trying to modify it responds through a button on a form so that it is more user friendly.
This is my old code that worked. I can't seem to see the difference.

Sub MergeWorkbooks_V2()

    Dim NCol As Long
    Dim NRow As Long
    Dim LastRow As Long
    Dim LastCol As Long
    Dim NSheets As Long
    Dim LastSheet As Integer
    
    ' Modify this folder path to point to the files you want to use.
    FolderPath = "C:\Users\VCHU\Desktop\Sample_LTE_0021_3-1261\"
    FileName = Dir(FolderPath & "Sheet1.xlsx")

    ' Call Dir the first time, pointing it to all Excel files in the folder path.
    FileName = Dir(FolderPath & "Sheet1.xlsx")
    
    ' Open a workbook in the folder
    Set WorkBk = Workbooks.Open(FolderPath & FileName)
    
    ' Number of sheets
    LastSheet = 2
    
    Dim nLeft As Long, nTop As Long
    Dim strChrt As String
    
    For NSheets = 1 To LastSheet
        Sheets(NSheets).Select
        LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
        
        nLeft = 20: nTop = 0
        
        For NCol = 1 To LastCol
            
            Range(Cells(1, NCol), Cells(LastRow, NCol)).Select
            ActiveSheet.Shapes.AddChart2(227, xlLine).Select
            ActiveChart.SetSourceData Source:=Range(Cells(1, NCol), Cells(LastRow, NCol))
        
            strChrt = Trim(Replace(ActiveChart.Name, ActiveSheet.Name, ""))
            
            ActiveSheet.Shapes(strChrt).Left = nLeft
            ActiveSheet.Shapes(strChrt).Top = nTop
            
            '~~> Increment the next `Top` placement for the chart
            nTop = nTop + ActiveSheet.Shapes(strChrt).Height + 20
            
        Next NCol
    
    Next NSheets
    
End Sub

DataProcessing_Tool.xlsm

Do you need to select the range?

You specify the same range as the source for the chart a couple of lines down, here.

 ActiveChart.SetSourceData Source:=Range(Cells(1, NCol), Cells(LastRow, NCol))

I would have thought that was all you would need.

Any chance you could upload a sample workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

I think I know what the problem is.

When you refer to a range in a worksheet module without specifying explicity the sheet and workbook the range is from VBA uses the range on the worksheet who's module the code is in.

In this case the code is looking at Sheet1 in DataProcessing_Tool.xlsm, not the active sheet of DC Merged.xls.

Give this a try.

Private Sub cmdGraph_Click()
Dim wbData As Workbook
Dim nLeft As Long, nTop As Long
Dim strChrt As String

    gFolderpath.Text = chkLastChar(gFolderpath.Text)
    ChDir gFolderpath.Text
    Set wbData = Workbooks.Open(Filename:=gFolderpath.Text & gFilename.Text)

    With wbData.ActiveSheet

        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column

        nLeft = 20: nTop = 0

        For NCol = 1 To LastCol
            'Sheets(1).Select
            'Sheets(1).Range(Cells(1, NCol), Cells(LastRow, NCol)).Select
            .Shapes.AddChart(227, xlLine).Select
            .ChartObjects(NCol).Chart.SetSourceData Source:=.Range(.Cells(1, NCol), .Cells(LastRow, NCol))

            strChrt = Trim(Replace(.ChartObjects(NCol).Chart.Name, .Name, ""))

            .Shapes(strChrt).Left = nLeft
            .Shapes(strChrt).Top = nTop

            '~~> Increment the next `Top` placement for the chart
            nTop = nTop + .Shapes(strChrt).Height + 20


        Next NCol
    End With
    
End Sub

I get a debug error here: .Shapes(strChrt).Left = nLesft
The item with specified name wasn't found.

I do see a graph now. Thank you very much. The next part (trying to place it in specified location) is a bit bugged.

You mean when you run it manually it works but it doesn't work when you click the GRAPH button?

Strange, it works both ways for me with the sample file.