Code not working but not returning any errors?

[dannyok90]

Hi,

This code is to send data to another spreadsheet and its not returning any bugs but it just isnt sending the data?

can anybody sot something im missing or just not seeing

-Excel for Mac

Thanks!

[COLOR=#011993][FONT=Menlo]Sub[/FONT][/COLOR][COLOR=#000000][FONT=Menlo] Workbook1()[/FONT][/COLOR][COLOR=#011993][FONT=Menlo]Dim[COLOR=#000000] LastRow [/COLOR]AsInteger[COLOR=#000000], i [/COLOR]AsInteger[COLOR=#000000], erow [/COLOR]AsInteger[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo][COLOR=#011993]For[/COLOR] i = 2 [COLOR=#011993]To[/COLOR] LastRow[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo][COLOR=#011993]If[/COLOR] Cells(i, 1) = Date [COLOR=#011993]And[/COLOR] Cells(i, 2) = “Sales” [COLOR=#011993]Then[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]Range(Cells(i, 1), Cells(i, 7)).Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]Selection.Copy[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]Workbooks.Open Filename:=(” / Users / danielokeefe / Desktop / master.xlsx”)[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]Worksheets(“Sheet1”).Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]ActiveSheet.Cells(erow, 1).Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]ActiveSheet.Paste[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]ActiveWorkbook.Save[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]ActiveWorkbook.Close[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]Application.CutCopyMode = [COLOR=#011993]False[/COLOR][/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]EndIf[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]Next[COLOR=#000000] i[/COLOR][/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]End[/FONT][/COLOR][COLOR=#011993][FONT=Menlo]Sub
[/FONT][/COLOR]

 

[thisoldman]

I don't know Excel for Macs but I'm surprised the code compiled.

Looks like you copied code from a web site or an ebook and that can produce code formatting errors. I've corrected those formatting errors here:

Sub Workbook1()
    Dim LastRow As Integer, i As Integer, erow As Integer
    LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Cells(i, 1) = Date And Cells(i, 2) = "Sales" Then
            Range(Cells(i, 1), Cells(i, 7)).Select
            Selection.Copy
            Workbooks.Open Filename:=("/Users/danielokeefe/Desktop/master.xlsx")
            Worksheets("Sheet1").Select
            erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            ActiveSheet.Cells(erow, 1).Select
            ActiveSheet.Paste
            ActiveWorkbook.Save
            ActiveWorkbook.Close
            Application.CutCopyMode = False
        End If
    Next i
End Sub

The code you posted includes smart quotes (curly-quotes). You need to change the smart quotes to straight quotes.

In path names, spaces are significant. On a BSD-like system, Apple OS, I'd expect the actual path to the file to be:

"/Users/danielokeefe/Desktop/master.xlsx"
' not ” / Users / danielokeefe / Desktop / master.xlsx”

 

[mikerickson]

That file path does not look like a Mac file path. Try replacing the slashes with colons.

 

[Michael M]

Is the other workbook actually getting opened ??
Try stepping through the code manually and see what each line does.
On a PC that would be using F8.....don't know what it is on a Mac.

 

[dannyok90]

thanks guys! i have copied the code from a website but tried to adjust it to my own parameters.. im new to VBA so its all learning!

I've corrected those formatting errors here

wasnt aware there was a format to code i litterally thought you could go ahead and write line by line. heres the site i found https://www.exceltrainingvideos.com/copy-data-to-another-excel-workbook-based-on-criteria-using-vba/

The code you posted includes

smart quotes (curly-quotes). You need to change the smart quotes to straight quotes.

Im not sure what they are but i will certainly go away and do some research.

In path names, spaces are significant. On a BSD-like system, Apple OS, I'd expect the actual path to the file to be:

In response to both of you, i looked into mac file paths and i found out that, as you said, spacing is significant.. when i type in the file path and return to the next line VB automatically puts them in unless is specifically go back in again and change it, i thought maybe that VB knew what i was trying to do. as for the colons, in the same article a user mentioned that either colons or slashes can be used so i dont know whats considered standard. heres the link to the info i found https://community.spiceworks.com/topic/349757-file-path-in-mac-for-excel-vba-script

Is the other workbook actually getting opened ??
Try stepping through the code manually and see what each line does.
On a PC that would be using F8.....don't know what it is on a Mac.

Its not opening no, but on the video in the first link i have posted it doesnt seem to do either, to be honest im not wanting it to open. i want it to just update the other workbook?


thanks again for the help though guys, i will go away and give the newly formatted code a test drive, research curly-quotes and likewise VBA formatting!

 

[Michael M]

This line in your code is asking the code to OPEN the workbook

Workbooks.Open Filename:=("/Users/danielokeefe/Desktop/master.xlsx")