saved file
saved file
Pico,
OK, so your first issue is where the sheets are pasted. The reason they are getting pasted out of order is that you are copying the entire workbook for the job_spec_sheet, and you have assigned the worksheets prior to that?? If you want my help you need to use the code that I've modified. If you look at the last post I gave you, it's coded the correct way. The way yours is coded you end up trying to remove code from a sheet without code in, and just ignore the sheet with the code.
Second issue, is the update link that keeps appearing. I've reviewed the workbook you attached, and the reason you are getting this, is because you have checkboxes with linked cells still attached to some sheet called
release.xls. Do these link cells need to be there? If not, remove them from the master copy of your workbook. To be sure they are all removed, just goto the excel menu
edit.....links
if they are all gone the links option should be grayed out.
Third,
Is there a reason why you can't upload the workbook that you are producing the sheet with? I'm trying to determine what's going on by looking at your output. This would be much easier to troubleshoot with the actual workbook that is used to produce the output sheet. That way, I could make the necessary corrections, and post it back to the thread.
Cal
Cal,
I cant upload my original workbook because the filesize is around 2MB. This forum supports around 300kb?. I do need the links in the original workbook. I use them to calculate the partnumbers in another sheet. Is there a way of copying the checkboxes without the links attached?. Please rememeber that i do need them in my original. The code that you supplied at the moment did not work like i wanted it. For example it created 2 parts sheets for me in the saved file.I will try to look at the code that you have supplied me and try to work around it. Btw, i thank you for all the help you have given me so far.
pico, can you zip up the file and reduce its size enough to be able to upload it?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Hang in the Cal....your doing a heck of a job....seriously
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
I have emailed the file to Cal.
I was having problems sending it through gmail. Sent it through hotmail instead.
This is an automatically generated Delivery Status Notification.Delivery to the following recipients failed. Ithere something wrong with your email Address>??? HOtmail did this as well
sorrry didnt mean to post your email here. DOH
No worries,
Not like I'm going get any extra junk mail in my hotmail account.
OK,
I've got your workbook, and I've figured out where the code is.
I've resolved the copy issue on ps2, and removed the formula's from the final sheet. My copy is getting some NA errors, but I'm assuming that's because I'm bypass the normal population method to run it. I've also removed some blank events from the job_spec_sheet that were causing the enable macro's message to appear, which eliminates the need to erase the methods programmatically.
NEXT PROBLEM TO RESOLVE
That the last problem is the request to update links. These are appearing because you have checkboxes linked to your other workbooks(In the release.xls workbook) using the linkedcell property for the checkboxes.
Are those links needed? If they are needed, then I will need to do some research to figure out how to copy the checkboxes without the linked cell property, and keep the current state of the checkbox.
Let me know.
Cal
Yes, I did mention in my previous post that the links were needed in the original sheet. There are being used to calculate the partnumbers in the second sheet of the orginal workboook
pico,
I think we are finally there. I was able to remove the linkedCell property without changing the state of the checkbox. I was then able to open the final produced workbook without the enable macro's and without the update links.
I've sent the zip file to your hotmail account. Let me know if this takes care of the problems.
Cal
PS-A very well put together wizard by the way.
Thank you Cal. Exactly what i needed. Finally i can say iam done with this project. I appreciate your help very much. Btw iam glad you like the design. I was going for a professional look. In the end i think it came out pretty good. Not too bad for a first timer with VBA. Thanks again. Cheers . HAHA..i can finally close this message.
Pico,
Glad everythings working now. You were looking for some out of the ordinary stuff, which is why it took so long to come up with a solution.
Thought I would post the finalized code, just in case anyone else has to deal with something similiar.
[vba]
Dim wb As Workbook, ws1 As Worksheet, ws2 As Worksheet
Dim pb As Workbook, ps1 As Worksheet, ps2 As Worksheet
Dim StartLine As Long
Dim HowManyLines As Long
Dim response As String
Dim s As OLEObject
'Set references to the Master workbook
Set wb = ActiveWorkbook
Set ws1 = wb.Sheets("JOB_SPEC_FORM")
Set ws2 = wb.Sheets("PARTS1")
'Add new workbook
Set pb = Workbooks.Add
Application.ScreenUpdating = False
'Unhide job_spec_form
Sheet1.Visible = xlSheetVisible
'Copy data from first sheet, set reference to it and hide original workbook again.
ws1.Copy pb.Sheets(1)
Set ps1 = ActiveSheet
Sheet1.Visible = xlSheetHidden
'cycle through shapes and remove link
For Each s In ps1.OLEObjects
If Left(s.Name, 6) = "CheckB" Then
s.LinkedCell = ""
End If
Next s
'Copy data from second sheet, set references to it and copy and paste values
ws2.Visible = xlSheetVisible
ws2.Copy , pb.Sheets(1)
Set ps2 = ActiveSheet
ws2.Cells.Copy
ps2.Range("A1").PasteSpecial (xlPasteValues)
ws2.Visible = xlSheetVeryHidden
ActiveWindow.DisplayGridlines = False
Application.ScreenUpdating = True
'response = "False"
' Do Until response <> "False"
' response = Application.Dialogs(xlDialogSaveAs).Show
' Loop
Application.Dialogs(xlDialogSaveAs).Show
pb.Close False
[/vba]