How to convert Excel VBA created on a mac to Windows?

Question

I've made a VBA for a button in Excel for Mac that is supposed to copy the content of a few selected cells on one tab and paste it (as values) on the first available cell in an assigned row on a different tab.

This is the first time I've ever had a go at making this, so I probably didn't do it as efficient as possible, but it works.

The problem is that it only works on Mac. My co-workers that I've made it for uses PC. Can I convert the code to work on Excel for PC?

Edit: I should have been more explicit into what the problem actually is (thanks @KenWhite).

So here's what happened:

I created the file and the VBA.
I saved my file and attatched it to an email
my co-worker saved it and opened it up
When she pressed the button she got an error "Indexet är utanför intervall". My best translation for this is Index out of Range (but I'm not completely sure)

I suspected that it had to do with Mac -> PC, but some have pointed out that there should be no difference. I realize that the named on the sheets and that the data needs to be in the exact same spot - but that shouldn't be an issue in this case.

Edit 2: It seems to be a problem with special characters. the "ä" and "ö" used in the sheet names where changed in to "š" and "¨" in the VBA code on their end. I can't test it right now, but my guess is that the code will work if I either manually change the characters in the code or make sure to use sheet names without special characters.

If I should/could add additional information, let me know and I'll make another edit.

Thank you everyone.

Sub Generera()
'
' Generera Makro
'

'
    Range("B1").Select
    Selection.Copy
    Sheets("Utveckling över tid").Select
    BMaxRows = Cells(Rows.Count, "B").End(xlUp).Row
    Range("B" & BMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("veckoräckvidd").Select
    Range("B2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Utveckling över tid").Select
    CMaxRows = Cells(Rows.Count, "C").End(xlUp).Row
    Range("C" & CMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("veckoräckvidd").Select
    Range("B3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Utveckling över tid").Select
    DMaxRows = Cells(Rows.Count, "D").End(xlUp).Row
    Range("D" & DMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("veckoräckvidd").Select
    Range("B4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Utveckling över tid").Select
    EMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
    Range("E" & EMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("veckoräckvidd").Select
    Range("B6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Utveckling över tid").Select
    FMaxRows = Cells(Rows.Count, "F").End(xlUp).Row
    Range("F" & FMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("veckoräckvidd").Select
    Range("B7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Utveckling över tid").Select
    GMaxRows = Cells(Rows.Count, "G").End(xlUp).Row
    Range("G" & GMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("veckoräckvidd").Select
    Range("B8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Utveckling över tid").Select
    HMaxRows = Cells(Rows.Count, "H").End(xlUp).Row
    Range("H" & HMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("veckoräckvidd").Select
    Range("B9").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("veckoräckvidd").Select
    Range("B11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Utveckling över tid").Select
    IMaxRows = Cells(Rows.Count, "I").End(xlUp).Row
    Range("I" & IMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("veckoräckvidd").Select
    Range("B12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Utveckling över tid").Select
    JMaxRows = Cells(Rows.Count, "J").End(xlUp).Row
    Range("J" & JMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

I don't really see anything specific to macs here. I'll be that there's a difference in their workbooks that is preventing this from working for them — Marcucciboy2, Oct 26 '18 at 12:06
Thanks! What would you guess the difference could be when they open my file on their machines? How can I circumvent that? — Slackerguy, Oct 26 '18 at 12:20
There is no difference. What **specific problem** are you having? What problem are they having when they open the file? Instead of asking us to speculate on anything that might go wrong, [edit] your post and explain the actual problem you're trying to solve. You say *the problem is*, but never actually identify a problem. This site is for **specific questions** about **actual problems**, not *guess what might be happening and how to fix it*. — Ken White, Oct 26 '18 at 12:23
The main things you have to be careful of with macros like this is that all sheets are named the same and that all data is in the *exact* same place on both sheets in yours and their excel files — Marcucciboy2, Oct 26 '18 at 12:27
Where exactly does the error happen? One cause could think of is that the code tries to select a cell at column/row position ´0´ which does not exist. On the other hand, @Marcucciboy2 code is probably the way to go. — L8n, Oct 26 '18 at 13:06
Yeah, Index out of range for your code can really only be about the worksheet names — Marcucciboy2, Oct 26 '18 at 13:28

Marcucciboy2 · Answer 1 · 2018-10-26T21:05:27.980

I believe this is a replacement for your current macro, so this might solve your problem.

Sub Generera()

    Dim ws1 As Worksheet
    Set ws1 = sheets("Utveckling över tid")

    Dim ws2 As Worksheet
    Set ws2 = sheets("veckoräckvidd")

    Dim i As Long
    For i = 2 To 10
        Dim colLetter As String
        colLetter = Split(Cells(1, i).Address, "$")(1)

        ws1.Range(colLetter & ws1.Cells(rows.count, colLetter).End(xlUp).row + 1).value = ws2.Range("B" & i - 1).value
    Next i

End Sub

Here are the steps I took to convert your original code to my shorter version:

Range("B1").Select
Selection.copy
sheets("Utveckling över tid").Select
BMaxRows = Cells(rows.count, "B").End(xlUp).row
Range("B" & BMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Stopped using .Select, and started using direct Range().value transferring instead of .copy and .pastespecial so that I dont have to juggle cutcopymode and because you're not doing anything special, just copying the values only.

BMaxRows = sheets("veckoräckvidd").Cells(rows.count, "B").End(xlUp).row
sheets("veckoräckvidd").Range("B" & BMaxRows + 1).value = _
    sheets("Utveckling över tid").Range("B1").value

Include the statement for BMaxRows inside of the range itself for eventual simplicity.

sheets("veckoräckvidd").Range("B" & sheets("veckoräckvidd").Cells(rows.count, "B").End(xlUp).row + 1).value = _
    sheets("Utveckling över tid").Range("B1").value

Use Worksheet variables to shorten every time that I need to refer to one of the sheet names.

ws2.Range("B" & ws2.Cells(rows.count, "B").End(xlUp).row + 1).value = _
    ws1.Range("B1").value

And to convert it to a loop you can compare a couple of the converted operations side by side to see what changes every instance. In this case it's the column letter for ws2 and the row number in ws1.

ws2.Range("B" & ws2.Cells(rows.count, "B").End(xlUp).row + 1).value = ws1.Range("B1").value
ws2.Range("C" & ws2.Cells(rows.count, "C").End(xlUp).row + 1).value = ws1.Range("B2").value
ws2.Range("D" & ws2.Cells(rows.count, "D").End(xlUp).row + 1).value = ws1.Range("B3").value

Wow. Awesome! Thank you! This looks a lot more efficient! And actually taught me a lot of how VBA's work. — Slackerguy, Oct 26 '18 at 13:03

How to convert Excel VBA created on a mac to Windows?

1 Answers1