In multiple sheet copy error subscript out of range ?? HELP

Data1 are the sheets to be copied from different files.
Combine sheet is the master sheet, where the data in data1 sheets are to be
copied.
I get an error "subscript out of range" ?? correct the following code please.

For N = LBound(FName) To UBound(FName)
Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets("DATA1").Range("A4:S200")
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets("COMBINE SHEET").Cells(rnum,
"A")

basebook.Worksheets("COMBINE SHEET").Cells(rnum, "G").Value =
mybook.Name
' This will add the workbook name in column D if you want

sourceRange.Copy destrange
' Instead of this line you can use the code below to copy only
the Values

' With sourceRange
' Set destrange =
basebook.Worksheets(1).Cells(rnum,"A"). _
' Resize(.Rows.Count, .Columns.Count)
' End With
' destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
Next

subscript out of range usually means you are refering to a member of a
collection that does not exits.

In this case, the likely candidates are

Data1
Combine Sheet

These don't exist in the workbook in which you try to reference them.

Could be a spelling error or they may actually not exist.

If you hit the debug button when the error occurs, it should highlight the
line of code where the problem is encountered. This should help you
recognize what the problem is.

--
Regards,
Tom Ogilvy


"Eddy Stan" wrote:

> Data1 are the sheets to be copied from different files.
> Combine sheet is the master sheet, where the data in data1 sheets are to be
> copied.
> I get an error "subscript out of range" ?? correct the following code please.
>
> For N = LBound(FName) To UBound(FName)
> Set mybook = Workbooks.Open(FName(N))
> Set sourceRange = mybook.Worksheets("DATA1").Range("A4:S200")
> SourceRcount = sourceRange.Rows.Count
> Set destrange = basebook.Worksheets("COMBINE SHEET").Cells(rnum,
> "A")
>
> basebook.Worksheets("COMBINE SHEET").Cells(rnum, "G").Value =
> mybook.Name
> ' This will add the workbook name in column D if you want
>
> sourceRange.Copy destrange
> ' Instead of this line you can use the code below to copy only
> the Values
>
> ' With sourceRange
> ' Set destrange =
> basebook.Worksheets(1).Cells(rnum,"A"). _
> ' Resize(.Rows.Count, .Columns.Count)
> ' End With
> ' destrange.Value = sourceRange.Value
>
> mybook.Close False
> rnum = rnum + SourceRcount
> Next

Data1 is the sheet name in three sample files (closed & they are in a folder
refered in mypath )
Combine is the sheet name in my consol file, from where I am running the
macro.
It opens the folder; I select all the files then this error occurs.
end or debug box is not coming up, just the subscript out of range pops up.

I am no well versed with VB/excel, can you correct my code below or tell me
what each lines mean ( as I have copied the code from discussion board only).

"Tom Ogilvy" wrote:

> subscript out of range usually means you are refering to a member of a
> collection that does not exits.
>
> In this case, the likely candidates are
>
> Data1
> Combine Sheet
>
> These don't exist in the workbook in which you try to reference them.
>
> Could be a spelling error or they may actually not exist.
>
> If you hit the debug button when the error occurs, it should highlight the
> line of code where the problem is encountered. This should help you
> recognize what the problem is.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Eddy Stan" wrote:
>
> > Data1 are the sheets to be copied from different files.
> > Combine sheet is the master sheet, where the data in data1 sheets are to be
> > copied.
> > I get an error "subscript out of range" ?? correct the following code please.
> >
> > For N = LBound(FName) To UBound(FName)
> > Set mybook = Workbooks.Open(FName(N))
> > Set sourceRange = mybook.Worksheets("DATA1").Range("A4:S200")
> > SourceRcount = sourceRange.Rows.Count
> > Set destrange = basebook.Worksheets("COMBINE SHEET").Cells(rnum,
> > "A")
> >
> > basebook.Worksheets("COMBINE SHEET").Cells(rnum, "G").Value =
> > mybook.Name
> > ' This will add the workbook name in column D if you want
> >
> > sourceRange.Copy destrange
> > ' Instead of this line you can use the code below to copy only
> > the Values
> >
> > ' With sourceRange
> > ' Set destrange =
> > basebook.Worksheets(1).Cells(rnum,"A"). _
> > ' Resize(.Rows.Count, .Columns.Count)
> > ' End With
> > ' destrange.Value = sourceRange.Value
> >
> > mybook.Close False
> > rnum = rnum + SourceRcount
> > Next

What line is highlighted when the error occurs and you go into debug mode?

--
Regards,
Tom Ogilvy


"Eddy Stan" wrote:

> Data1 is the sheet name in three sample files (closed & they are in a folder
> refered in mypath )
> Combine is the sheet name in my consol file, from where I am running the
> macro.
> It opens the folder; I select all the files then this error occurs.
> end or debug box is not coming up, just the subscript out of range pops up.
>
> I am no well versed with VB/excel, can you correct my code below or tell me
> what each lines mean ( as I have copied the code from discussion board only).
>
> "Tom Ogilvy" wrote:
>
> > subscript out of range usually means you are refering to a member of a
> > collection that does not exits.
> >
> > In this case, the likely candidates are
> >
> > Data1
> > Combine Sheet
> >
> > These don't exist in the workbook in which you try to reference them.
> >
> > Could be a spelling error or they may actually not exist.
> >
> > If you hit the debug button when the error occurs, it should highlight the
> > line of code where the problem is encountered. This should help you
> > recognize what the problem is.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Eddy Stan" wrote:
> >
> > > Data1 are the sheets to be copied from different files.
> > > Combine sheet is the master sheet, where the data in data1 sheets are to be
> > > copied.
> > > I get an error "subscript out of range" ?? correct the following code please.
> > >
> > > For N = LBound(FName) To UBound(FName)
> > > Set mybook = Workbooks.Open(FName(N))
> > > Set sourceRange = mybook.Worksheets("DATA1").Range("A4:S200")
> > > SourceRcount = sourceRange.Rows.Count
> > > Set destrange = basebook.Worksheets("COMBINE SHEET").Cells(rnum,
> > > "A")
> > >
> > > basebook.Worksheets("COMBINE SHEET").Cells(rnum, "G").Value =
> > > mybook.Name
> > > ' This will add the workbook name in column D if you want
> > >
> > > sourceRange.Copy destrange
> > > ' Instead of this line you can use the code below to copy only
> > > the Values
> > >
> > > ' With sourceRange
> > > ' Set destrange =
> > > basebook.Worksheets(1).Cells(rnum,"A"). _
> > > ' Resize(.Rows.Count, .Columns.Count)
> > > ' End With
> > > ' destrange.Value = sourceRange.Value
> > >
> > > mybook.Close False
> > > rnum = rnum + SourceRcount
> > > Next

Thanks for your support. I changed sheet names several times the following
worked.. and I don't know why.. if you explain each row below it will be
useful for my next one.
Set sourceRange = mybook.Worksheets("DATA1").Range("A4:S200")
SourceRcount = sourceRange.Rows.Count
Lrnum = LastRow(basebook.Worksheets("COMBINE")) + 1
Set destrange = basebook.Worksheets("COMBINE").Cells(Lrnum, "A")
basebook.Worksheets("COMBINE").Cells(rnum, "G").Value = mybook.Name





"Tom Ogilvy" wrote:

> What line is highlighted when the error occurs and you go into debug mode?
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Eddy Stan" wrote:
>
> > Data1 is the sheet name in three sample files (closed & they are in a folder
> > refered in mypath )
> > Combine is the sheet name in my consol file, from where I am running the
> > macro.
> > It opens the folder; I select all the files then this error occurs.
> > end or debug box is not coming up, just the subscript out of range pops up.
> >
> > I am no well versed with VB/excel, can you correct my code below or tell me
> > what each lines mean ( as I have copied the code from discussion board only).
> >
> > "Tom Ogilvy" wrote:
> >
> > > subscript out of range usually means you are refering to a member of a
> > > collection that does not exits.
> > >
> > > In this case, the likely candidates are
> > >
> > > Data1
> > > Combine Sheet
> > >
> > > These don't exist in the workbook in which you try to reference them.
> > >
> > > Could be a spelling error or they may actually not exist.
> > >
> > > If you hit the debug button when the error occurs, it should highlight the
> > > line of code where the problem is encountered. This should help you
> > > recognize what the problem is.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Eddy Stan" wrote:
> > >
> > > > Data1 are the sheets to be copied from different files.
> > > > Combine sheet is the master sheet, where the data in data1 sheets are to be
> > > > copied.
> > > > I get an error "subscript out of range" ?? correct the following code please.
> > > >
> > > > For N = LBound(FName) To UBound(FName)
> > > > Set mybook = Workbooks.Open(FName(N))
> > > > Set sourceRange = mybook.Worksheets("DATA1").Range("A4:S200")
> > > > SourceRcount = sourceRange.Rows.Count
> > > > Set destrange = basebook.Worksheets("COMBINE SHEET").Cells(rnum,
> > > > "A")
> > > >
> > > > basebook.Worksheets("COMBINE SHEET").Cells(rnum, "G").Value =
> > > > mybook.Name
> > > > ' This will add the workbook name in column D if you want
> > > >
> > > > sourceRange.Copy destrange
> > > > ' Instead of this line you can use the code below to copy only
> > > > the Values
> > > >
> > > > ' With sourceRange
> > > > ' Set destrange =
> > > > basebook.Worksheets(1).Cells(rnum,"A"). _
> > > > ' Resize(.Rows.Count, .Columns.Count)
> > > > ' End With
> > > > ' destrange.Value = sourceRange.Value
> > > >
> > > > mybook.Close False
> > > > rnum = rnum + SourceRcount
> > > > Next