Solved: Extracted Sheets Limits to 43 only why?

[jammer6_9]

[VBA]
Sub CopyData()
Dim w As Workbook, ws As Worksheet, ss As Worksheet
Dim Arr(), a, tp As Worksheet, sh As Range
Dim i As Long
Application.ScreenUpdating = False

MsgBox ("This might take time... Do not disturb the process!"), vbInformation, "ofsjcr"

ReDim Arr(0)
i = -1
With Sheets("SALARY LIST")
For Each sh In Range(.Cells(3, 1), .Cells(3, 1).End(xlDown))
i = i + 1
ReDim Preserve Arr(i)
Arr(i) = sh
Next
End With

Set tp = Sheets("MASTER TEMPLATE")
For Each a In Arr
tp.Range("C12") = a

tp.Copy after:=Sheets(Sheets.Count)

Sheets(Sheets.Count).Name = a

Next
Application.ScreenUpdating = True

End Sub
[/VBA]

[Bob Phillips]

Because you are not fully qualifying your ranges, there are 43 rows in Master Template, and I bet that is the active sheet wen you run the macro.

[Bob Phillips]

Scotch that it was total tosh.

The error is being masked by the On Error Resume Next. It throws a 1004 error. Oddly enough, if you remove the sheets and restrat it errors immediately.

This seems to work though

[vba]

Sub CopyData()
'On Error Resume Next
Dim w As Workbook, ws As Worksheet, ss As Worksheet
Dim Arr(), a, tp As Worksheet, sh As Range
Dim i As Long
Application.ScreenUpdating = False

MsgBox ("This might take time... Do not disturb the process!"), vbInformation, "ofsjcr"

ReDim Arr(0)
i = -1
With Sheets("SALARY LIST")
For Each sh In Range(.Cells(3, 1), .Cells(3, 1).End(xlDown))
i = i + 1
ReDim Preserve Arr(i)
Arr(i) = sh
Next
End With

Set tp = Sheets("MASTER TEMPLATE")
For Each a In Arr
Worksheets.Add after:=Sheets(Sheets.Count)
tp.Cells.Copy ActiveSheet.Cells
ActiveSheet.Range("C12") = a
ActiveSheet.Name = a
Next

Application.ScreenUpdating = True

End Sub
[/vba]

I would put up a progress bar as well to show what is happening.

[jammer6_9]

Solved: I have extracted all sheets required xld

NOW before going to the Progress Bar, I am trying to do is I want to extract extracted sheets to a new workbook with the My code below and I have error pointing in this line

[vba]
ws.Move after:=ss
[/vba]

My Code
[vba]
Sub Button3_Click()
Dim w As Workbook, ws As Worksheet, ss As Worksheet

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "common message" And ws.Name <> "SALARY LIST" And ws.Name <> "MASTER TEMPLATE" Then

If w Is Nothing Then
ws.Move
Set w = ActiveWorkbook

Else
ws.Move after:=ss

End If
Set ss = ActiveSheet

End If

Next ws

End Sub

[/vba]

[JonPeltier]

This is a known issue, though offhand I don't know the reference. You can work around it by saving, closing, and reopening the target document at a frequency shorter than the typical number of sheets that can be transferred before failure. I think I've also addressed it by copying each sheet to a new workbook, then moving it into the target workbook; alternatively by copying to a new workbook, saving this temporarily as a template, then adding a new sheet to the target workbook based on this template.

[jammer6_9]

Progress Bar has done as well

I would put up a progress bar as well to show what is happening.

[Bob Phillips]

Well that is certainly better, never a good idea to leave a user waiting wondering what is going on.

But ... and you are going to hate me for this. I always feel the PB should feed back info about the item being processed, and there should be a cancel button so that if the users sees that there are too many, they can quit and so it later.

Also, I reran it. First it fails if you rerun because the sheets are already there. Secondly, I got a message saying it was processing item No x of 44, when in fact there were 200. I had the Master sheet active then, and although it worked fine, it took its lastrow count off of the active sheet, not the Salary List.

[jammer6_9]

Defenitely Not xld infact I appreciate your comments . As I thought I am already done I did not even tried to reran it as you did wherein it really fails. Well I have to go back with it and I hope It will not take me ages to finished it. Should I say an expert is badly needed...

But ... and you are going to hate me for this. I always feel the PB should feed back info about the item being processed, and there should be a cancel button so that if the users sees that there are too many, they can quit and so it later.

Also, I reran it. First it fails if you rerun because the sheets are already there. Secondly, I got a message saying it was processing item No x of 44, when in fact there were 200. I had the Master sheet active then, and although it worked fine, it took its lastrow count off of the active sheet, not the Salary List.

[JonPeltier]

Sometimes I give fake information for the progress. I might know how many total items to process and which one I'm on, but sometimes in my wildest guess I can't predetermine how long each will take. As long as there's some motion towards 100%, the user tends to be patient.

Canceling is very tricky. You have to know just where you were at the start so you can revert as closely as possible.

[Bob Phillips]

In this case Jon it is very simple, the count is easily pre-determined, and a number of worksheets are created from a master template, so the target count is available, and cancel just needs to delete the newly created sheets.

When I have a situation where I have no idea of the counts, I will often recycle the bar, so do a 1-100 a number of times. It is then likely that the last time might rush to the finish, but that is better IMO than having a bar that moves slightly on for 99% of the time, then rushes to the end. If there is some info updating, such as the item being processed, then the user is getting dsome feedback that something is happening.

[Bob Phillips]

This is the sort of thing I am alluding to

[jammer6_9]

You're one of a kind xld ...

This is the sort of thing I am alluding to

[Bob Phillips]

Thank goodness says the rest of the world.