CODEWITHSUNDEEP
vbaexcel
shettyrish
shettyrish

Reputation: 99

VBA code to refer to a range on another sheet in a separate closed workbook, and perform operations on the referred cells

This is a snippet of my code 

    answer = MsgBox("To get the December KPI values from the previous year folder click Yes, if you want to go to default path, click No and If you are not sure, click Cancel", vbYesNoCancel + vbQuestion, "User Specified Path")
    If answer = vbYes Then

        MyFile = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*")

        Set wkb = Workbooks.Open(MyFile, UpdateLinks:=0)

        'Searching for Month KPI values of previous year December


TryAgain: xName = InputBox("Enter sheet name to find in the workbook: )", "Sheet search")

        If xName = "" Or xName = False Then Exit Sub
        found = False

            On Error Resume Next
            Sheets(xName).Activate

            If ActiveSheet.Name = xName Then
            MsgBox ("Sheet Found")
            found = True

            For j = 3 To 17


                Wb.Sheets(Name).Cells(16, j).Formula = "=(R[-13]C-'" & MyFile & xName & "'!R[-13]C)/'" & MyFile & xName & "'!R[-13]C"
                Wb.Sheets(Name).Cells(17, j).Formula = "=(R[-13]C-'" & xName & "'!R[-13]C)/'" & xName & "'!R[-13]C"
                Wb.Sheets(Name).Cells(18, j).Formula = "=(R[-13]C-'" & xName & "'!R[-13]C)/'" & xName & "'!R[-13]C"
                Wb.Sheets(Name).Cells(19, j).Formula = "=(R[-13]C-'" & xName & "'!R[-13]C)/'" & xName & "'!R[-13]C"
                Wb.Sheets(Name).Cells(20, j).Formula = "=(R[-13]C-'" & xName & "'!R[-13]C)/'" & xName & "'!R[-13]C"
                Wb.Sheets(Name).Cells(21, j).Formula = "=R[-12]C -'" & xName & "'!R[-12]C"
                Wb.Sheets(Name).Cells(22, j).Formula = "=R[-9]C -'" & xName & "'!R[-9]C"
                Wb.Sheets(Name).Cells(23, j).Formula = "=IFERROR(((R[-9]C- '" & xName & "'!R[-9]C)/'" & xName & "'!R[-9]C),0)"

            Next j

            wkb.Close

            End If


           If found = False Then
            If MsgBox("Worksheet name does not exist, click OK to try again or Click Cancel to Exit", vbOKCancel) _
            = vbCancel Then Exit Sub
            GoTo TryAgain

            End If

I want to refer the sheet named Month KPI Dec16 from another workbook named Group View_2016.xlsx in a different folder named 2016. The main problem is in the for loop in the TryAgain snippet. I want to perform the following operation in my current sheet :

Wb.Sheets(Name).Cells(16, j).Formula = "=(R[-13]C-'" & MyFile & xName & "'!R[-13]C)/'" & MyFile & xName & "'!R[-13]C"

by referring the cells from the source sheet. I can't figure out how to tweak this particular formula to get it to refer the sheet.

This is the resultant formula that is getting applied in the cell by writing the above code:

=(C3-'E:\John\2016\[Group View_2016.xlsxMonth KPI Dec16]Group View_2016'!C3)/'E:\John\2016\[Group View_2016.xlsxMonth KPI Dec16]Group View_2016'!C3

whereas I need it as :

 =(C3-'E:\John\2016\[Group View_2016.xlsx]Month KPI Dec16'!C3)/'E:\John\2016\[Group View_2016.xlsx]Month KPI Dec16'!C3

Need to delete the last bit ]Group View_2016 and shift the ] to before Month KPI Dec16.

Can someone help me with this? Thank you

Upvotes: 0

Views: 226

Answers (1)

Dave
Dave

Reputation: 1643

After you have set the wkb object you can change your MyFile variable with the following, that should put it all into the correct format:

MyFile = wkb.path & "\[" & wkb.name & "]"

Upvotes: 1

Related Questions