Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL VBA question
Hi EXCEL VBA Wizards!
I keep improving a template I use but each time I improve it, I need to retest it with data from a number of past examples to make sure I haven't compromised any functionality. In effect, I need to copy and paste various blocks of data from the same-named worksheet of an old file to the same-named worksheet (and locations) of the new template. I think I can do this via simple recording of the macro. The only thing I don't know is the code to have it, at the outset, ask me what the name of the other EXCEL file is and, then, how to tell it to go to that filename, before each copy command. Can you help me, please, with the specific coding to do this? I'm thinking something like: 1) have it ask the user via some sort of input message box, for the filename I want to use for copying into the current file that will contain the macro. 2) assign the response and identify it as "X" 3) go to filename X.xls Thank you, Jill |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL VBA question
Hi Jill
See if you can use this: Sub test() Dim F As Variant F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _ , "Select a file to copy into:") If F = False Then Exit Sub Workbooks.Open F End Sub HTH. Best wishes Harald "Grace" skrev i melding ... Hi EXCEL VBA Wizards! I keep improving a template I use but each time I improve it, I need to retest it with data from a number of past examples to make sure I haven't compromised any functionality. In effect, I need to copy and paste various blocks of data from the same-named worksheet of an old file to the same-named worksheet (and locations) of the new template. I think I can do this via simple recording of the macro. The only thing I don't know is the code to have it, at the outset, ask me what the name of the other EXCEL file is and, then, how to tell it to go to that filename, before each copy command. Can you help me, please, with the specific coding to do this? I'm thinking something like: 1) have it ask the user via some sort of input message box, for the filename I want to use for copying into the current file that will contain the macro. 2) assign the response and identify it as "X" 3) go to filename X.xls Thank you, Jill |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL VBA question
With a little learning on my part, this works nicely. Only a couple of
follow-up questions. Your macro codes the file as F. I know that before each copy operation, I don't want to re-open the file, but would rather activate it. I tried several versions of: Windows("F.xls").Activate and each yields a bug. What is the proper syntax for activating this file we call F, after it has been opened? One other thing, I use a similar command (that DOES work, for the name of the file with the macro in it. Specifically: Windows("test.xls").Activate and that works fine. However, if I save the template with a new name, it won't adjust to it. So, I need some way to tell it to always paste into the current filename, the one with the macro. What is the best way to define this, so I can activate it over and over again? I'm thinking of something like G = the current filename with this macro, then I activate G over and over again as I am pasting Great work. Thanks! "Harald Staff" wrote in message ... Hi Jill See if you can use this: Sub test() Dim F As Variant F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _ , "Select a file to copy into:") If F = False Then Exit Sub Workbooks.Open F End Sub HTH. Best wishes Harald "Grace" skrev i melding ... Hi EXCEL VBA Wizards! I keep improving a template I use but each time I improve it, I need to retest it with data from a number of past examples to make sure I haven't compromised any functionality. In effect, I need to copy and paste various blocks of data from the same-named worksheet of an old file to the same-named worksheet (and locations) of the new template. I think I can do this via simple recording of the macro. The only thing I don't know is the code to have it, at the outset, ask me what the name of the other EXCEL file is and, then, how to tell it to go to that filename, before each copy command. Can you help me, please, with the specific coding to do this? I'm thinking something like: 1) have it ask the user via some sort of input message box, for the filename I want to use for copying into the current file that will contain the macro. 2) assign the response and identify it as "X" 3) go to filename X.xls Thank you, Jill |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL VBA question
"Grace" skrev i melding
... I know that before each copy operation, I don't want to re-open the file, but would rather activate it. Usually one doesn't have to select or activate anything to move/copy stuff from one place to another. It just slows things down while looking ugly. Best wishes Harald |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL VBA question
Perhaps I am confused but I don't think you have answered my question. What
I do is copy data from a source file to the same location in a destination file. I do this for many different areas on many different tabs. So, I go to the source file (which you have called F) and I highlight, say, worksheet tab 1, then a range of cells on that tab. Then I activate the destination file, find the same tab and same cell area and paste it. Then, I go back to the source file and repeat for another tab and area. So, each time I am toggling between two files. I was trying to find a way to call the source file each time. I thought the command: Windows(F.xls).Activate would work but it bombs the macro out. What is the right syntax to toggle back to that sheet? Thanks, Grace "Harald Staff" wrote in message ... "Grace" skrev i melding ... I know that before each copy operation, I don't want to re-open the file, but would rather activate it. Usually one doesn't have to select or activate anything to move/copy stuff from one place to another. It just slows things down while looking ugly. Best wishes Harald |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL VBA question
I don't know who this mona001 is, who has posted within my thread. My reply
was to Harald Staff and I hope he will reply once more. Thanks! "Grace" wrote in message ... Perhaps I am confused but I don't think you have answered my question. What I do is copy data from a source file to the same location in a destination file. I do this for many different areas on many different tabs. So, I go to the source file (which you have called F) and I highlight, say, worksheet tab 1, then a range of cells on that tab. Then I activate the destination file, find the same tab and same cell area and paste it. Then, I go back to the source file and repeat for another tab and area. So, each time I am toggling between two files. I was trying to find a way to call the source file each time. I thought the command: Windows(F.xls).Activate would work but it bombs the macro out. What is the right syntax to toggle back to that sheet? Thanks, Grace "Harald Staff" wrote in message ... "Grace" skrev i melding ... I know that before each copy operation, I don't want to re-open the file, but would rather activate it. Usually one doesn't have to select or activate anything to move/copy stuff from one place to another. It just slows things down while looking ugly. Best wishes Harald |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL VBA question
Grace
I didn't answer your question because I believed it was the wrong solution to a problem. What you want done is to copy from one place to another, not to activate windows. So I still won't tell you, but see if this does what you want: Sub test() Dim F As Variant Dim wbSource As Workbook Dim wbTarget As Workbook Set wbTarget = ThisWorkbook 'or ActiveWorkbook F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _ , "Select a file to copy into:") If F = False Then Exit Sub Set wbSource = Workbooks.Open(F) wbSource.Sheets(1).Range("A1:F14").Copy wbTarget.Sheets(2).Range("D1:I14") wbSource.Saved = True wbSource.Close End Sub OR: Do you need to manually select a range in the middle of your macro ? HTH. Best wishes Harald "Grace" skrev i melding ... Perhaps I am confused but I don't think you have answered my question. What I do is copy data from a source file to the same location in a destination file. I do this for many different areas on many different tabs. So, I go to the source file (which you have called F) and I highlight, say, worksheet tab 1, then a range of cells on that tab. Then I activate the destination file, find the same tab and same cell area and paste it. Then, I go back to the source file and repeat for another tab and area. So, each time I am toggling between two files. I was trying to find a way to call the source file each time. I thought the command: Windows(F.xls).Activate would work but it bombs the macro out. What is the right syntax to toggle back to that sheet? Thanks, Grace "Harald Staff" wrote in message ... "Grace" skrev i melding ... I know that before each copy operation, I don't want to re-open the file, but would rather activate it. Usually one doesn't have to select or activate anything to move/copy stuff from one place to another. It just slows things down while looking ugly. Best wishes Harald |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Macro/VB Question DDE Question | Excel Worksheet Functions | |||
Excel 2003 to Excel 2007 Question | Excel Worksheet Functions | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions | |||
Statistical Excel Function Question within Excel 2000... | Excel Worksheet Functions |