Solved

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

Posted on 2003-12-03
11
442 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
 

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
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.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What does Survey Monkey cost? 7 102
Clipboard Manager for Windows 7 5 57
JItbit AD intergration 4 76
Java JRE greater than 1.6 5 50
Storage devices are generally used to save the data or sometime transfer the data from one computer system to another system. However, sometimes user accidentally erased their important data from the Storage devices. Users have to know how data reco…
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
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.
This video will demonstrate how to find the puppet warp tool from the edit menu and where to put the points to edit.

914 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

21 Experts available now in Live!

Get 1:1 Help Now