i need to copy a worksheet to another workbook. the problem is that i dont know what that workbook will be until i run my macro. so need a macro to ask the user to point to an excel workbook, then copy the worksheet over.
i know how to create a worksheet but not how to copy it to the other workbook (in VBA).
can someone help?
Can you better explain "point to an excel workbook"? Do you want...Originally Posted by dmcgov
A.) a file picker dialog so the user can select an Excel file to open?
or
B.) a dialog with a list of already opened Excel files?
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
Re: need to copy sheet to another workbook
Hye Alpha,
I also have the same problem here. For example we have File A and File B. Inside File A, the Price is located in column C while in File B the Price is located in column F. The File A we can call it as the master File. So the Price data in File B want to be copy exactly in File A but located inside column C. Hope you can help.
Thank you.
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Re: need to copy sheet to another workbook
Okay noted.
Im sorry. Will post a new thread regarding my question.
Thank you.
Thank you for understanding.Okay noted.
Im sorry. Will post a new thread regarding my question.
Thank you.
I would pick A, a file picker dialog box
thanks
so below is the code that i am using. it doesnt work though, it fails on this line: Sheets("formulas").Copy Before:=Workbooks(FileName).Sheets("a")
Please Login or Register to view this content.
Try this. ThisWorkbook is the workbook with the macro.
Please Login or Register to view this content.
when i run it, i get a "run time error '9': subscript out of range.
what do you think is wrong with this macro?
You haven't explained very well which workbook you want to copy from and which workbook you want to copy to.
ThisWorkbook.Sheets("formulas").Copy Before:=Workbooks(FileName).Sheets("a")
This copies a sheet called "formulas" located in the workbook that contains the macro code and pastes it in the newly opened workbook after a sheet called "a". For all I know, that could be backwards from what you really intend.
The error is because one of the two workbooks does not exist, or one of the two named worksheets doesn't exist. I suspect it's one of the named sheets isn't in it's qualified workbook.
ok so the workbook that has the macros in it, is named "estimate test"
the workbook that it is going to (for testing purposes) is called "102 angler with macros" but this is the one that will change based on the end user picking a file to put the sheet "formulas" into. this needs to be dynamic. and 102 angler has the sheet "a" in it.
as for "estimate test", this will always be that name.
Does "estimate test" have a sheet named exactly "formulas" and does "102 angler with macros" have a sheet named exactly "a" ?
If yes, then give this a try.
ThisWorkbook.Sheets("formulas").Copy Before:=ActiveWorkbook.Sheets("a")
Last edited by AlphaFrog; 12-08-2015 at 12:36 PM.
Yes, I just checked both workbooks. estimate test has "formulas" and 102 angler has "a".
i can upload both files if needed
Give this a try.
ThisWorkbook.Sheets("formulas").Copy Before:=ActiveWorkbook.Sheets("a")
i tried that and it works!!! thanks so much
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks