CODEWITHSUNDEEP
vbaexcel
Sergio Oropeza
Sergio Oropeza

Reputation: 53

VBA Sub or Function not defined

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

Upvotes: 1

Views: 3221

Answers (1)

Trum
Trum

Reputation: 630

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

Upvotes: 1

Related Questions