Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a range from one workbook to another workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a range from one workbook to another workbook
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 "caldog" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying a range from one workbook to another workbook | Excel Programming | |||
Copying A Worksheet From Each Open Workbook to an new Workbook | Excel Worksheet Functions | |||
Copying range to other workbook | Excel Programming | |||
Problem copying a range to a different workbook | Excel Programming | |||
Copying a Filtered Range from a Sheet in another Workbook | Excel Programming |