Solved

Duplicate and name workbooks on list of names

Posted on 2013-01-18
15
387 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:Alex972
  • 6
  • 5
  • 3
15 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Attached is file with macro
0
 

Author Comment

by:Alex972
Comment Utility
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
Comment Utility
Have you tried the first one? It asks for the file name.
0
 
LVL 7

Expert Comment

by:Jignesh Thar
Comment Utility
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
 

Author Comment

by:Alex972
Comment Utility
Perfect, just what I was looking for.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
Hi, can you please tell me why the first one did not work?
0
 

Author Comment

by:Alex972
Comment Utility
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
Comment Utility
That is exactly what the first one does. That one is

Sub generatefiles()
0
 

Author Comment

by:Alex972
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Alex972
Comment Utility
my apologies i missed that
0
 

Author Closing Comment

by:Alex972
Comment Utility
my apologies for missing your correct solution the first time
I also for help to resolve my error
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now