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.
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.
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.