VBA export data to csv, copy and append to another sheet, getting error 400

[maurig]

Dear all,
I'm pretty new to VBA so sorry if my questions is really but I'm not able to figure it out.
I have an excel workbook with two sheets (input, opp_list). I need to:
1) export data from "input" to csv
2) copy data from "input" and paste values (with append after the last row with not-empty) into "opp_list"
3) clear some specific columns from "opp_list"

The script below works until it start copying data to "opp_list". There I receive the error 400. I dont'understand why.

"opp_list" have one column more than "input", the first one. It is a sort of id. The rest of columns are equal.
"input" : A to G
"opp_list": A to F (equal to B to G in "input")

The columns in common have same formatting. The macro is executed from "input", after users enter data. On click it data should be saved as csv and then copied to "opp_list" and at the end data in 2 columns from "input" must be cleared.

Sub ExportAsCSV()
 
'Export csv
    Dim MyFileName As String
    Dim CurrentWB As Workbook, TempWB As Workbook
     
    Set CurrentWB = ActiveWorkbook
    ActiveWorkbook.ActiveSheet.Range("C2:H30").Copy
 
    Set TempWB = Application.Workbooks.Add(1)
    With TempWB.Sheets(1).Range("B3")
      .PasteSpecial xlPasteValues
      .PasteSpecial xlPasteFormats
    End With

    Dim Change As String
    MyFileName = CurrentWB.Path & "\" & Left(CurrentWB.Name, Len(CurrentWB.Name) - 5) & ".csv"
     
    Application.DisplayAlerts = False
    TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=False
    TempWB.Close SaveChanges:=False
    Application.DisplayAlerts = True
    
    
'Copy to another Sheet
    Sheets("input").Range("A2:G21").Select
    Selection.Copy
    Sheets("opp_list").Select
    Range("A2").Select
    
    Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select

    Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False

' Go to Input sheet and clear entry
    Sheets("input").Select
    Range("A2:A40").Select
    Selection.ClearContents
    Range("U2:U40").Select
    Selection.ClearContents
    

End Sub

I would like to understand how to overcome this error.
Also I'm trying to make all the ranges dynamic, considering only the rows not-empty. I've tried when copying data but I would like to do the same when selecting the range of cell to export to csv and when clearing the output from "opp_list".

Do you have any suggestions? Thank you very much.

 

[Alex Blakenburg]

Someone else may be able to explain why but if you change the 2nd line of the below from A2 to A1 it works.
I have tested various paste options (PasteValues & PasteFormulas) but out of the ones I tried, using A2 is only crashing the xlPasteAllExceptBorders option.
Changing the A2 to A1 seems to fix it and still seems to work fine.

    Sheets("opp_list").Select
    Range("A1").Select

 

[maurig]

Thank you Alex for your answer. I'm still getting the error. I've tried many times. Now, I'm trying rebuilding the procedure from scratch using different syntax. Let's see if I figure it out using a different approach.

 

[Alex Blakenburg]

Are you by any chance saving to a OneDrive or Sharepoint folder ?
I don't get error 400 but the macro seems to terminate without any error message as soon as its run the Saveas csv line.

 

[maurig]

Are you by any chance saving to a OneDrive or Sharepoint folder ?
I don't get error 400 but the macro seems to terminate without any error message as soon as its run the Saveas csv line.

No I'm working on my own laptop, working on Office 2016 and saving on the hard drive. When completed the script should run under office 365. However, I'm now trying another way putting the two sheets in one sheet and inserting new rows at the bottom.... I no longer use two different sheets..... and obviosuly now I'm having other problems :D

I will open another thread to discuss about them but thak you very much for your prompt reply.