Solved

macro that print a range and save the range in a file

Posted on 2003-12-03
11
437 Views
Last Modified: 2006-11-17
Hi there
It is the first time that I use EE.I am trying to write a macro for excel that wil print a range ,say sheet 1  ( A1:G40)  and save (A1:G40) in a new workbook . I use Sheet 1  to create a school payment register .
with lookup tabels and formulas , the data is in cell (ABB1:ABB200). I am using a  checkbox
in range(A1:G40 ) to get the child's name ,did he /she pay or not.

Thanks!!
topend
0
Comment
Question by:topend
  • 5
  • 3
  • 2
11 Comments
 
LVL 8

Accepted Solution

by:
SNilsson earned 500 total points
Comment Utility
Put this in a module and you are good to go:

Sub Go()

Dim oRange As Range
Dim oPrintRange As Range
Dim oSheet As Worksheet
Dim oWB As Workbook

Set oSheet = Worksheets("Sheet1")
Set oRange = oSheet.Range("A1:G1")
Set oPrintRange = oSheet.Range(oRange.Address, oRange.SpecialCells(xlLastCell).Address)
   
oSheet.PageSetup.PrintArea = oPrintRange.Address
oSheet.PrintOut Copies:=1, Collate:=True

oPrintRange.Copy

Set oWB = Workbooks.Add

oWB.ActiveSheet.Paste
oWB.Save
oWB.Close

End Sub

Please write back if you have any questions.
0
 
LVL 50

Expert Comment

by:Dave Brett
Comment Utility
Pleasee dont award any points to me for this post

Nice code with a couple of suggestions

-The macro wont compile with the name Go
-Why not set the range directly to the requested A1:G40, ie
Set oRange = oSheet.Range("A1:G40")

Sub GoPrint()
Dim oPrintRange As Range
Dim oWB As Workbook

Set oPrintRange = Sheets(1).Range("A1:G40")
oPrintRange.PrintOut
oPrintRange.Copy
Set oWB = Workbooks.Add
oWB.ActiveSheet.Paste
oWB.Save
oWB.Close
End Sub

Cheers

Dave
0
 
LVL 8

Expert Comment

by:SNilsson
Comment Utility
Thanks Dave,

I just put the Go there a last change (from macro1) before I pasted it from the VBA env ;)

And about the range, if for some reason the range should get bigger on sheet1 then it would be handled automatic, but you are right a static range may be best in this case, if there are rows below row 40 they will also be printed.

Also the oWb.save will not alow you to specify a name and location for your new workbook.
Topend, if you want to save/name the new workbook in some way, please tell.
0
 

Author Comment

by:topend
Comment Utility
Sorry!!
It tooke me a while to get the comments on my questions .
Thanx to you gays . Is it possible to specify  the path name and file
0
 
LVL 50

Expert Comment

by:Dave Brett
Comment Utility
>> Thanx to you gays

:)

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 8

Expert Comment

by:SNilsson
Comment Utility
If you want you can save the new workbook as the students name.

If you put the name of the student in for example H1.
Then if you put 'Bruce Willson' there before you do the print out you will have the new workbook saved as 'Bruce Willson.xls' on the path you specify.
Se exampel below:
_____________________________________________________
Sub GoPrint()
Const sSavePath As String = "C:\"
Dim oPrintRange As Range
Dim oSheet As Worksheet
Dim oWB As Workbook
Dim sStudent As String

Set oSheet = Worksheets("Sheet1")
Set oPrintRange = oSheet.Range("A1:G40")

sStudent = oSheet.Range("H1").Text

oPrintRange.PrintOut
oPrintRange.Copy

sStudent = sSavePath & sStudent & ".xls"

Set oWB = Workbooks.Add
oWB.ActiveSheet.Paste
oWB.ActiveSheet.Range("A1").Select
oWB.SaveAs sStudent
oWB.Close

Application.CutCopyMode = False
End Sub
_____________________________________________________
The row you want to change to specify the path is this one:
Const sSavePath As String = "C:\"

And dont forget the backslash at the end (ie: "C:\Students\"


If you want to get the workbook name from another location you need to change:
sStudent = oSheet.Range("H1").Text

Example:
sStudent = oSheet.Range("X27").Text
or
sStudent = Format(Now(), "YYYY-MM-DD HH MM")
If you want a time stamp only, if so you can skip the student name idéa if you wish.
0
 

Author Comment

by:topend
Comment Utility
Thanx a nmillion  !!!! SNilsson

Evry thing is working fine.

Marry Christmas and a Happy new year
cheers,
topend
0
 
LVL 8

Expert Comment

by:SNilsson
Comment Utility
And thanks to you topend !

Since this is your first time using EE you might not be aware of the grading policy her.

_______________________
A: The Expert(s) either provided you with a thorough answer or they provided you with a link to information that thoroughly answered your question. An "A" can also be given to any answer that you found informative or enlightening beyond the direct question that you asked.

B: The Expert(s) provided an acceptable solution, or a link to an acceptable solution, that you were able to use, although you may have needed a bit more information to complete the task.
_______________________

Thats the EE definition, and if you still think a B grade is correct (if you miss some info or such) please ask away, if not you can make a grade change by asking a moderator to change the grade with a link to this question at:
http://www.experts-exchange.com/Community_Support/

Kind Regards SNilsson



0
 
LVL 50

Expert Comment

by:Dave Brett
Comment Utility
Hi topend,

Glad to see you got there

Based on those comments
>>Thanx a nmillion  !!!! SNilsson
>>Evry thing is working fine.

He definitely deserves the A.

And In future you may wish to post your Excel questions to the Excel TA

Cheers

Dave
0
 
LVL 8

Expert Comment

by:SNilsson
Comment Utility
Thank you Topend !
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
This video will demonstrate how to find the puppet warp tool from the edit menu and where to put the points to edit.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now