Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Duplicate and name workbooks on list of names

Posted on 2013-01-18
15
Medium Priority
?
395 Views
Last Modified: 2013-01-20
I am collecting information on people and what products they use.
In this case, I will be using SoftwareSurvey.xls.
***But, I want to be able to select the file that will be duplicated***

The list of people is People.xlsm
The macro will create:
Tom-SoftwareSurvey.xls
Dick-SoftwareSurvey.xls
Harry-SoftwareSurvey.xls

In each of the created xls files, Sheet1 should be renamed for the person and cell B1 should get their name as well.

I want to run the macro in People.xlsm and be asked for what file to be duplicated with their names.
SurveyForm.xlsm
People.xlsm
0
Comment
Question by:Alex Campbell
  • 6
  • 5
  • 3
14 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 38793905
Try this macro

Sub generatefiles()
    Dim fn As String, wb As Workbook, cel As Range
    fn = Application.GetOpenFilename("*.xls*,*.xls*")
    For Each cel In Range("A1:A" & Range("A1").End(xlDown).Row)
        Set wb = Workbooks.Open(fn)
        wb.ActiveSheet.Name = cel.Value
        wb.ActiveSheet.Range("B1") = cel
        wb.SaveAs cel & "-" & wb.Name
        wb.Close
    Next cel
End Sub
0
 
LVL 7

Expert Comment

by:Jignesh Thar
ID: 38793933
Put below code in People.xlsm. Keep both files in same directory.
Sub CopyFilesWithName()
    strSurveyName = InputBox("Enter survey name", "Survey Name")
    For Each oCells In Range("A:A")
        If oCells.Value = "" Then
            End
        End If
        
        Workbooks.Open Filename:="SurveyForm.xlsm"
        ActiveCell.FormulaR1C1 = oCells.Value
        Sheets("Sheet1").Select
        Sheets("Sheet1").Name = oCells.Value
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & oCells.Value & "-" & strSurveyName & ".xlsm", FileFormat _
            :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        ActiveWindow.Close
    Next
End Sub

Open in new window

0
 
LVL 7

Expert Comment

by:Jignesh Thar
ID: 38793935
Attached is file with macro
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 1

Author Comment

by:Alex Campbell
ID: 38794181
Thanks, works well.  I will give you the points because it is so close to what I want, but I would appreciate it if you add a select the filename to be opened for the survey rather than having to hardcode it.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38794241
Have you tried the first one? It asks for the file name.
0
 
LVL 7

Expert Comment

by:Jignesh Thar
ID: 38794268
Try below code and attached file

Sub CopyFilesWithName()
    strSurveyName = Application.GetOpenFilename
    For Each oCells In Range("A:A")
        If oCells.Value = "" Then
            End
        End If
        
        Workbooks.Open Filename:=strSurveyName
        ActiveCell.FormulaR1C1 = oCells.Value
        Sheets(1).Select
        Sheets(1).Name = oCells.Value
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & oCells.Value & "-" & ActiveWorkbook.Name & ".xlsm", FileFormat _
            :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        ActiveWindow.Close
    Next
End Sub

Open in new window

People.xlsm
0
 
LVL 1

Author Comment

by:Alex Campbell
ID: 38794506
Perfect, just what I was looking for.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38794766
Hi, can you please tell me why the first one did not work?
0
 
LVL 1

Author Comment

by:Alex Campbell
ID: 38794793
It worked, but I had to know the name of the file.
The change showed the files in the directory and I could move to another directory.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38795349
That is exactly what the first one does. That one is

Sub generatefiles()
0
 
LVL 1

Author Comment

by:Alex Campbell
ID: 38795353
The first macro stopped and asked for the file name in a data entry box

The next one gave a listing of files
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38795381
No it did not. It  was the second one which "stopped and asked for the file name in a data entry box" and it was the the third one which you accepted. You did not try the first one which does ask for file name.
0
 
LVL 1

Author Comment

by:Alex Campbell
ID: 38795439
my apologies i missed that
0
 
LVL 1

Author Closing Comment

by:Alex Campbell
ID: 38798910
my apologies for missing your correct solution the first time
I also for help to resolve my error
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question