Need help creating a Macro.

[shaikhstonevilla]

Hi,

I have 2 sheets in excel. (Say "Data" and "Source")

and I do the following task on regular basis.

1) Double click the cell C3 in "Data" sheet . (It takes me to the "source" sheet).
2) Press Cltr + A (To select entire details from "source" sheet ).
3) Press Cltr + C (To copy entire details from "source" sheet ).
4) Press Cltr + N (To create a new excel sheet).
5) Press Cltr + V (To paste the details from source in new excel sheet).
6) Press Cltr + S (To save new worksheet).
7) Copy Cell C3 text from the "Data" sheet and paste it in the pop up window in order to save the file with C3 cell name.

Follow the above procedure for Cell C4, C5, C6.....C11 on "Data" sheet.


I tried recording macro, but it couldn't succeed. Please help me creating macro.


Thanks,
Tim.

 

[Boswell]

This should work:

Public Sub CreateNewFiles()
    
    ' supress alerts so that excel doesn't ask if you want to save as or close
    Application.DisplayAlerts = False
    
    ' get the list of names from Data sheet
    Dim fileNames As Variant: fileNames = ThisWorkbook.Worksheets("Data").Range("C4:C11").Value
    
    ' loop through the names and add workbooks
    Dim v As Variant
    For Each v In fileNames
        
        ' create new workbook
        Dim newWb As Workbook: Set newWb = Application.Workbooks.Add
        
        ' copy source worksheet to new workbook
        ThisWorkbook.Worksheets("Source").Copy Before:=newWb.Worksheets(1)
        
        ' save new workbook as name at same file path as this workbook
        newWb.SaveAs (ThisWorkbook.Path & "\" & CStr(v))
        
        ' close new workbook
        newWb.Close
        
    Next
    
    Application.DisplayAlerts = True
    
    MsgBox ("Success!")
    
End Sub

This will throw an error if you try to save the workbook in a folder where there is already a workbook with the same name. The easiest way to avoid that is to create a new folder, add the workbook with this VBA method to the folder, open the workbook with this VBA method and the run the macro. That way each time you create the new files you won't have to worry about name conflicts with previously existing files. I documented what the code does with code comments.