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" <(E-Mail Removed)> wrote in message
news:O$(E-Mail Removed)...
> This also works if I Dim srcBook & Book1 as Object rather than workbook
>
> srcBook.Sheets(srcSheetName).Range(srcSheetRng.Address).Copy
> ActiveSheet.Paste Destination:=Book1.Sheets(1).Range("A" & shtlastcell +
> 1)
>
> I may understand this one day ...
>
>
> "Project Mangler" <(E-Mail Removed)> wrote in message
> news:u%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Sorry, forgot the Set part.
>> >
>> > To this:
>> >
>> > Set srcSheetName = ActiveSheet
>> >
>> >
>> >
>> >
>> > "Project Mangler" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> > > 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
>> > >
>> > >
>> > >
>> >
>> >
>>
>>
>
>
|
Similar Threads
