VBA Code to Copy Entire Workbook

[zach9208]

I am looking for a code to copy an entire workbook and name it based on the cell value that is placed in A1.

I would also like A1 to change. I have a column that has all the values I would like to cycle through cell A1 until the column hits a blank cell.

After the cell changes if it would create the entire workbook that would be even better.

Any ideas?

Thanks in advance!

 

[jeffcrtra]

Not sure the question is explicit enough...
If I am getting this right... you want a macro that open an existing file and save it (filename based on the original file's A1) and do a loop until the last cell with data on Column A?

 

[MichaelS94]

This works for me:

Sub NameMe()

Dim nLastRow As Integer
Dim i As Integer
Dim wsName As String


nLastRow = Range("A" & Rows.Count).End(Direction:=xlUp).Row

For i = 1 To nLastRow
    
    wsName = Cells(i, 1).Value
    ActiveWorkbook.SaveAs Filename:=wsName, FileFormat:=52
  
  
Next i

End Sub

 

[zach9208]

Hi Jeff!

I have a total of 6 tabs. Each tab feeds off the value in A1, as I have sumifs slicing and dicing data that is stored on a data tab. I need cell A1 on the first worksheet to cycle down a client list that is stored in column A on another tab, say the second tab. In column A there is a list of values that I need to feed into A1 first, then duplicate the workbook and save the file with the title of the current value in A1.

I need this ofcourse to loop through all the available values in column A on the second tab and create a seperate workbook for each client with all tabs.

Hope this makes more sense. Thanks!!!

 

[zach9208]

I am going to make this even more exact.

The cell I need to change is cell B5 on the tab labeled "Summary". The list of all values I need to enter into cell B5 is stored on a tab called "Drop Down List Data" in column A. I do have column headers so I need the list to cycle down from cell A2.

I need cell B5 on the summary tab to change to the first available cell down this list, then duplicate the entire workbook after this changes. I need this to loop saving duplicate workbooks until I hit an empty cell in column A of the "drop down list" tab. I would like each workbook to inheret the name of cell B5 of the summary tab with each duplicated workbook.

I know this is complex, so I appreciate the help.

 

[zach9208]

This is the closest code that I have found. Any ideas on how to modify this to loop and save the file as the the current value in B5 of the summary tab. See comment above.

Private Sub SaveWorkbookAsNewFile(NewFileName As String)
    Dim ActSheet As Worksheet
    Dim ActBook As Workbook
    Dim CurrentFile As String
    Dim NewFileType As String
    Dim NewFile As String
 
    Application.ScreenUpdating = False    ' Prevents screen refreshing.
 
    CurrentFile = ThisWorkbook.FullName
 
    NewFileType = "Excel Files 1997-2003 (*.xls), *.xls," & _
               "Excel Files 2007 (*.xlsx), *.xlsx," & _
               "All files (*.*), *.*"
 
    NewFile = Application.GetSaveAsFilename( _
        InitialFileName:=NewFileName, _
        fileFilter:=NewFileType)
 
    If NewFile <> "" And NewFile <> "False" Then
        ActiveWorkbook.SaveAs Filename:= NewFile, _
            FileFormat:=xlNormal, _
            Password:="", _
            WriteResPassword:="", _
            ReadOnlyRecommended:=False, _
            CreateBackup:=False
 
        Set ActBook = ActiveWorkbook
        Workbooks.Open CurrentFile
        ActBook.Close
    End If
 
    Application.ScreenUpdating = True
End Sub