VBA Help Needed - Automating New File Creation

[JDSchmidt1977]

Hello Everyone,

I'm looking for some help. Before going in the details, I would like to clarify that I am a novice at VBA. I'm trying to do the following:

I have a couple of tables that are updated everyday. I have a query built out of power query that consolidates these to tables into a report. What I want to do is at the push of a button, refresh the query, copy the report into a new workbook, save that workbook in a specific location, and return the cursor to cell A1 in the original workbook.

So far I have created a form control button, recorded a macro that does everything as described above and also deletes the button on the new workbook, and assigned the macro to the button. When I run it everything works as needed; however, the button on the original file duplicates itself, and when I open the newly created file, the original button is gone, but the duplicated button now appears.

Does anyone know why this might be the case and what I can do about it? Here is the code from my Macro if it helps.......

Sub Macro1()
'
' Macro1 Macro
'
'
Range("B2").Select
Sheets("Query").Select
ActiveSheet.Buttons.Add(939, 52.5, 105, 47.25).Select
Sheets("Query").Copy
ActiveSheet.Shapes.Range(Array("Button 1")).Select
Selection.Delete
ActiveWindow.SmallScroll Down:=-9
ChDir "F:"
ActiveWorkbook.SaveAs Filename:="F:\Test.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Range("Test_Data_2[[#Headers],[Transaction ID]]").Select
End Sub

Thanks in Advance,

Jonathan

 

[hotabae]

the button on the original file duplicates itself, and when I open the newly created file, the original button is gone, but the duplicated button now appears.

These lines delete the original button before the save happens; therefore there will be no "Button 1" in the original file Nor the new file.

[LEFT][COLOR=#333333][FONT=Verdana]ActiveSheet.Shapes.Range(Array("Button 1")).Select[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Selection.Delete
[/FONT][/COLOR][/LEFT]

Also, you added the new button before saving, so it will show up in the new file

[LEFT][COLOR=#333333][FONT=Verdana]ActiveSheet.Buttons.Add(939, 52.5, 105, 47.25).Select[/FONT][/COLOR][/LEFT]

I rewrote your code below. Assuming the button you are clicking to activate Macro1 is in fact "Button 1"...

This will Remove the button, save the file (without the button), and then Replace the button.

Sub Macro1()
'
' Macro1 Macro
'
'
Sheets("Query").Shapes.Range("Button 1").Delete


ActiveWorkbook.SaveCopyAs Filename:="F:\Test.xlsx"


Dim newButton As Button
Set newButton = Sheets("Query").Buttons.Add(939, 52.5, 105, 47.25)


With newButton
    .Name = "Button 1"
    .OnAction = "'" & ActiveWorkbook.Name & "'!Macro1"
    .Caption = "Button 1"
    
End With


Range("Test_Data_2[[#Headers],[Transaction ID]]").Select


End Sub

 

[JDSchmidt1977]

Thanks,

It seems to be working well. The button in the new file is gone and the macro is no longer duplicating the button in the original file. The only issue is instead of creating a copy of the "query" worksheet, it is re-creating the entire workbook in the new file. Do you know how to fix this in the code? I tried changing the line:

ActiveWorkbook.SaveCopyAs Filename:="F:\Test.xlsx"

to

ActiveWorksheet.SaveCopyAs Filename:="F:\Test.xlsx"

But that gave me an error.

 

[hotabae]

Try this:

Sub Macro1()
'
' Macro1 Macro
'
'
Sheets("Query").Shapes.Range("Button 1").Delete


Dim oldBook As Workbook
Dim oldSheet As Worksheet


Dim newBook As Workbook


Set oldBook = ActiveWorkbook
Set oldSheet = Sheets("Query")


Set newBook = Workbooks.Add


oldBook.Activate

oldSheet.Copy Before:=newBook.Sheets(1)


newBook.Activate


Application.DisplayAlerts = False


newBook.Sheets("Sheet1").Delete

newBook.SaveCopyAs Filename:="F:\Test.xlsx"
newBook.Close


Application.DisplayAlerts = False


oldBook.Activate


Dim newButton As Button

Set newButton = Sheets("Query").Buttons.Add(939, 52.5, 105, 47.25)


With newButton
    .Name = "Button 1"
    .OnAction = "'" & ActiveWorkbook.Name & "'!Macro1"
    .Caption = "Button 1"
    
End With


Range("Test_Data_2[[#Headers],[Transaction ID]]").Select


End Sub

It Opens a second, blank workbook and copies your "Query" page into it (without the button), deletes the extra sheet, and then saves a copy to F:/

Next, it deletes the placeholder workbook and restores the button to the original document. Lemme know how it goes.

 

[JDSchmidt1977]

Thanks a ton! That worked perfectly