PDA

View Full Version : How to export part of a sheet



ioncila
12-22-2009, 05:25 AM
Hi
I've already learnt how to export a sheet to a new workbook and email it.
But What I wish to do is to export only the first 25 rows.

What changes do I need to do in the code below?

Private Sub CommandButton4_Click()

msg = "Do you wish to create a new workbook?"
If MsgBox(msg, vbQuestion + vbYesNo, "ATTENTION") = vbNo Then
Exit Sub
End If

Dim sFile As String
Dim avLink As Variant
Dim vLink As Variant
Dim strDate As String

strDate = Format(Date, "dd mmmm yyyy") & " " & Format(Time, "hh:mm:ss")
Worksheets(Array("MONTH RESUME")).Copy

With ActiveWorkbook
sFile = Application.GetSaveAsFilename( _
FileFilter:="Excel Files (*.xls), *.xls", _
Title:="Specify Location for Copy:")

If sFile <> "False" Then
avLink = .LinkSources(xlExcelLinks)
For Each vLink In avLink
.BreakLink Name:=vLink, Type:=xlLinkTypeExcelLinks
Next vLink

.SaveAs Filename:=sFile
End If
.SendMail "abcde@gmail.com", _
"MONTH REPORT" & " " & strDate
.Close SaveChanges:=False
End With
End Sub

Thanks

Bob Phillips
12-22-2009, 06:32 AM
Why not export the whole sheet, and delete anything past row 25? Seems the simplest way to me.

GTO
12-22-2009, 06:32 AM
Not well tested, but try tacking in:

Sheets(1).Rows("26:" & Rows.Count).Delete

...right above .SaveAs ...

Hope that helps,

Mark

Bob Phillips
12-22-2009, 07:33 AM
Not well tested, but try tacking in:

Sheets(1).Rows("26:" & Rows.Count).Delete

...right above .SaveAs ...

Hope that helps,

Mark

Don't you mean Rows.Count - 25?

ioncila
12-22-2009, 08:29 AM
Sorry, I forgot a relevant point:
Three Sheets must be exported but only one will export a part ("MOVEMENTS")

Worksheets(Array("MONTH RESUME", "MOVEMENTS", "BASE")).Copy


How do I implement suggestions above? Just as the below?

Dim ws1, ws2, ws3 As Worksheet
ws1=Worksheets("MONTH RESUME")
ws2=Worksheets("MOVEMENTS")
ws3=Worksheets("BASE")
...
Worksheets(Array(ws1, ws2.Rows.Count-25, ws3)).Copy
...


Thanks

Bob Phillips
12-22-2009, 03:06 PM
Delete the part from just the one, what is the issue?

ioncila
12-22-2009, 03:30 PM
Delete the part from just the one, what is the issue?

Sorry, could you be more specific?

GTO
12-22-2009, 06:01 PM
Don't you mean Rows.Count - 25?

Ehhh... Well, I am afraid that if that is what I should have meant, I must be having a 'face too close to the tree, can't see the forest' day :dunno

Full admission to testing awfully quick, but given the OP's orig request to leave 25 rows, I was thinking that after the Copy, we would have a new/one sheet/active workbook, and that 'Sheets(1).Rows("26:65536").Delete' would leave the first 25 rows.

Sorry, I'm still staring and just missing it?

Mark