Extracting from another file

M

Marie

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
 
T

Tom Ogilvy

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 said:
I am combining information from one file to another. I ran a macro to cut
Click to expand...
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
 
M

Marie

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?
 
T

Tom Ogilvy

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 said:
Sorry, I just realized it didn't download the information. I think it has
Click to expand...
something to do with not closing the file I am extracting from...please
help!file I am extracting the information from. What code do I need to use to say
yes to that?
 
M

Marie

Tom Ogilvy said:
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







something to do with not closing the file I am extracting from...please
help!
file I am extracting the information from. What code do I need to use to say
yes to that?
Click to expand...
 
T

Tom Ogilvy

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.
 
Top