Copy another formula sheet

[JERRY89]

Dear All,

I have a workbook as per attached here, the problem is every month i will receive from many entity where i need the Formula sheet in "Sheet1" and the problem is this file is system generated so the entity can't help me to put the formula as shown in the "Sheet1".

In this case is the a VBA code where it will auto rename the Active Sheet to "FORM" then auto extract from the share drive Report 1 (Sheet1) into the current Excel. This is very important as different entity have different sheet name so i need the VBA to auto rename the active worksheet to FORM in order for my formula to work.

[HansV]

I'm very sorry, but I don't have the slightest idea what you mean and what you want.
Do you already have some VBA code? The workbook that you attached is a .xlsx workbook, so it does not contain any code.

[JERRY89]

Hi Hans,

All the file is without VBA, example the below file is i receive from user so i need a VBA to auto rename the sheet from YYZ Limited to "FORM" and direct move a copy the "Sheet1" from Report 1.xlsx(This File is from my shared drive) to this new workbook Entity 1.xlsx. After Move a copy it auto close the Report 1 file.

[HansV]

See the attached workbook.

ProcessData.xlsm

[JERRY89]

Hi Hans ,

May i know how this VBA function, because i want to save this VBA in personal macro workbook, so that every time i receive any file i can execute it to auto rename the worksheet and move a copy Report 1 Sheet1 from my shared drive to the existing worksheet.

[HansV]

Open ProcessData.xlsm.
Activate the Visual Basic Editor (Alt+F11).
Drag Module1 from ProcessData.xlsm to Personal.xlsb and drop it there.
The macro will now be available every time you receive a file.

[JERRY89]

Hi Hans,

I have tried to execute, nothing happen? YYZ sheet is not rename to "Form" and my sheet1 file is not move in too as shown in the below picture.

[HansV]

I have tested the code several times, it works for me.

Perhaps you didn't select the correct workbooks.
The code first prompts you to select the Report 1 workbook, then to select the data workbook (Entity 1 in your example).

[JERRY89]

Hi Hans,

can it only select report 1 only since i am opening the Entity 1 worksheet to execute the VBA, because i will be having more than 300 Entity to be update and this 300 Entity have their own folder so if each time i execute i need to manually find the entity folder.

[HansV]

Change the macro to

Code: Select all

Sub ProcessData()
    Dim path1
    Dim path2
    Dim wbk1 As Workbook
    Dim wsh1 As Worksheet
    Dim wbk2 As Workbook
    Dim wsh2a As Worksheet
    Dim wsh2b As Worksheet
    With Application.FileDialog(1) ' msoFileDialogOpen
        .Filters.Clear
        .Filters.Add "Excel Workbooks (*.xlsx)", "*.xlsx"
        .InitialFileName = "Report 1.xlsx"
        .Title = "Select the Report 1 workbook"
        If .Show Then
            path1 = .SelectedItems(1)
        Else
            Beep
            Exit Sub
        End If
    End With
    Application.ScreenUpdating = False
    Set wbk2 = ActiveWorkbook
    Set wbk1 = Workbooks.Open(Filename:=path1)
    Set wsh1 = wbk1.Worksheets("Sheet1")
    Set wsh2a = wbk2.Worksheets(1)
    wsh2a.Name = "FORM"
    wsh1.Copy After:=wsh2a
    Set wsh2b = wbk2.Worksheets(2)
    wsh2b.Cells.Replace What:="[" & wbk1.Name & "]", Replacement:="", LookAt:=xlPart
    wbk1.Close SaveChanges:=False
    Application.ScreenUpdating = True
End Sub

[JERRY89]

Hi Hans,

Thanks alot, perfectly solved my problem