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

maurig

New Member
Joined
Mar 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
628
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
    Sheets("opp_list").Select
    Range("A1").Select
 

maurig

New Member
Joined
Mar 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
628
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,333
Messages
5,641,550
Members
417,220
Latest member
lam150498

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top