Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2003-12-03
11
Medium Priority
?
475 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
11 Comments
 
LVL 8

Accepted Solution

by:
SNilsson earned 2000 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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

The top UI technologies you need to be aware of

An important part of the job as a front-end developer is to stay up to date and in contact with new tools, trends and workflows. That’s why you cannot miss this upcoming webinar to explore the latest trends in UI technologies!

Question has a verified solution.

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

There are many software programs on offer that will claim to magically speed up your computer. The best advice I can give you is to avoid them like the plague, because they will often cause far more problems than they solve. Try some of these "do it…
Want to know how to use Exchange Server Eseutil command? Go through this article as it gives you the know-how.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

670 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