Paste Open Workbook Range to New Open Workbook - "Subscrip out of range error"

[Shiseiji]

Am trying two things. Copy values into a new workbook that's already saved as a .cvs. Had problem with saving the original xlsx workbook as .cvs and the worksheet filling up with #NULL .
I have the code running until I want to paste the values into the new .csv workbook and am receiving a error 91 Subscrip out of range.

Thanks in Advance for any help.

Ron

Sub tm_AtHoc_CEDR_EmployeeRegionRpt()
' .xlsx files saved as a .csv for this have #NULL values
' 3/21/2022
' Can't copy named range into new workbook. Error 91 Subscrip out of range.
' 3/15/2022 added code to remove #NULL cells - couldn't make it work
' https://excel.tips.net/T003068_Inconsistent_Output_for_Empty_Columns_in_a_CSV_File.html
' Copies the source file worksheet to a new workbook. 
'

    With Application
      .ScreenUpdating = False
      .EnableEvents = True
      .DisplayAlerts = False
      .Calculation = xlManual
    End With ' Application
    Dim LastCol   As Integer
    Dim i         As Long
    Dim LastRow   As Long
    Dim CopyRng   As Range
    Dim oneCell   As Range
    Dim Rng       As Range
    Dim wbPath    As String
    Dim varPath   As String
    Dim fPath     As String
    Dim DestWb    As Workbook
    Dim SourceWb  As Workbook
    Dim ThisWb    As Workbook
    Dim SourceWs  As Worksheet
    Dim ThisWs    As Worksheet
    Set SourceWb = ActiveWorkbook
    Set SourceWs = ActiveSheet

    With SourceWb
    With SourceWs
    Cells.UnMerge
    Range("A1").EntireRow.Delete
    Columns("D:H").Delete
    LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Range("A1", Cells(LastRow, LastCol)).Name = "CopyRng"
    Range("CopyRng").Copy ' range is valid $A$1:$C$15843
'---Add a workbook and save as a .cvs file
    Set DestWb = Workbooks.Add
    DestWb.SaveAs "S:\Security (Restricted)\Emergency Management Programs\Operations\AtHoc\Files_for_Upload\CEDR_Data\CEDR_Data_" _
& Format(Now, "mm-dd-yyyy hh mm AM/PM") & ".csv", FileFormat:=6 ' valid
'=== Testing ===
    'https://www.mrexcel.com/board/threads/paste-as-values.1197811/
     DestWb.Worksheets("Sheet1").Range("A1").Value = SourceWs.Range("CopyRng").Value 'Subscrip out of range
'-- more code--

 

[Joe4]

CSV files are flat files that cannot have multiple sheets. So once you save a file as a CSV file, "Sheet1" no longer exists (at least not under that name - if you open up the file, it will give the sheet a name that matches the file name).

But since it doesn't have multiple sheets, you should no longer have to specify the sheet name.
What happens if you just change this line:

     DestWb.Worksheets("Sheet1").Range("A1").Value = SourceWs.Range("CopyRng").Value

to this:

     DestWb.Activate
     Range("A1").Value = SourceWs.Range("CopyRng").Value

 

[Shiseiji]

Sadly now now rendering error 431

DestWb.Activate
     DestWb.Range("A1").Value = SourceWs.Range("CopyRng").Value 'Object doesn't support this property or method

 

[Joe4]

That is not what I posted.
Please remove the "DestWb" before the "Range..." and see if that makes any difference.

 

[Shiseiji]

Please accept my apology. Worked perfect!
Two bad tooth extractions, poor sleep for two weeks; mental state is not top notch right now. Should have done copy/paste to prevent just this error.

[Edit] Thank you for the extra explanation. Much of my work is with flat files so this is a really good thing to add to my list of "How to."

 

[Joe4]

You are welcome.