Hi Ron,

Since you were such an angel with the previous macro....I actually
thought the previous task was impossible to do but you managed to do
the impossible.

I wondered if when it had finished processeding each macro it open up
another spreadsheet and filled in the data and time within a range of
cells,

So in Column A I had the value of the previous sheet which contains
the name in Cells A1. I want the macro to look up the value from Cell
A1 of the template file and then search for this in column A of
another record sheet spreadsheet called "RecordSheet.xls" and then
next to this in Column B it will put the date and then in Column C the
time.

Do you get what I mean?

I just want a record of when the files were created to save me having
to enter the data into another sheet.

Please let me know if you need something else,

THanks so much

Andrea

On 11 May, 20:26, "Ron de Bruin" wrote:
You can use this if you want to use range values

MyPath = Sheets("Sheet1").Range("A1").Value

You can use this also for the other things

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

wrote in ooglegroups.com...

Hi Ron,

How could I have the folders locations selected by look at Cells A1

I tried using MYPATH= Cells ("A1").Value for example and gave up
because it didnt work for me.

I would like th template file value selected from cells A2 of the
current file.

I also want the output placed a folder using the value from A3 so the
user doesnt have to open the macro and make any changes.

I have saved the entire macro in a file and named the file macro1.

I appreciate your help,

Many thanks,

Andrea

On 10 May, 19:16, "Ron de Bruin" wrote:



Hi Andrea

Suggestion: please reply above the thread.
Now everybody must scroll down to read your reply.

You must be earning millions for doing this!,

Nothing, it is my hobby

When it has finished processing all the files I am getting an error
message.

There is no error trapping in this macro
Maybe a file with no sheet Products or a empty cell A1
or characters in A1 that are not allowed in a file name

Could I perhaps receive a message telling me it has completed all the
files in the folder? Just to make it more user friendly.

After this line

Loop

you can add this line

MsgBox "Done with running this macro"

P.S Where have you been all my life???!!!

In the Netherlands <vbg

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

wrote in oglegroups.com...

On 10 May, 18:31, "Ron de Bruin" wrote:

See my reply with a new macro

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

wrote in messagenews:1178817815.668780..166100@p77g2000hsh. googlegroups.com...
On May 10, 4:37 pm, "Ron de Bruin" wrote:
OK, here we go

Change a few things in my basic example

MyPath = "C:\Users\Ron\test"
Path of the folder with the files

Set DestWB = Workbooks.Open("C:\Users\Ron\template.xls")
Path and name of your template file

Application.Run "template.xls!MyMacroName"
Name of the template filer and the macro you want to run in this file

This is the save line
DestWB.SaveAs foldername & DestWB.Sheets("Products").Range("A1").Value & ".xls"
I think it is what you want, it save with the cell value of A1 in the sheet Products

Sub Example1()
Dim mybook As Workbook
Dim DestWB As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim oApp As Object
Dim oFolder
Dim foldername

SaveDriveDir = CurDir
MyPath = "C:\Users\Ron\test"

ChDrive MyPath
ChDir MyPath

FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Set oApp = CreateObject("Shell.Application")

Application.ScreenUpdating = False

Do While FNames < ""

'open the template file
Set DestWB = Workbooks.Open("C:\Users\Ron\template.xls")

Set mybook = Workbooks.Open(FNames)

'Copy sheets to your template workbook
mybook.Worksheets.Copy after:= _
DestWB.Sheets(DestWB.Sheets.Count)

'run the macro in your tempate file
Application.Run "template.xls!MyMacroName"

'Browse to the folder
Set oFolder = oApp.BrowseForFolder(0, "Select folder to save the files", 512)
If Not oFolder Is Nothing Then
foldername = oFolder.Self.Path
If Right(foldername, 1) < "\" Then
foldername = foldername & "\"
End If

Application.DisplayAlerts = False
DestWB.SaveAs foldername & DestWB.Sheets("Products").Range("A1").Value & ".xls"
Application.DisplayAlerts = True
End If

'close files
mybook.Close False
DestWB.Close False

'go to the next file
FNames = Dir()
Loop

ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

wrote in ooglegroups.com...
On 9 May, 23:03, wrote:
On 9 May, 22:28, "Ron de Bruin" wrote:

Ok, now I understand you.

Is it a real template (xlt) or a normal workbook that you use to copy the sheets in ?.

Bed time here now.
I will make a example for you tomorrow after work

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

wrote in oglegroups.com...
On 9 May, 21:38, "Ron de Bruin" wrote:
You can use this basic macro to collect all sheet in the folder "C:\Users\Ron\test"
Change it to your folder and run the macro in your template file

Post back if it is working and then we add the save code

Sub Example11()
Dim basebook As Workbook
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Users\Ron\test"

ChDrive MyPath
ChDir MyPath

FNames = Dir("*.xlsx")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook

Do While FNames < ""
Set mybook = Workbooks.Open(FNames)

mybook.Worksheets.Copy after:= _
basebook.Sheets(basebook.Sheets.Count)

mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

wrote in ooglegroups.com...
On 9 May, 21:02, "Ron de Bruin" wrote:
How many sheet you have in the workbooks ?
How do you want to name the sheets in the new workbook?

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

wrote in glegroups.com...
On 9 May, 20:20, "Ron de Bruin" wrote:
Hi Andrea

Start with this page for a one sheet example or use the copy a range example from all
sheetshttp://www.rondebruin.nl/fso.htm

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

wrote in oglegroups.com...
Hi

I have multiple XLS files located in one folder.

Each week I have to manually go into the files and then copy all the
worksheets into my template.

I then run a macro in the template and when done save the file as a
name in the appropriate folder.

I repeat this for all my spreadsheets.

Is there a macro that can open all the spreadsheets in a particular
folder one by one then copy and paste all the sheets into another
spreadsheet which shall remain the same (it's a template),

I would then like the macro to call the macro in my template sheet
then save the spreadsheet in another location with the name of the
spreadsheet being the value in Cell A1 of the sheet "Products".

I really hope this makes sense to someone,

Thanks for your help,

Andrea- Hide quoted text -

- Show quoted text -

Hi Ron,

I have found out how to copy the sheets but I cannot find a macro to
meet my exact needs.....

I have say 10 files. I want them opened 1 by 1 individually and all
the sheets copied into my template file located in a

...

read more »- Hide quoted text -

- Show quoted text -