| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting from another file
I am combining information from one file to another. I ran a macro to cut and paste the information and it works except that the macro will stop and say the standard 'there is information on the clipboard, do you want to be able to paste..." that I have to answer yes to before it will transfer the information to the second file. Is there something I can wirte in the coding so I can bypass this? My macro is
Sheets("Graph Data").Select Range("b134:G258").Select Workbooks.Open Filename:= _ "C:\Retail\DirectConsolidated.xls" Sheets("Graph Data").Select Range("B4:G128").Select Selection.Copy ActiveWindow.Close ActiveSheet.Past I would appreciate any help!! Thanks Marie |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extracting from another file
Sheets("Graph Data").Select
Range("b134:G258").Select Workbooks.Open Filename:= _ "C:\Retail\DirectConsolidated.xls" Sheets("Graph Data").Select Range("B4:G128").Select Selection.Copy ActiveSheet.Past application.CutCopyMode = False ActiveWindow.Close -- Regards, Tom Ogilvy "Marie" wrote in message ... I am combining information from one file to another. I ran a macro to cut and paste the information and it works except that the macro will stop and say the standard 'there is information on the clipboard, do you want to be able to paste..." that I have to answer yes to before it will transfer the information to the second file. Is there something I can wirte in the coding so I can bypass this? My macro is Sheets("Graph Data").Select Range("b134:G258").Select Workbooks.Open Filename:= _ "C:\Retail\DirectConsolidated.xls" Sheets("Graph Data").Select Range("B4:G128").Select Selection.Copy ActiveWindow.Close ActiveSheet.Past I would appreciate any help!! Thanks Marie |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extracting from another file
Thanks Tom, It worked!! But...now it is asking me if I want to save the file I am extracting the information from. What code do I need to use to say yes to that?
"Tom Ogilvy" wrote: Sheets("Graph Data").Select Range("b134:G258").Select Workbooks.Open Filename:= _ "C:\Retail\DirectConsolidated.xls" Sheets("Graph Data").Select Range("B4:G128").Select Selection.Copy ActiveSheet.Past application.CutCopyMode = False ActiveWindow.Close -- Regards, Tom Ogilvy "Marie" wrote in message ... I am combining information from one file to another. I ran a macro to cut and paste the information and it works except that the macro will stop and say the standard 'there is information on the clipboard, do you want to be able to paste..." that I have to answer yes to before it will transfer the information to the second file. Is there something I can wirte in the coding so I can bypass this? My macro is Sheets("Graph Data").Select Range("b134:G258").Select Workbooks.Open Filename:= _ "C:\Retail\DirectConsolidated.xls" Sheets("Graph Data").Select Range("B4:G128").Select Selection.Copy ActiveWindow.Close ActiveSheet.Past I would appreciate any help!! Thanks Marie |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extracting from another file
Sheets("Graph Data").Select
Range("b134:G258").Select Workbooks.Open Filename:= _ "C:\Retail\DirectConsolidated.xls" Sheets("Graph Data").Select Range("B4:G128").Select Selection.Copy ActiveSheet.Paste application.CutCopyMode = False ActiveWindow.Close SaveChanges:=True I am not sure if you want to copy to the workbook with Graph Data or copy to DirectConsolidated.xls which might also have a sheet named Graph Data Right now, you copy from the Graph Data sheet of DirectConsolidated.xls and paste right back to that same sheet The original Graph Data sheet is not affected. Assume you want to copy from Graph Data in the original workbook and paste to Graph Data in DirectConsolidate.xls Dim sh as Worksheet Dim wkbk as Workbook Sheets("Graph Data").Select set sh = ActiveSheet set wkbk = Workbooks.Open( Filename:= _ "C:\Retail\DirectConsolidated.xls") sh.Range("b134:G258").Copy _ Destination:=wkbk.Worksheets("Graph Data").Range("B4:G128") wkbk.Close SaveChanges:=True -- Regards, Tom Ogilvy "Marie" wrote in message ... Sorry, I just realized it didn't download the information. I think it has something to do with not closing the file I am extracting from...please help! "Marie" wrote: Thanks Tom, It worked!! But...now it is asking me if I want to save the file I am extracting the information from. What code do I need to use to say yes to that? "Tom Ogilvy" wrote: Sheets("Graph Data").Select Range("b134:G258").Select Workbooks.Open Filename:= _ "C:\Retail\DirectConsolidated.xls" Sheets("Graph Data").Select Range("B4:G128").Select Selection.Copy ActiveSheet.Past application.CutCopyMode = False ActiveWindow.Close -- Regards, Tom Ogilvy "Marie" wrote in message ... I am combining information from one file to another. I ran a macro to cut and paste the information and it works except that the macro will stop and say the standard 'there is information on the clipboard, do you want to be able to paste..." that I have to answer yes to before it will transfer the information to the second file. Is there something I can wirte in the coding so I can bypass this? My macro is Sheets("Graph Data").Select Range("b134:G258").Select Workbooks.Open Filename:= _ "C:\Retail\DirectConsolidated.xls" Sheets("Graph Data").Select Range("B4:G128").Select Selection.Copy ActiveWindow.Close ActiveSheet.Past I would appreciate any help!! Thanks Marie |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extracting from another file
"Tom Ogilvy" wrote: Sheets("Graph Data").Select Range("b134:G258").Select Workbooks.Open Filename:= _ "C:\Retail\DirectConsolidated.xls" Sheets("Graph Data").Select Range("B4:G128").Select Selection.Copy ActiveSheet.Paste application.CutCopyMode = False ActiveWindow.Close SaveChanges:=True I am not sure if you want to copy to the workbook with Graph Data or copy to DirectConsolidated.xls which might also have a sheet named Graph Data Right now, you copy from the Graph Data sheet of DirectConsolidated.xls and paste right back to that same sheet The original Graph Data sheet is not affected. Assume you want to copy from Graph Data in the original workbook and paste to Graph Data in DirectConsolidate.xls Dim sh as Worksheet Dim wkbk as Workbook Sheets("Graph Data").Select set sh = ActiveSheet set wkbk = Workbooks.Open( Filename:= _ "C:\Retail\DirectConsolidated.xls") sh.Range("b134:G258").Copy _ Destination:=wkbk.Worksheets("Graph Data").Range("B4:G128") wkbk.Close SaveChanges:=True -- Regards, Tom Ogilvy I hate to ask again but I am going nuts with this. Now it is highlighing the := after the word destination and saying "expected expression" Marie "Marie" wrote in message ... Sorry, I just realized it didn't download the information. I think it has something to do with not closing the file I am extracting from...please help! "Marie" wrote: Thanks Tom, It worked!! But...now it is asking me if I want to save the file I am extracting the information from. What code do I need to use to say yes to that? "Tom Ogilvy" wrote: Sheets("Graph Data").Select Range("b134:G258").Select Workbooks.Open Filename:= _ "C:\Retail\DirectConsolidated.xls" Sheets("Graph Data").Select Range("B4:G128").Select Selection.Copy ActiveSheet.Past application.CutCopyMode = False ActiveWindow.Close -- Regards, Tom Ogilvy "Marie" wrote in message ... I am combining information from one file to another. I ran a macro to cut and paste the information and it works except that the macro will stop and say the standard 'there is information on the clipboard, do you want to be able to paste..." that I have to answer yes to before it will transfer the information to the second file. Is there something I can wirte in the coding so I can bypass this? My macro is Sheets("Graph Data").Select Range("b134:G258").Select Workbooks.Open Filename:= _ "C:\Retail\DirectConsolidated.xls" Sheets("Graph Data").Select Range("B4:G128").Select Selection.Copy ActiveWindow.Close ActiveSheet.Past I would appreciate any help!! Thanks Marie |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extracting from another file
Went to all the trouble of creating two workbooks, creating a directory
c:\Retail and setting them all up to match the code. Ran the code and it worked just as expected - exactly as written: Sub AAB() Dim sh As Worksheet Dim wkbk As Workbook Sheets("Graph Data").Select Set sh = ActiveSheet Set wkbk = Workbooks.Open(FileName:= _ "C:\Retail\DirectConsolidated.xls") sh.Range("b134:G258").Copy _ Destination:=wkbk.Worksheets( _ "Graph Data").Range("B4:G128") wkbk.Close SaveChanges:=True End Sub Can't fix what isn't broken. -- Regards, Tom Ogilvy "Marie" wrote in message ... "Tom Ogilvy" wrote: Sheets("Graph Data").Select Range("b134:G258").Select Workbooks.Open Filename:= _ "C:\Retail\DirectConsolidated.xls" Sheets("Graph Data").Select Range("B4:G128").Select Selection.Copy ActiveSheet.Paste application.CutCopyMode = False ActiveWindow.Close SaveChanges:=True I am not sure if you want to copy to the workbook with Graph Data or copy to DirectConsolidated.xls which might also have a sheet named Graph Data Right now, you copy from the Graph Data sheet of DirectConsolidated.xls and paste right back to that same sheet The original Graph Data sheet is not affected. Assume you want to copy from Graph Data in the original workbook and paste to Graph Data in DirectConsolidate.xls Dim sh as Worksheet Dim wkbk as Workbook Sheets("Graph Data").Select set sh = ActiveSheet set wkbk = Workbooks.Open( Filename:= _ "C:\Retail\DirectConsolidated.xls") sh.Range("b134:G258").Copy _ Destination:=wkbk.Worksheets("Graph Data").Range("B4:G128") wkbk.Close SaveChanges:=True -- Regards, Tom Ogilvy I hate to ask again but I am going nuts with this. Now it is highlighing the := after the word destination and saying "expected expression" Marie "Marie" wrote in message ... Sorry, I just realized it didn't download the information. I think it has something to do with not closing the file I am extracting from...please help! "Marie" wrote: Thanks Tom, It worked!! But...now it is asking me if I want to save the file I am extracting the information from. What code do I need to use to say yes to that? "Tom Ogilvy" wrote: Sheets("Graph Data").Select Range("b134:G258").Select Workbooks.Open Filename:= _ "C:\Retail\DirectConsolidated.xls" Sheets("Graph Data").Select Range("B4:G128").Select Selection.Copy ActiveSheet.Past application.CutCopyMode = False ActiveWindow.Close -- Regards, Tom Ogilvy "Marie" wrote in message ... I am combining information from one file to another. I ran a macro to cut and paste the information and it works except that the macro will stop and say the standard 'there is information on the clipboard, do you want to be able to paste..." that I have to answer yes to before it will transfer the information to the second file. Is there something I can wirte in the coding so I can bypass this? My macro is Sheets("Graph Data").Select Range("b134:G258").Select Workbooks.Open Filename:= _ "C:\Retail\DirectConsolidated.xls" Sheets("Graph Data").Select Range("B4:G128").Select Selection.Copy ActiveWindow.Close ActiveSheet.Past I would appreciate any help!! Thanks Marie |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| extracting a date from a comment field in an excel file | Excel Discussion (Misc queries) | |||
| Extracting data from one xls file and input into another xls file | Excel Discussion (Misc queries) | |||
| Extracting First Name | New Users to Excel | |||
| Extracting text from one file to another | Excel Discussion (Misc queries) | |||
| Extracting File Names and Properties | Excel Worksheet Functions | |||
Linear Mode
