Solved: Invoking the excel saveas screen

[pico]

I have attached the PARTS1 sheet. The other sheet was included earlier

[pico]

Ok I came up with some changes to the code. It prints the values that i want in the Parts sheet without formulas. But there are a few changes i need. 1. I'd like the row height of the original copied over to the second sheet. At the moment I tried using the paste special method. It does not seem to work. The column width works perfectly. 2. When i open the saved file i still get the enable macro screen on startup. I thought iam deleting all the code. Just wondering why that's happening. . Here is the code.

[VBA]Dim VBCodeMod As Object, VBComp As Object
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
Set wb = ActiveWorkbook
'Sheet1.Visible = xlSheetVisible
Set ws1 = wb.Sheets("JOB_SPEC_FORM")
Set ws2 = wb.Sheets("PARTS1")
Set pb = Workbooks.Add
Application.ScreenUpdating = False
Set ps1 = pb.Sheets(1)
Set ps2 = pb.Sheets(2)
ws1.Copy pb.Sheets(1)
With pb.VBProject.VBComponents(ps1.CodeName).CodeModule
StartLine = 1
HowManyLines = .CountOfLines
.DeleteLines StartLine, HowManyLines
End With
ws2.Range("A1:E10").Cells.Copy ps2.Range("A1:E10")
ws2.Range("A11:E75").Cells.Copy
ps2.Range("A11:E75").Cells.PasteSpecial (xlPasteFormats)
ps2.Range("A11:E75").Cells.PasteSpecial (xlPasteValues)
ps2.Range("A11:E75").Cells.PasteSpecial (8) 'copy column width
ps2.Range("A11:E75").Cells.PasteSpecial (xlrowheight) 'copy row width
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
'Sheet1.Visible = xlSheetHidden[/VBA]

[CBrine]

I changed the copy and paste method to the entire workbook, added the copy paste values, and setup code to remove the code from the PS2 modules.

[VBA]

Dim VBCodeMod As Object, VBComp As Object
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
Set wb = ActiveWorkbook
'Sheet1.Visible = xlSheetVisible
Set ws1 = wb.Sheets("JOB_SPEC_FORM")
Set ws2 = wb.Sheets("PARTS1")
Set pb = Workbooks.Add
Application.ScreenUpdating = False
Set ps1 = pb.Sheets(1)
Set ps2 = pb.Sheets(2)
ws1.Copy pb.Sheets(1)
With pb.VBProject.VBComponents(ps1.CodeName).CodeModule
StartLine = 1
HowManyLines = .CountOfLines
.DeleteLines StartLine, HowManyLines
End With
ws2.Copy pb.Sheets(1)
ws2.Range("A11:E75").Cells.Copy
ps2.Range("A11:E75").Cells.PasteSpecial (xlPasteValues)
With pb.VBProject.VBComponents(ps2.CodeName).CodeModule
StartLine = 1
HowManyLines = .CountOfLines
.DeleteLines StartLine, HowManyLines
End With
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
'Sheet1.Visible = xlSheetHidden

[/VBA]

Let me know if this works.

Cal

[pico]

I tried your code. But there seem to be a couple problems there. I have updated it to look like the following . Iam going to attach the saved file. This is what is output after i hit the save button. There are still a couple of problems with this that i cannot seem to fix. It tries to update itself from the original file when i try to open it.2. The copy works but there is an empty sheet in between the Parts1 and teh JobSpecForm sheet. My code looks like this:
[VBA]Dim VBCodeMod As Object, VBComp As Object
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
Set wb = ActiveWorkbook
Set ws1 = wb.Sheets("JOB_SPEC_FORM")
Set ws2 = wb.Sheets("PARTS1")
Set pb = Workbooks.Add
Application.ScreenUpdating = False
Set ps1 = pb.Sheets(1)
Set ps2 = pb.Sheets(2)
Sheet1.Visible = xlSheetVisible
ws1.Copy pb.Sheets(1)
Sheet1.Visible = xlSheetHidden
'ws2.Range("A1:E10").Cells.Copy ps2.Range("A1:E10")
ws2.Range("A1:E75").Cells.Copy
ps2.Range("A1:E75").Cells.PasteSpecial (xlPasteFormats)
ps2.Range("A1:E75").Cells.PasteSpecial (xlPasteValues)
ps2.Range("A1:E75").Cells.PasteSpecial (8) 'copy column width
ps2.Rows(2).RowHeight = ws2.Rows(2).RowHeight 'copy 2nd row height
ps2.Rows(4).RowHeight = ws2.Rows(4).RowHeight 'copy 4th row height
ps2.Rows(6).RowHeight = ws2.Rows(6).RowHeight 'copy 6th row height
ps2.Rows(8).RowHeight = ws2.Rows(8).RowHeight 'copy 8th row height
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]

This is ****ing me off. Iam getting frustrated with VBA

[pico]

f

[pico]

saved file