how-to-fix-compile-error-expected-end-sub-property-or-function

Question

For my work, I'm trying to automate some analysis into a standard Excel file to do the work for me. I thought I had assembled the right code to do so.

What I'm looking for is just a sheet where I can put the source file export from my financial programm en turn that into a more complex version with additional formulas to add some fields and the add different pivots to it.

I've have written code to basicly clean wipe the sheet where I want the sourcefile to be copied to, then I want Excel to copy all the data from the sourcefile sheet to the new sheet and then add the formulas. I'm already getting an error on the first part.

I used the following code:

    enter code here
Sub TransferSourcefile()
Dim EndRow, FirstRow

    'TransferSourcefile
   Sheets("Macro sortering").Select
   Range("A4").Select
   If Sheets("Macro sortering").Range("A4").Value > 0 Then
       Selection.CurrentRegion.Select
       EndRow = Selection.CurrentRegion.Rows.Count
       Sheets("Macro sortering").Range("A4:AG" & EndRow).Select
       Selection.ClearContents
   End If
   Sheets("Bronbestand").Select
    
   Sheets("Bronbestand").Select
   Range("A1").Select
   Selection.End(xlDown).Select
   EndRow = ActiveCell.Row
   Range("A1:AE" & EndRow).Select

   Selection.Copy
   Sheets("Macro sortering").Select
   Sheets("Macro sortering").Range("A4").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Application.CutCopyMode = False

End Sub

enter code here

Can you guys help me out?

user4157124 · Accepted Answer · 2020-11-27 04:51:53Z

1

Run this and press Ctrl + C when the error popup appears to copy the error text and paste it here. It will tell you what line is causing the error.

Sub TransferSourcefile()
      Dim EndRow, FirstRow

          'TransferSourcefile
10       On Error GoTo TransferSourcefile_Error

20       Sheets("Macro sortering").Select
30       Range("A4").Select
40       If Sheets("Macro sortering").Range("A4").Value > 0 Then
50           Selection.CurrentRegion.Select
60           EndRow = Selection.CurrentRegion.Rows.Count
70           Sheets("Macro sortering").Range("A4:AG" & EndRow).Select
80           Selection.ClearContents
90       End If
100      Sheets("Bronbestand").Select
          
110      Sheets("Bronbestand").Select
120      Range("A1").Select
130      Selection.End(xlDown).Select
140      EndRow = ActiveCell.Row
150      Range("A1:AE" & EndRow).Select

160      Selection.Copy
170      Sheets("Macro sortering").Select
180      Sheets("Macro sortering").Range("A4").Select
190      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
             :=False, Transpose:=False
200      Application.CutCopyMode = False

210      On Error GoTo 0
220      Exit Sub

TransferSourcefile_Error:

230       MsgBox "Line:" & Erl & vbCrLf & "Error " & Err.Number & " (" & Err.Description & ") in procedure TransferSourcefile"

End Sub

edited Nov 27, 2020 at 4:51

user4157124

3,00021 gold badges33 silver badges48 bronze badges

answered Nov 7, 2020 at 16:04

garbb

6995 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

NoFace Over a year ago

--------------------------- Microsoft Excel --------------------------- Line:100 Error 9 (Het subscript valt buiten het bereik) in procedure TransferSourcefile --------------------------- OK ---------------------------

garbb Over a year ago

Does a sheet named exactly "Bronbestand" exist?

NoFace Over a year ago

Yes it does, I recopied your data into my vba and it works now.... maybe I had too many spaces somewhere?

NoFace Over a year ago

Additionally: If I want to add a new row to the copied data with a formula, how do I make that row go to the exact ending of the copied data?

Collectives™ on Stack Overflow

how-to-fix-compile-error-expected-end-sub-property-or-function

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related