Last Step Please Help! Transferring Data Code (:

[ShiGuy]

Hi all,

Thanks so much to those who have been helping me. My code is throwing an error of "Compile error: Expected Array" in my following code where I never even declared an array... The sheetExists() function is being highlighted by the Debugger.

A bit of some background. I'm trying to transfer data from one “Master” sheet - "Profile List (VBA)" to several other sheets with different names. I'm trying to go through the master sheet and checking if the company names in the master sheet exists as a sheet name in my workbook and if it does exist then I update several rows/columns.

For example, let's say cell A2 in the master sheet is "Facebook" and there exists a "Facebook" sheet then I update and if there doesn't, then I do not.

This is the last step and I'm done... New to VBA and would like to impress my boss as an intern hahaha...

Cheers!
Shi

[FONT=arial][FONT=Arial][SIZE=2]Sub transferInfo()[/SIZE][/FONT][/FONT]
[FONT=Arial]    Dim strSourceSheet As String, strDestinationSheet As String, sourceData As String, financialSheet As String
    Dim lastRowOfMasterFile As Long, rowColNumberProfile As Long, activeCellRow As Long, activeCellRow1 As Long
 
    Dim rowColNumberBasicInfo As Long, rowColNumberContact, rowNumFinanceDest As Long
    Dim colNumberFinanceSource As Long, colNumberFinanceSource1
    Dim sheetExists As Boolean
    strSourceSheet = "Profile List (VBA)"
    Sheets(strSourceSheet).Visible = True
    Sheets(strSourceSheet).Select
    Range("A2").Select
    Do While ActiveCell.Value <> ""
        strDestinationSheet = ActiveCell.Value
        If sheetExists(<wbr>strDestinationSheet) = True Then
            activeCellRow = ActiveCell.Row
                For rowColNumberBasicInfo = 5 To 6
                    Sheets(strDestinationSheet).<wbr>Cells(rowColNumberBasicInfo, "E") = Sheets(strSourceSheet).Cells(<wbr>activeCellRow, rowColNumberBasicInfo).Value
                    Next
                For rowColNumberProfile = 11 To 19
                    Sheets(strDestinationSheet).<wbr>Cells(rowColNumberProfile, "C") = Sheets(strSourceSheet).Cells(<wbr>activeCellRow, rowColNumberProfile).Value
                    Next
                For rowColNumberContact = 56 To 59
                    Sheets(strDestinationSheet).<wbr>Cells(rowColNumberContact, "E") = Sheets(strSourceSheet).Cells(<wbr>activeCellRow, rowColNumberContact).Value
                    Next
        End If
        Loop
End Sub


Public Function sheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet
    If wb Is Nothing Then Set wb = ActiveWorkbook
    On Error Resume Next
    For Each sht In wb.Worksheets
        If sht.Name = shtName Then sheetExists = True
    Next
[/FONT][FONT=Arial]End Function
[/FONT]

 

[dmt32]

Delete following line in your transferInfo Sub & see if resolves:

Dim sheetExists As Boolean

Dave

 

[ShiGuy]

Hi Dave,

Managed to fix it but didn't need to change what you mentioned. Thank you though!

If you're interested in what went wrong:

1) the Compile error: Expected array is because I was naming my function sheetExists() which I think referenced something already built-in so I changed it to sheetExistsCheck() and fixed that error.

2) another error popped out in which it would make the Excel file go into "Not Responding" mode but that was because of the error you pointed out, how it doesn't increment to the next ActiveCell so I fixed that with ActiveCell.Offset(1,0).Select

TA-DA

Shi

Delete following line in your transferInfo Sub & see if resolves:

Dim sheetExists As Boolean

Dave