Copy Worksheets from existing Workbooks to new Workbook

[pendle666]

Hello

have a template workbook shakeout.xltm - what I want to do when running my macro is:

on click: open file dialog box which is at P:\data\misc

It to pause while I pick the xlsx file I want to use (there will be new files each month, so I can't hard code the filename)

When I've picked it, the file opens and then copies from the newly opened file to the shakeout.xltm workbook.

Then I'll repeat this so the next file is opened and copied.

I've been looking for examples to get me started, but I'm only getting quite old results which are giving errors.

I've got as far as the dialog box opening up, but it won't go to where I want it to.

[pre]ChDir "P:\data\misc"

workbookName = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")[/pre]

Can anyone help please?

thank you for helping

____________
Pendle

 

[SkipVought]

Hi,

“pick the xlsx file I want to use (there will be new files each month..”

Well it has some standard form, I’d guess??? Care to share?

You’re not gonna get that path in this dialog.

And what about the “next file?”

“When I've picked it, the file opens and then copies from the newly opened file to the shakeout.xltm workbook.”

I’d do all this via FSO in a loop, I think.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

If the file is in other drive than current excel directory, you need [tt]ChDrive+ChDir[/tt].

combo

 

[pendle666]

Hi
Well one file will be called something like "FBP report.xlsx" and the other "Workplan report.xlsx" - it depends who has run those reports as to what they get named. But they'll always be put into P:\misc\true-ups\[month]

So basically I want the worksheets from these reports (there is only one in each) copied to the shakeout.xltm workbook which is already open.

When the template is opened, it'll be saved into the folder for the month with these other two.

Does that give a better idea of what I'm trying to do?

thanks

thank you for helping

____________
Pendle

 

[SkipVought]

“it depends who has run those reports as to what they get named.”

But if this is all run by code (ie, opening and saving) the file names can be known, yes?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[pendle666]

Well yes, I'll know what they are because there will only be 2 files in that folder.

But in October someone might have called a file FBP report.xlsx but in November someone else might have run the report and called it FBP report November.xlsx

I could rename the files manually but I may as well copy the worksheets manually while I'm in there. But I'd like to give this to someone to be able to do themselves.

thank you for helping

____________
Pendle

 

[SkipVought]

“But in October someone might have called a file FBP report.xlsx but in November someone else might have run the report and called it FBP report November.xlsx”

Not if your program explicitly names these two files when it saves them at the end of your procedure!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

So where is the problem?
The steps:
1. user picks the file (GetOpenFilename dialog),
2. code tests if user selected file (GetOpenFilename returns non-zero string),
3. Workbooks.Open opens file and returns workbook as Workbook,
4. YourSheetReferenceObject.Copy Before/After: Target Worksheetreference copies sheet.

combo

 

[pendle666]

My problem is that I couldn't find code for what I wanted to do. I didn't know about [pre]Application.GetOpenFilename[/pre] I've now found code for opening a file and then used the macro recorder for the rest.

This is what I've ended up with eventually:

[pre]Sub OpenandCopyreport()


ChDrive "P"
ChDir "\data\misc"


Dim MyFile As String

MyFile = Application.GetOpenFilename()

Workbooks.Open fileName:=MyFile


Sheets("Sheet1").Select
Sheets("Sheet1").Move Before:=Workbooks("shakeout1").Sheets(1)

End Sub
[/pre]

thank you for helping

____________
Pendle