Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy problem between worksheets
My understanding of fully qualified ranges is a little flimsy. Perhaps this
is why the copy operation in the last line of this code fails with an "Object doesn't support this property or method". I would be grateful if someone could point out where I'm going wrong? Is it the variable typing, the qualification of the ranges or am I just using the wrong syntax? Thanks! Sub procOpenBook() Dim Book1 As Workbook 'target book Dim i As Long ' loop variable Dim wbName As Variant 'file to be opened Dim shtlastcell As Long 'cell on target sheet at which rows will be inserted Dim selRows As Long 'number of elected rows on source sheet Dim srcSheetName As String ' name of last Dim srcSheetRng As Range 'selected range on source sheet Dim srcBook As Workbook ' source workbook Set srcBook = ActiveWorkbook Set srcSheetRng = ActiveWindow.RangeSelection srcSheetName = ActiveSheet.Name selRows = Selection.Rows.Count wbName = Application.GetOpenFilename _ (FileFilter:="microsoft excel files (*.xls), *.xls", _ Title:="Get File", MultiSelect:=False) If wbName < False Then Set Book1 = Workbooks.Open(wbName) Else Exit Sub End If Book1.Sheets(1).Activate shtlastcell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To selRows Rows(shtlastcell + i).Insert (xlShiftDown) Next srcBook.srcSheetName.Range(srcSheetRng).Copy Destination:=Book1.Sheets(1).Range("A" & shtlastcell + 1) End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy problem between worksheets
I believe that if you change this:
srcSheetName = ActiveSheet.Name To this: srcSheetName = ActiveSheet It should work. Otherwise, you will have to refer to it as: Sheets(srcSheetName) to make it work as a destination sheet. but you can do one or the other and it should work. "Project Mangler" wrote in message ... My understanding of fully qualified ranges is a little flimsy. Perhaps this is why the copy operation in the last line of this code fails with an "Object doesn't support this property or method". I would be grateful if someone could point out where I'm going wrong? Is it the variable typing, the qualification of the ranges or am I just using the wrong syntax? Thanks! Sub procOpenBook() Dim Book1 As Workbook 'target book Dim i As Long ' loop variable Dim wbName As Variant 'file to be opened Dim shtlastcell As Long 'cell on target sheet at which rows will be inserted Dim selRows As Long 'number of elected rows on source sheet Dim srcSheetName As String ' name of last Dim srcSheetRng As Range 'selected range on source sheet Dim srcBook As Workbook ' source workbook Set srcBook = ActiveWorkbook Set srcSheetRng = ActiveWindow.RangeSelection srcSheetName = ActiveSheet.Name selRows = Selection.Rows.Count wbName = Application.GetOpenFilename _ (FileFilter:="microsoft excel files (*.xls), *.xls", _ Title:="Get File", MultiSelect:=False) If wbName < False Then Set Book1 = Workbooks.Open(wbName) Else Exit Sub End If Book1.Sheets(1).Activate shtlastcell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To selRows Rows(shtlastcell + i).Insert (xlShiftDown) Next srcBook.srcSheetName.Range(srcSheetRng).Copy Destination:=Book1.Sheets(1).Range("A" & shtlastcell + 1) End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy problem between worksheets
Sorry, forgot the Set part.
To this: Set srcSheetName = ActiveSheet "Project Mangler" wrote in message ... My understanding of fully qualified ranges is a little flimsy. Perhaps this is why the copy operation in the last line of this code fails with an "Object doesn't support this property or method". I would be grateful if someone could point out where I'm going wrong? Is it the variable typing, the qualification of the ranges or am I just using the wrong syntax? Thanks! Sub procOpenBook() Dim Book1 As Workbook 'target book Dim i As Long ' loop variable Dim wbName As Variant 'file to be opened Dim shtlastcell As Long 'cell on target sheet at which rows will be inserted Dim selRows As Long 'number of elected rows on source sheet Dim srcSheetName As String ' name of last Dim srcSheetRng As Range 'selected range on source sheet Dim srcBook As Workbook ' source workbook Set srcBook = ActiveWorkbook Set srcSheetRng = ActiveWindow.RangeSelection srcSheetName = ActiveSheet.Name selRows = Selection.Rows.Count wbName = Application.GetOpenFilename _ (FileFilter:="microsoft excel files (*.xls), *.xls", _ Title:="Get File", MultiSelect:=False) If wbName < False Then Set Book1 = Workbooks.Open(wbName) Else Exit Sub End If Book1.Sheets(1).Activate shtlastcell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To selRows Rows(shtlastcell + i).Insert (xlShiftDown) Next srcBook.srcSheetName.Range(srcSheetRng).Copy Destination:=Book1.Sheets(1).Range("A" & shtlastcell + 1) End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy problem between worksheets
JLGWhiz,
Thanks for the reply. I couldn't get the code to work but you put me on the right track: Workbooks(srcBook.Name).Sheets(srcSheetName).Range (srcSheetRng.Address).Copy ActiveSheet.Paste Destination:=Workbooks(Book1.Name).Sheets(1).Range ("A" & shtlastcell + 1) Thanks for the help! "JLGWhiz" wrote in message ... Sorry, forgot the Set part. To this: Set srcSheetName = ActiveSheet "Project Mangler" wrote in message ... My understanding of fully qualified ranges is a little flimsy. Perhaps this is why the copy operation in the last line of this code fails with an "Object doesn't support this property or method". I would be grateful if someone could point out where I'm going wrong? Is it the variable typing, the qualification of the ranges or am I just using the wrong syntax? Thanks! Sub procOpenBook() Dim Book1 As Workbook 'target book Dim i As Long ' loop variable Dim wbName As Variant 'file to be opened Dim shtlastcell As Long 'cell on target sheet at which rows will be inserted Dim selRows As Long 'number of elected rows on source sheet Dim srcSheetName As String ' name of last Dim srcSheetRng As Range 'selected range on source sheet Dim srcBook As Workbook ' source workbook Set srcBook = ActiveWorkbook Set srcSheetRng = ActiveWindow.RangeSelection srcSheetName = ActiveSheet.Name selRows = Selection.Rows.Count wbName = Application.GetOpenFilename _ (FileFilter:="microsoft excel files (*.xls), *.xls", _ Title:="Get File", MultiSelect:=False) If wbName < False Then Set Book1 = Workbooks.Open(wbName) Else Exit Sub End If Book1.Sheets(1).Activate shtlastcell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To selRows Rows(shtlastcell + i).Insert (xlShiftDown) Next srcBook.srcSheetName.Range(srcSheetRng).Copy Destination:=Book1.Sheets(1).Range("A" & shtlastcell + 1) End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy problem between worksheets
This also works if I Dim srcBook & Book1 as Object rather than workbook
srcBook.Sheets(srcSheetName).Range(srcSheetRng.Add ress).Copy ActiveSheet.Paste Destination:=Book1.Sheets(1).Range("A" & shtlastcell + 1) I may understand this one day ... "Project Mangler" wrote in message ... JLGWhiz, Thanks for the reply. I couldn't get the code to work but you put me on the right track: Workbooks(srcBook.Name).Sheets(srcSheetName).Range (srcSheetRng.Address).Copy ActiveSheet.Paste Destination:=Workbooks(Book1.Name).Sheets(1).Range ("A" & shtlastcell + 1) Thanks for the help! "JLGWhiz" wrote in message ... Sorry, forgot the Set part. To this: Set srcSheetName = ActiveSheet "Project Mangler" wrote in message ... My understanding of fully qualified ranges is a little flimsy. Perhaps this is why the copy operation in the last line of this code fails with an "Object doesn't support this property or method". I would be grateful if someone could point out where I'm going wrong? Is it the variable typing, the qualification of the ranges or am I just using the wrong syntax? Thanks! Sub procOpenBook() Dim Book1 As Workbook 'target book Dim i As Long ' loop variable Dim wbName As Variant 'file to be opened Dim shtlastcell As Long 'cell on target sheet at which rows will be inserted Dim selRows As Long 'number of elected rows on source sheet Dim srcSheetName As String ' name of last Dim srcSheetRng As Range 'selected range on source sheet Dim srcBook As Workbook ' source workbook Set srcBook = ActiveWorkbook Set srcSheetRng = ActiveWindow.RangeSelection srcSheetName = ActiveSheet.Name selRows = Selection.Rows.Count wbName = Application.GetOpenFilename _ (FileFilter:="microsoft excel files (*.xls), *.xls", _ Title:="Get File", MultiSelect:=False) If wbName < False Then Set Book1 = Workbooks.Open(wbName) Else Exit Sub End If Book1.Sheets(1).Activate shtlastcell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To selRows Rows(shtlastcell + i).Insert (xlShiftDown) Next srcBook.srcSheetName.Range(srcSheetRng).Copy Destination:=Book1.Sheets(1).Range("A" & shtlastcell + 1) End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy problem between worksheets
I apologize for not mentioning that if you use the:
Set srcSheetName = ActiveSheet That you would also have to Dim it as a Worksheet. Probably the easiest way would have been to just use the Sheets(srcSheetName) syntax. The point is that the syntax originally posted was attempting to use the variable as a worksheet vs a worksheet name. 1. If it is used as a name then it has to be qualified with an object Sheets or Worksheets. 2. If it is Dim as a worksheet and Set as an object variable to either the ActiveSheet or a specific sheet name or sheets index, then only the variable need be used because it will equate to the appropriate sheet name as an object. I sometimes get in a hurry to respond to these postings and overlook the fact that if you had known what to do, there would not have been a need for posting in the first place. Again, my apologies. "Project Mangler" wrote in message ... This also works if I Dim srcBook & Book1 as Object rather than workbook srcBook.Sheets(srcSheetName).Range(srcSheetRng.Add ress).Copy ActiveSheet.Paste Destination:=Book1.Sheets(1).Range("A" & shtlastcell + 1) I may understand this one day ... "Project Mangler" wrote in message ... JLGWhiz, Thanks for the reply. I couldn't get the code to work but you put me on the right track: Workbooks(srcBook.Name).Sheets(srcSheetName).Range (srcSheetRng.Address).Copy ActiveSheet.Paste Destination:=Workbooks(Book1.Name).Sheets(1).Range ("A" & shtlastcell + 1) Thanks for the help! "JLGWhiz" wrote in message ... Sorry, forgot the Set part. To this: Set srcSheetName = ActiveSheet "Project Mangler" wrote in message ... My understanding of fully qualified ranges is a little flimsy. Perhaps this is why the copy operation in the last line of this code fails with an "Object doesn't support this property or method". I would be grateful if someone could point out where I'm going wrong? Is it the variable typing, the qualification of the ranges or am I just using the wrong syntax? Thanks! Sub procOpenBook() Dim Book1 As Workbook 'target book Dim i As Long ' loop variable Dim wbName As Variant 'file to be opened Dim shtlastcell As Long 'cell on target sheet at which rows will be inserted Dim selRows As Long 'number of elected rows on source sheet Dim srcSheetName As String ' name of last Dim srcSheetRng As Range 'selected range on source sheet Dim srcBook As Workbook ' source workbook Set srcBook = ActiveWorkbook Set srcSheetRng = ActiveWindow.RangeSelection srcSheetName = ActiveSheet.Name selRows = Selection.Rows.Count wbName = Application.GetOpenFilename _ (FileFilter:="microsoft excel files (*.xls), *.xls", _ Title:="Get File", MultiSelect:=False) If wbName < False Then Set Book1 = Workbooks.Open(wbName) Else Exit Sub End If Book1.Sheets(1).Activate shtlastcell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To selRows Rows(shtlastcell + i).Insert (xlShiftDown) Next srcBook.srcSheetName.Range(srcSheetRng).Copy Destination:=Book1.Sheets(1).Range("A" & shtlastcell + 1) End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select specific worksheets & copy - code problem | Excel Programming | |||
how do i copy a cell in worksheets 10 to the other 9 worksheets | New Users to Excel | |||
A visual basic value copy BUG?? - accounting format has copy problem!! | Excel Programming | |||
copy between worksheets does not copy formulae just values | Excel Discussion (Misc queries) | |||
Copy between worksheets... | Excel Worksheet Functions |