Subscript out of range

Hello,

Need help to fix a code. I am trying to copy all the information from DFG, Geo tab of thisworkbook to Created workbook("DFG_" $environ("username"), doing so I am getting and subscript out of range error. I have underlined the code statement which seems to be incorrect.

Private Sub DFG_Exp_Click()
Application.FileDialog(msoFileDialogFolderPicker).InitialFileName = "I:\Analytical_Services\Public\"
FldrShow = Application.FileDialog(msoFileDialogFolderPicker).Show
If FldrShow <> 0 Then
    FldrSel = Trim(Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1))
    If Right(FldrSel, 1) = "\" Then
        FldrSel = Left(FldrSel, Len(FldrSel) - 1)
    End If
Set newbook = Workbooks.Add
    With newbook
    Worksheets.Add
       .Sheets.Add().Name = "Exception"
       .Sheets.Add().Name = "Geo"
       .Sheets.Add().Name = "DFG"
      
    
    newbook.SaveAs Filename:=FldrSel & "\" & "DFG_" & Environ$("UserName") & ".xls"

    End With
    DFG = "DFG_" & Environ$("UserName")
    row_cnt = ThisWorkbook.Sheets("DFG").UsedRange.Rows.Count
    ThisWorkbook.Sheets("DFG").Range(Cells(5, 1), Cells(row_cnt, 146)).Copy
    'ActiveSheet.Paste
    With Workbooks("DFG").Sheets("DFG").Range("A1")
        .PasteSpecial Paste:=xlPasteFormats
        .PasteSpecial Paste:=xlPasteValues
    End With
    
    row_cnt = ThisWorkbook.Sheets("Geo").UsedRange.Rows.Count
    ThisWorkbook.Sheets("Geo").Range(Cells(1, 1), Cells(row_cnt, 4)).Copy
    
    With Workbooks("DFG").Sheets("Geo").Range("A1")
    .PasteSpecial Paste:=xlPasteFormats
    .PasteSpecial Paste:=xlValues
    End With
End If
End Sub

It means the sheet names do not exist, you need to check the spelling, including spaces.

Sheet exist. I checked again looks fine to me.

If you are getting an error on this line

With Workbooks("DFG").Sheets("Geo").Range("A1")

It could be the Workbooks("DFG"). does not exist or it is not the active book( If you open two books at the same time), or the sheet name "Geo"with in sheet Workbooks("DFG") does not exist.

Thanks! It worked using Newbook .

Now I am getting Application Defined or object error in

row_cnt = ThisWorkbook.Sheets("Geo").UsedRange.Rows.Count
    ThisWorkbook.Sheets("Geo").Range(Cells(1, 1), Cells(row_cnt, 4)).Copy

Thanks for all your help Norie! It worked like a charm.

One more help, I am trying to attach the Exception_Template to "Exception" sheet of newfile. Getting an application or Object defined error. Am I going wrong somewhere.

ThisWorkbook.Sheets("Exception_Template").Visible = -1
         ThisWorkbook.Sheets("Exception_Template").Copy
        
    ThisWorkbook.Sheets("Exception_Template").Visible = 2
    
    newbook.Sheets("Exception").Paste

Any suggestions?

What i am trying to achieve here copy the exception_template worksheet and paste it in the New worksheet of workbook "Newbook" where other 2 worksheets is already added - DFG & Geo.

Thanks Again Norie, it worked perfectly!