Solved

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

Posted on 2003-12-03
11
448 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

I previously wrote an article addressing the use of UBCD4WIN and SARDU. All are great, but I have always been an advocate of SARDU. Recently it was suggested that I go back and take a look at Easy2Boot in comparison.
In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
The viewer will learn common shortcuts with easy ways to remember them. The viewer will then learn where to find all of the keyboard shortcuts, how to create/change them, and how to speed up their workflow.
The viewer will learn how to successfully download and install the SARDU utility on Windows 8, without downloading adware.

813 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

15 Experts available now in Live!

Get 1:1 Help Now