Copying a range from one workbook to another workbook

[Guest]

I know there is probably a simple answer to this problem, but fot the life of
me, I can't find it.

Here is my code:

Private Sub SingleValues_Click()
Workbooks.Open Filename:="C:\Excel\Data1.xls"

'Refresh Data
'Sheets("Singles").Select
'Selection.QueryTable.Refresh BackgroundQuery:=False
'Sheets("Doubles").Select
'Selection.QueryTable.Refresh BackgroundQuery:=False

'Copy Data
Windows("Data1.xls").Activate
Sheets("Singles").Select
Range("A9:F51").Copy
Windows("Data2.xls").Activate
Sheets("Single Game Stats").Select

'Error happens at next line
Range("A9:F51").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'Sort Download
Windows("Jai-Alai.xls").Activate
Worksheets("Single Game Stats").Range("A9:F51").Select
Selection.Sort Key1:=Range("B9"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Windows("Data1.xls").Activate
ActiveWorkbook.Close
End Sub

Thanks for the help

Steve

 

[EvolBob]

Two things you have to do:
1st check all the workbook and sheet names and make sure they are the same as the ones you are using in your code.
2nd Don't believe it, use code to get this data, like msgbox thiswork.name and Activesheet.name - paste this!

And if there is a question in your post I didn't see one - anyway when copying you rarely need to select.
If your names are wrong, you may have to modify this to get it to work.

['[Data1.xls]Sheet1'!A9:F51].copy ['[Data2.xls]Sheet1'!A9]

'Ok now I see you only wanted to copy values - so use this as the above copies everything:

['[Data2.xls]Sheet1'!A1].range("A9:F51").value = ['[Data1.xls]Sheet1'!A9:F51].value

This bit of the code works just like an offset function does > range("A9:F51")
So if you were to append to end of data in the other sheet/workbook then use this version:

['[Data2.xls]Sheet1'!A65536].End(xlup).range("A2:F44").value = ['[Data1.xls]Sheet1'!A9:F51].value

Note neither of these suggestions required the workbook or the sheets to be selected or active - they do need to be open for this to work. There are ways around this as well, but that is another story.


Regards
Robert McCurdy

I know there is probably a simple answer to this problem, but fot the life of
me, I can't find it.

Here is my code:

Private Sub SingleValues_Click()
Workbooks.Open Filename:="C:\Excel\Data1.xls"

'Refresh Data
'Sheets("Singles").Select
'Selection.QueryTable.Refresh BackgroundQuery:=False
'Sheets("Doubles").Select
'Selection.QueryTable.Refresh BackgroundQuery:=False

'Copy Data
Windows("Data1.xls").Activate
Sheets("Singles").Select
Range("A9:F51").Copy
Windows("Data2.xls").Activate
Sheets("Single Game Stats").Select

'Error happens at next line
Range("A9:F51").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'Sort Download
Windows("Jai-Alai.xls").Activate
Worksheets("Single Game Stats").Range("A9:F51").Select
Selection.Sort Key1:=Range("B9"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Windows("Data1.xls").Activate
ActiveWorkbook.Close
End Sub

Thanks for the help

Steve