PC Review


Reply
Thread Tools Rate Thread

Creating identical print layout from copied worksheet

 
 
Chris Maddogz
Guest
Posts: n/a
 
      15th Jun 2009
Currently running the following macro to save multiple worksheets from
workbook "Jobs" to a new workbook with the name in cell E1 of original Jobs
worksheet "Concrete".
Because I am copying cells A:E into the first new worksheet within the new
workbook I then need to make sure that the print layout is identical - ie the
1st worksheet in the new workbook needs to be set to fit to one page (as per
"Concrete" worksheet is setup - just not sure where to insert code

Thanks

Sub savejobC()
' Macro recorded 23/05/2009 by Chris
'

Dim strName As String
Dim strPath As String
Dim strFolder As String
Dim wbTemp As Workbook
Dim varSheets As Variant

varSheets = Array("SubCon C", "Inv C", "Sub C", "Safety C", "Work Method C")
Sheets("CONCRETE").Select
Columns("A:E").Copy
Set wbTemp = Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Workbooks("Jobs Workbook.xls").Sheets(varSheets).Copy _
After:=wbTemp.Worksheets(1)
wbTemp.Activate
Sheets("Sheet1").Select
strName = Left(Trim(Range("E1")), 3)
strPath = "c:\Jobs\"
If Dir(strPath & strName, vbDirectory) = "" Then
MkDir "c:\jobs\" & strName
End If
ActiveSheet.Name = Trim(Range("E1"))
For Each sh In ActiveWorkbook.Sheets
sh.Activate
Cells.Copy
Cells.PasteSpecial Paste:=xlPasteValues
Next sh
Range("A1").Select
Sheets("Safety C").Select
Range("A1").Select
Sheets("Sub C").Select
Range("D9").Select
Sheets("Inv C").Select
Range("E13").Select
Sheets("SubCon C").Select
Range("B6").Select
Sheets(1).Select
Range("E1").Select
ActiveWorkbook.SaveAs "c:\jobs\" & strName & "\" & Trim(Range("E1")) & ".xls"
ActiveWorkbook.Close True
End Sub

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Setting page layout and print set up in code for a new worksheet NDBC Microsoft Excel Programming 2 22nd Jul 2009 07:17 AM
Creating print sections within a worksheet Miner Jeff Microsoft Excel Discussion 2 11th Dec 2007 03:53 PM
Files copied even when identical! =?Utf-8?B?R2lhbmdp?= Windows XP Photos 0 13th Sep 2006 01:47 PM
2 identical versions of word, differnt layout lenny Microsoft Word Document Management 5 16th Nov 2004 09:31 PM
Re: Print multiple worksheet in one layout Ron de Bruin Microsoft Excel Misc 2 21st Aug 2004 12:44 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 08:54 PM.