MaxWork
New Member
- Joined
- Mar 31, 2021
- Messages
- 5
- Office Version
- 365
- 2019
- 2016
- 2013
- 2011
- 2010
- 2007
- Platform
- Windows
Hello People!
I am at the point in my job, where I need to make a macro, that would create a fresh excel file based on another excel file (like a template).
After that, the macro has to copy 1 specific sheet (For ex. "Summary") from an unspecified file in a different folder to an excel file, that was just created by the same macro!
(It is preferred, that the macro would execute using a command button)
I am very new to VBA, and was only able to make a few relatively easy macros in the past. This one however, proves to be more complex.
My code so far:
I know that the code is very messy as I'm trying different things to achieve the desired result.
Any help on this will be very appreciated!
Thanks!
I am at the point in my job, where I need to make a macro, that would create a fresh excel file based on another excel file (like a template).
After that, the macro has to copy 1 specific sheet (For ex. "Summary") from an unspecified file in a different folder to an excel file, that was just created by the same macro!
(It is preferred, that the macro would execute using a command button)
I am very new to VBA, and was only able to make a few relatively easy macros in the past. This one however, proves to be more complex.
My code so far:
VBA Code:
Sub New_file_with_import()
Dim directory As String, fileName2 As String
'I was able to make the first part work, a fresh excel file is created based on a template and saved in a different folder'
Dim strTemplate As String: strTemplate = "Directory\Template File name"
Dim wb As Workbook
Set wb = Workbooks.Add(strTemplate)
ActiveWorkbook.SaveAs Filename:="Directory\New file name.xlsx"
ActiveWorkbook.Close
'The 2nd part doesn't work!'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'This is where im trying to take the file from, from which ill be copying the sheet'
directory = "Directory"
fileName2 = Dir(directory & "*.xl??")
Workbooks.Open (directory & fileName2)
'How do i copy from an unspecified file? Basically, i need to copy from the only file in that folder'
Workbooks("?").Sheets("Summary").Copy _
after:=Workbooks("Directory\Freshly created file.xlsx").Sheets("Sheet1")
'Im trying to paste the sheet after the first sheet'
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
I know that the code is very messy as I'm trying different things to achieve the desired result.
Any help on this will be very appreciated!
Thanks!
Last edited by a moderator: