0

I am new to excel macros. I have created this macro that transfer data to another excel file. However, after transferring the file it should clear the content from old sheet which I am not able to fix I get an error. Below is my code this will transfer data properly after that it stops and debugs at the below statement. Please help, after transferring data I want to clear content in old excel from where it has copied the data. Set ws = Workbooks("Path Old WorkBook").Worksheets("Sheet2").Range("A2").ClearContents

Private Sub CommandButton2_Click()
Dim InvoiceNumber As String
Dim ForwarderCode As String
Dim Status As String
Dim wb As Workbook
Dim ws As Workbook

Worksheets("Sheet2").Select
InvoiceNumber = Range("A2")
Worksheets("SampleFile").Select
ForwarderCode = Range("B2")
Worksheets("SampleFile").Select
Status = Range("C2")

Set wb = Workbooks.Open("Path")
Worksheets("Sheet2").Select
Worksheets("Sheet2").Range("A2").Select
RowCount = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Sheet2").Range("A1")
.Offset(RowCount, 0) = InvoiceNumber
.Offset(RowCount, 1) = ForwarderCode
.Offset(RowCount, 2) = Status
End With

wb.Save

Set ws = Workbooks("Path Old WorkBook").Worksheets("Sheet2").Range("A2").ClearContents
Set ws = Workbooks("Path Old WorkBook").Worksheets("Sheet2").Range("B2").ClearContents
Set ws = Workbooks("Path Old WorkBook").Worksheets("Sheet2").Range("C2").ClearContents
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Akshay Chari
  • 33
  • 8
  • 1
    Try: `Workbooks("Path Old WorkBook").Worksheets("Sheet2").Range("A2:C2").ClearContents`. As you just want to clear the contents of the range, you don't need the `Set ws =` bit of the statement. `Set` is used in VBA to setup an object. For what you are trying to do, you don't need that. Also, you shouldn't use things like `Select` or `Activate`.. I hear you say why.. [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) is why – Zac Sep 07 '20 at 14:24
  • I did now it is giving RunTime Error - 9 Subscript Out Of range – Akshay Chari Sep 07 '20 at 15:46
  • You must have the **old workbook** open – Zac Sep 08 '20 at 07:59

1 Answers1

1

Did you mean for WS to refer to a WorkBook or a WorkSheet?. Anyway, just backspace over those last 3 Set ws = bits. So
Workbooks("Path Old...").Worksheets("Sheet2").Range("A2").ClearContents

klausnrooster
  • 520
  • 3
  • 13
  • I did now it is giving RunTime Error - 9 Subscript Out Of range – Akshay Chari Sep 07 '20 at 15:45
  • Ah! Take the quotes out from around Sheet2. Or use the name dispayed on the tab. Sheet# (where # is some integer or rather "Long", is how Excel identifies the Sheet _internally_. Maybe you named it "Sheet 2" *with a space?. Look in the Editor on the left. Both types of names will be listed under +Microsoft Excel Objects. You may need to click the + to expand the list. If you don't see anything over there click the Project Explorer Icon or do Control-R. – klausnrooster Sep 07 '20 at 22:04
  • @AkshayChari, see the 2nd topic in this [SODocumentation][1] link. [1]:[https://sodocumentation.net/excel-vba/topic/2240/excel-vba-tips-and-tricks] – klausnrooster Sep 07 '20 at 22:11