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.
Copy another formula sheet
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Copy another formula sheet
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Copy another formula sheet
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.
Do you already have some VBA code? The workbook that you attached is a .xlsx workbook, so it does not contain any code.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Copy another formula sheet
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.
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.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Copy another formula sheet
See the attached workbook.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Copy another formula sheet
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.
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.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Copy another formula sheet
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.
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.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Copy another formula sheet
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.
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.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Copy another formula sheet
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).
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).
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Copy another formula sheet
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.
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.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Copy another formula sheet
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
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Copy another formula sheet
Hi Hans,
Thanks alot, perfectly solved my problem
Thanks alot, perfectly solved my problem