PDA

View Full Version : Solved: Extracted Sheets Limits to 43 only why?



jammer6_9
03-19-2008, 07:04 AM
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

xld
03-19-2008, 07:25 AM
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.

xld
03-19-2008, 07:50 AM
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



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


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

jammer6_9
03-20-2008, 12:35 AM
Solved: I have extracted all sheets required xld :bow:

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


ws.Move after:=ss


My Code

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

JonPeltier
03-20-2008, 03:52 AM
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
03-25-2008, 01:39 AM
Progress Bar has done as well :whistle:




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

xld
03-25-2008, 01:51 AM
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
03-25-2008, 03:38 AM
Defenitely Not xld infact I appreciate your :dau: comments . As I thought I am already done :banghead: 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 :help is badly :ipray: 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
03-25-2008, 03:57 AM
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.

xld
03-25-2008, 04:10 AM
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.

xld
03-25-2008, 04:53 AM
This is the sort of thing I am alluding to

jammer6_9
03-25-2008, 05:13 AM
You're one of a kind xld :bow: ...


This is the sort of thing I am alluding to

xld
03-25-2008, 05:45 AM
Thank goodness says the rest of the world.