Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
In multiple sheet copy error subscript out of range ?? HELP
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
In multiple sheet copy error subscript out of range ?? HELP
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
In multiple sheet copy error subscript out of range ?? HELP
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
In multiple sheet copy error subscript out of range ?? HELP
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error:Subscript out of range | Excel Discussion (Misc queries) | |||
Subscript out of range (Error 9) | Excel Programming | |||
Type Mismatch error & subscript out of range error | Excel Programming | |||
Subscript Out Of Range Error? | Excel Programming | |||
Subscript out of range error | Excel Programming |