INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Copy Worksheets from existing Workbooks to new Workbook

Copy Worksheets from existing Workbooks to new Workbook

(OP)
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.

ChDir "P:\data\misc"

workbookName = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*") 


Can anyone help please?

thank you for helping

____________
Pendle

RE: Copy Worksheets from existing Workbooks to new Workbook

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Copy Worksheets from existing Workbooks to new Workbook

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

combo

RE: Copy Worksheets from existing Workbooks to new Workbook

(OP)
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

RE: Copy Worksheets from existing Workbooks to new Workbook

“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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Copy Worksheets from existing Workbooks to new Workbook

(OP)
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

RE: Copy Worksheets from existing Workbooks to new Workbook

“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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Copy Worksheets from existing Workbooks to new Workbook

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

RE: Copy Worksheets from existing Workbooks to new Workbook

(OP)
My problem is that I couldn't find code for what I wanted to do. I didn't know about
Application.GetOpenFilename 
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:

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
 

thank you for helping

____________
Pendle

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close