Copy problem between worksheets

[Project Mangler]

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

 

[JLGWhiz]

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.

 

[JLGWhiz]

Sorry, forgot the Set part.

To this:

Set srcSheetName = ActiveSheet

 

[Project Mangler]

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!

 

[Project Mangler]

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

 

[JLGWhiz]

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.