Macro works on 32 bit, but not on 64 bit

Hi there - hoping that someone can help me on a macro issue.

the macro i've written opens a csv file in a folder, copies over data into a "aggregated" workbook, does some transformations to this data, and closes the open csv file, and then moves on to the next csv. The macro finishes when all the csv files have been cycled through.

So I wrote this macro on a 32 bit computer, using microsoft office 2010. It worked fine on this. However when I updated my computer to 64 bit, I have been encountering issues. The macro still works on my colleague's computers (who have 32 bit computers), but not mine.

While the macro is running, it will close the aggregated workbook (which stops the code, as the macro is currently stored in that workbook), and reopen the workbook. And each time i run this macro, it will crash at what appears to be random times - e.g. sometimes after the 10th csv, sometimes after the 20th csv, and sometimes it wont crash at all and successfully completes the code.

So i don't believe i can trace the source of the error, as excel (and vba) closes and reopens as soon as it goes wrong. My guess is its something to do with 32/64 bit differences, or a memory issue.

It would be great to get some feedback on what the problem is, or even if you've got some suggestions on what might fix it. Really at a loose end here, so any help would be greatly appreciated!

I've listed all of the code below, just in case it's useful (sorry for large amount of it). Please let me know if i need to explain the problem further.

Thanks a lot,
Dave




Sub UploadAndTransCSV_STAOnly_parallel()

'Application.EnableCancelKey = xlDisabled

Application.DisplayAlerts = False
Application.ReferenceStyle = xlR1C1
'Application.EnableEvents = False

Dim folderpath As String
Dim filename As String
Dim wb As Workbook
Dim OutputFileName As String

OutputFileName = ActiveWorkbook.Name

'check if an "ALLDATA" tab already exists.
'If so, ask the user if they would like to keep it and exit the macro, or replace it with newly imported data.

Dim sh1 As Worksheet
Dim flg1 As Boolean
For Each sh1 In Worksheets
If sh1.Name = "ALLDATA" Then flg1 = True: Exit For
Next
If flg1 = True Then
MSG1 = MsgBox("An ALLDATA tab already exists - would you like to replace it with a new ALLDATA tab?", vbYesNo, "Yes")
If MSG1 = vbNo Then
MsgBox "Please rename the existing ALLDATA tab before running this macro"
GoTo Finish
Else
Sheets("ALLDATA").Delete
End If
Else
End If

Sheets.Add before:=Sheets(1)
Sheets(1).Select
Sheets(1).Name = "ALLDATA"

'select the folder in which the csv files are located

MsgBox "Please locate the folder containing the csv files to be transformed."

Dim fldr As FileDialog
Dim fldr_dir As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = strPath
If .Show <> -1 Then GoTo NextCode
fldr_dir = .SelectedItems(1)
End With
NextCode:
Set fldr = Nothing

'if the user pressed cancel, exit the macro

If fldr_dir = "" Then
MsgBox "A folder must be selected. Try again..."
Sheets("ALLDATA").Delete
GoTo Finish
Else
End If

folderpath = fldr_dir & "\"

'flag if its the first CSV file or not; some operations are done only to the first csv, to set up the
'"ALLDATA" tab.

Dim FirstCSV As Boolean
FirstCSV = True

'Start cycling through the different CSV files

filename = Dir(folderpath & "*.csv")
Do While filename <> ""
Application.ScreenUpdating = False
Set wb = Workbooks.Open(folderpath & filename)

Dim tab_name As String
Dim new_vars_cnt As Integer
Dim exist_vars_cnt As Integer
Dim Observ_cnt As Integer
Dim XS_cnt As Integer

new_vars_cnt = 0
exist_vars_cnt = 0
Observ_cnt = 0

tab_name = ActiveSheet.Name
Sheets(tab_name).Select
Sheets(tab_name).Copy before:=Workbooks(OutputFileName).Sheets(1)

'count the number of new variabes to upload

Sheets(tab_name).Activate
new_vars_cnt = WorksheetFunction.CountA(Rows(1)) - 1

'count the number of observations (time periods * cross sections)

Observ_cnt = WorksheetFunction.CountA(Columns(1)) - 2

'If we are uploading the first CSV into the ALLDATA tab, then some extra data needs to be copied over

If FirstCSV = True Then

'copy over the entire cross section column, then count the number of cross sections
'do this by removing duplicates after copying over the cross section column

Sheets(tab_name).Activate
Range(Cells(3, 1), Cells(2 + Observ_cnt, 1)).Copy
Sheets("ALLDATA").Activate
Cells(2, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Range(Cells(2, 1), Cells(2 + Observ_cnt, 1)).RemoveDuplicates Columns:=1, Header:=xlNo
Cells(2, 1).Select
XS_cnt = Range(Selection, Selection.End(xlDown)).Count

'put the data row/column header names in the ALLDATA tab

Cells(1, 2).Value = "Transformation"
Cells(2, 2).Value = "Average Val"
Cells(2, 2).Copy
Range(Cells(2, 2), Cells(1 + XS_cnt, 2)).Select
ActiveSheet.Paste
Cells(XS_cnt + 2, 1).Value = "Region Name"
Cells(XS_cnt + 2, 2).Value = "Observation"

'copy the cross section and observation columns over

Sheets(tab_name).Activate
Range(Cells(3, 1), Cells(Observ_cnt + 2, 2)).Copy
Sheets("ALLDATA").Activate
Cells(XS_cnt + 3, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Else
End If

'count the number of existing variables in the ALLDATA tab

Sheets("ALLDATA").Activate
exist_vars_cnt = WorksheetFunction.CountA(Rows(2)) - 2

'copy the variable names over

Sheets(tab_name).Activate
Range(Cells(2, 3), Cells(2, new_vars_cnt + 2)).Copy
Sheets("ALLDATA").Activate
Cells(XS_cnt + 2, exist_vars_cnt + 3).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'copy the transformations over

Sheets(tab_name).Activate
Range(Cells(1, 3), Cells(1, new_vars_cnt + 2)).Copy
Sheets("ALLDATA").Activate
Cells(1, exist_vars_cnt + 3).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'calculate the averages for each cross section

Cells(2, exist_vars_cnt + 3).Value = "=AVERAGEIF(R" & XS_cnt + 3 & "C1:R" & (Observ_cnt + XS_cnt + 2) & "C1,RC1,'" & tab_name & "'!R3C[" & (exist_vars_cnt * -1) & "]:R" & (Observ_cnt + 2) & "C[" & (exist_vars_cnt * -1) & "])"
Cells(2, exist_vars_cnt + 3).Copy
Range(Cells(2, exist_vars_cnt + 3), Cells(XS_cnt + 1, exist_vars_cnt + new_vars_cnt + 2)).Select
ActiveSheet.Paste

'paste the formulas as values

Range(Cells(2, exist_vars_cnt + 3), Cells(XS_cnt + 1, exist_vars_cnt + new_vars_cnt + 2)).Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'use a formula to transform the variables, either STA or SUB

Cells(XS_cnt + 3, exist_vars_cnt + 3).Value = "=IF('" & tab_name & "'!R[" & (XS_cnt * -1) & "]C[" & (exist_vars_cnt * -1) & "]<>"""", IF('" & tab_name & "'!R1C[" & (exist_vars_cnt * -1) & "]=""STA"",IFERROR('" & tab_name & "'!R[" & (XS_cnt * -1) & "]C[" & (exist_vars_cnt * -1) & "]/AVERAGEIF(R" & (XS_cnt + 3) & "C1:R" & (Observ_cnt + XS_cnt + 2) & "C1,RC1,'" & tab_name & "'!R3C[" & (exist_vars_cnt * -1) & "]:R" & (Observ_cnt + 2) & "C[" & (exist_vars_cnt * -1) & "]),0), '" & tab_name & "'!R[" & (XS_cnt * -1) & "]C[" & (exist_vars_cnt * -1) & "]), """")"
Cells(XS_cnt + 3, exist_vars_cnt + 3).Copy
Range(Cells(XS_cnt + 3, exist_vars_cnt + 3), Cells(XS_cnt + Observ_cnt + 2, exist_vars_cnt + new_vars_cnt + 2)).Select
ActiveSheet.Paste

'paste the formulas as values

Range(Cells(XS_cnt + 3, exist_vars_cnt + 3), Cells(XS_cnt + Observ_cnt + 2, exist_vars_cnt + new_vars_cnt + 2)).Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'tidying up...

Sheets(tab_name).Select
ActiveWindow.SelectedSheets.Delete

wb.Close

filename = Dir
FirstCSV = False
Loop

'PART 2; STACKED TO PARALLEL *************************************************************************************

'count the number of existing variables in the ALLDATA tab

Sheets("ALLDATA").Activate
Rows(2).Select
exist_vars_cnt = WorksheetFunction.CountA(ActiveWindow.Selection) - 2

'count the number of time periods

Dim periods_cnt As Integer
periods_cnt = Observ_cnt / XS_cnt

'start moving stuff across for each cross section

Dim x1 As Integer

For x1 = 2 To XS_cnt
'move the averages across
Range(Cells(1 + x1, 3), Cells(1 + x1, 2 + exist_vars_cnt)).Cut
Cells(2, 3 + (x1 - 1) * exist_vars_cnt).Select
ActiveSheet.Paste
'move the data across
Range(Cells(3 + XS_cnt + periods_cnt * (x1 - 1), 3), Cells(2 + XS_cnt + periods_cnt * x1, 2 + exist_vars_cnt)).Cut
Cells(XS_cnt + 3, 3 + (x1 - 1) * exist_vars_cnt).Select
ActiveSheet.Paste
'copy over the transformations
Range(Cells(1, 3), Cells(1, 2 + exist_vars_cnt)).Copy
Cells(1, 3 + (x1 - 1) * exist_vars_cnt).Select
ActiveSheet.Paste
Next x1

For x2 = 1 To XS_cnt
'create the new variable names (including cross section reference)
Cells(3, 3 + (x2 - 1) * exist_vars_cnt).Value = "=LEFT(R[" & (x2 - 2) & "]C1, 1)&""_""&R" & (XS_cnt + 2) & "C[" & (-1 * (x2 - 1) * exist_vars_cnt) & "]"
Cells(3, 3 + (x2 - 1) * exist_vars_cnt).Copy
Range(Cells(3, 3 + (x2 - 1) * exist_vars_cnt), Cells(3, 2 + x2 * exist_vars_cnt)).Select
ActiveSheet.Paste
Range(Cells(3, 3 + (x2 - 1) * exist_vars_cnt), Cells(3, 2 + x2 * exist_vars_cnt)).Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Next x2

Dim RowsDelete As String

RowsDelete = "4:" & XS_cnt + 2
Rows(RowsDelete).Delete
Columns("A:A").Delete

'delete the excess dates

Range(Cells(4 + periods_cnt, 1), Cells(3 + periods_cnt * XS_cnt, 1)).Delete

Sheets("ALLDATA").Activate
Columns(1).EntireColumn.AutoFit
Cells(4, 2).Select
ActiveWindow.FreezePanes = True
Range("A1").Select

Finish:

Application.ReferenceStyle = xlA1
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub