VBA Sub or Function not defined

Question

Im writing a code and I'm kinda of new to this. I keep getting a compile error on my macro code, a compile error that the sub or function is not defined. I tried to use to vba menu < tools < reference but I cannot click on it. Any suggestions? Below is the macro code I am trying to run

Sub Update()
'
'

Application.ScreenUpdating = True

Dim copysheet As Worksheet
Dim pastesheet As Worksheet

Set copysheet = Worksheets("Daily Sheet")
Set pastehseet = Worksheets("Raw Data")

copysheet.Range("G5").Copy
Sheet("Raw Data").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial _
Paste:=xlPasteValue, Operation:=xlNone, Skipblanks_:=False, Transpose:=False

copysheet.Range("G7").Copy
Sheet("Raw Data").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial _
Paste:=xlPasteValue, Operation:=xlNone, Skipblanks_:=False, Transpose:=False

copysheet.Range("G14").Copy
Sheet("Raw Data").Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial _
Paste:=xlPasteValue, Operation:=xlNone, Skipblanks_:=False, Transpose:=False

copysheet.Range("G9").Copy
Sheet("Raw Data").Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial _
Paste:=xlPasteValue, Operation:=xlNone, Skipblanks_:=False, Transpose:=False

copysheet.Range("G11").Copy
Sheet("Raw Data").Range("G" & Rows.Count).End(xlUp).Offset(1).PasteSpecial _
Paste:=xlPasteValue, Operation:=xlNone, Skipblanks_:=False, Transpose:=False

copysheet.Range("G13").Copy
Sheet("Raw Data").Range("H" & Rows.Count).End(xlUp).Offset(1).PasteSpecial _
Paste:=xlPasteValue, Operation:=xlNone, Skipblanks_:=False, Transpose:=False


Sheets("Daily Sheet").Range("G5:G14").Select.ClearContents
Sheets("Daily Sheet").Range("K9").Select


End Sub

Trum Trum · Accepted Answer · 2015-06-24T13:28:31

Dim copysheet As Worksheet
Dim pastesheet As Worksheet

Set copysheet = Worksheets("Daily Sheet")
Set pastesheet = Worksheets("Raw Data")

copysheet.Range("G5").Copy
Sheets("Raw Data").Range("A" & Rows.Count).End(xlUp).offset(1).PasteSpecial Paste:=xlPasteValues

copysheet.Range("G7").Copy
Sheets("Raw Data").Range("B" & Rows.Count).End(xlUp).offset(1).PasteSpecial Paste:=xlPasteValues

copysheet.Range("G14").Copy
Sheets("Raw Data").Range("C" & Rows.Count).End(xlUp).offset(1).PasteSpecial Paste:=xlPasteValues

copysheet.Range("G9").Copy
Sheets("Raw Data").Range("D" & Rows.Count).End(xlUp).offset(1).PasteSpecial Paste:=xlPasteValues

copysheet.Range("G11").Copy
Sheets("Raw Data").Range("G" & Rows.Count).End(xlUp).offset(1).PasteSpecial Paste:=xlPasteValues

copysheet.Range("G13").Copy
Sheets("Raw Data").Range("H" & Rows.Count).End(xlUp).offset(1).PasteSpecial Paste:=xlPasteValues


Sheets("Daily Sheet").Range("G5:G14").clear
Worksheets("Daily Sheet").Range("K9").Select

Appears to do what you want.

The issues I found were:

The use of sheet - not sheets (that explains the error you were getting, as it thought sheet was another macro or similar

Similarly, you had missed the x off of the end of xlvalues on the paste specials.

Finally, you need to use Worksheets(ws.name) for the selection at the end.

Hope that this helps

VBA Sub or Function not defined

1 Answers