Solved

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

Posted on 2003-12-03
11
453 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
ID: 9866670
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
ID: 9872144
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
ID: 9872358
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:topend
ID: 9873394
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
ID: 9873788
>> Thanx to you gays

:)

0
 
LVL 8

Expert Comment

by:SNilsson
ID: 9873836
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
ID: 9880647
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
ID: 9880819
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
ID: 9880845
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
ID: 9889274
Thank you Topend !
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
Healthcare organizations in the United States must adhere to the guidance of both the HIPAA (Health Insurance Portability and Accountability Act) and HITECH (Health Information Technology for Economic and Clinical Health Act) for securing and protec…
This video will demonstrate how to find the puppet warp tool from the edit menu and where to put the points to edit.
Viewers will learn how to use the Hootsuite Dashboard.

808 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