This worked beautifully. Now...I have yet another issue...

This new book and data, I have created (thanks to you) will be sent via
email to a user. I need to be able to extract the data sent (minus column
headings) and import it into the last line of a master file.

Can you wave your magic wand, once again?

--
Carlee


"JLatham" wrote:

First thing I see is that the With Sheets() instruction has a different sheet
name than you mention in your description. That sheet name should be the
actual name of the sheet that your source data is on. You would also need a
sheet named sheet16 in the workbook for it to function properly.

But this all takes place in the same workbook, not between two separate
workbooks (.xls files).

The code here will copy 1st and last row of data on a sheet to Sheet1 in a
new workbook. It works to some degree because we know that in a new workbook
there is always a sheet named "Sheet1" and that any sheet has a row 1 and a
row 2. I show this code within a regular Sub declaration, but you could copy
the code between Sub ... and End Sub and replace the code in your
Button2_Click routine with it. It works based on Button2 being in the same
workbook with the source data.

Where you see an instruction end with " _" it means that the instruction
continues on the next row. Don't worry - Excel will deal with it just fine.
I just wanted you to know what it means.

Two things that may need changing: the Const sourceSheet should be set to =
the exact name of the sheet that you want to copy row 1 and last row from.
Later in the code where you see "A" - change that (if needed) to refer to a
column that will always have information in that last row so that the program
can find it. That line of code will find the last cell in the referenced
column with information in that column.

Sub CopyToNewWorkbook()
Const sourceSheet = "Site Master Log" ' change??
Dim sourceBook As String
Dim destBook As String
Dim sourceRange As Range
Dim destRange As Range

Application.ScreenUpdating = False
sourceBook = ThisWorkbook.Name
Workbooks.Add ' create new book
destBook = ActiveWorkbook.Name
Windows(sourceBook).Activate
Worksheets(sourceSheet).Select

Set sourceRange = ActiveSheet.Rows("1:1")
Set destRange = Workbooks(destBook).Worksheets( _
"Sheet1").Rows("1:1")
destRange.Value = sourceRange.Value
'change "A" to column with data in last row
Range("A" & Rows.Count).End(xlUp).Select
Set sourceRange = ActiveSheet.Rows( _
ActiveCell.Row & ":" & ActiveCell.Row)
Set destRange = Workbooks(destBook).Worksheets( _
"Sheet1").Rows("2:2")
destRange.Value = sourceRange.Value
Set sourceRange = Nothing
Set destRange = Nothing
End Sub



"Carlee" wrote:

Hi there,
I am pretty new to MS Excel, but this is what i want to do. I have asked
for help on this already, but haven't had much luck. I apprevciate everyones
patiences.

I have a 'Site Master Log' sheet which contains site readings. There are
many site readings.

I want to be able to click a button on my user form and have the last row of
'Site Master Log' copied (including column headers) and pasted into new
workbook. This new workbook will be used as an attachment for a subsequent
email.

The code i was given (see below) stated the 'subscript was out of range',
but i don't know what that means.

Thanks for your help

Sub Button2_Click()
With Sheets("Site Reading Log") 'source
.Rows(.Cells(Rows.Count, "a").End(xlUp).Row).Copy Sheets("sheet16").Rows(1)
'destination

End With


End Sub

--
Carlee

Try this - be sure and change the added Const values as needed for your
actual setup. Everything you need to deal with should now be declared as a
Const so
that you can make those changes without having to go any further into the
code.

It is also set up to continue to work properly when you finally move up to
Excel 2007 or later. In saving the new workbook with just the 2 rows of data
(header and data) it will not prompt/warn you that you may be overwriting a
file of the same name - it just does it. The Master workbook will be opened
for use if it is not open at the start of all of this, and it will be closed
before the process is complete unless it turns out that the master sheet in
that workbook is full, in which case the new data won't get written to it,
you'll get an alert message and the Master workbook will remain open.

As before, if you want to associate it with a button in the source workbook,
just copy the code between the Sub and End Sub statements into the button's
_Click() event code area.

Sub CopyToNewWorkbook()
'these have to do with THIS workbook
'name of the sheet to get data from
Const sourceSheet = "Site Master Log" ' change??
'column that always have value in it in last row
Const sourceKeyColumn = "A"
'****
'This is the name you want to give to the
'NEW workbook created each time to put new data
'into as set up this code will overwrite any
'existing file of this name without any warning.
Const newWorkbookName = "Copreco Reading.xls"
'****
'these have to do with the MASTER workbook that
'keeps all data in it
'if this book is on a network drive/system
'then use the path to it as shown in My Network Places
'so that anyone using the workbook can use it reliably
'without having same drive mapping in effect, as:
' Const masterBook = "\\SharedSystem\folder\MasterFile.xls"
'need the full path and name here
'***change as required***
Const masterBook = "C:\folder\folder\MasterFile.xls"
'sheet in Master Workbook to add data to
' Change as required
Const masterSheet = "MasterSheet"

Dim sourceBook As String
Dim destBook As String
Dim sourceRange As Range
Dim destRange As Range
Dim masterBookLastRow As Long
Dim MaxLastRow As Long

'set up to use pre- and post-Excel 2007 files
If Val(Left(Application.Version, 2)) < 12 Then
'pre Excel 2007
MaxLastRow = Rows.Count
Else
'Excel 2007 (or later)
MaxLastRow = Rows.CountLarge
End If
Application.ScreenUpdating = False
sourceBook = ThisWorkbook.Name
Workbooks.Add ' create new book
destBook = ActiveWorkbook.Name
Windows(sourceBook).Activate
Worksheets(sourceSheet).Select

Set sourceRange = ActiveSheet.Rows("1:1")
Set destRange = Workbooks(destBook).Worksheets( _
"Sheet1").Rows("1:1")
destRange.Value = sourceRange.Value
Range(sourceKeyColumn & MaxLastRow).End(xlUp).Select
Set sourceRange = ActiveSheet.Rows( _
ActiveCell.Row & ":" & ActiveCell.Row)
Set destRange = Workbooks(destBook).Worksheets( _
"Sheet1").Rows("2:2")
destRange.Value = sourceRange.Value
Set destRange = Nothing
'save new workbook, but don't nag
'user with "file exists" message
Application.DisplayAlerts = False
With Workbooks(destBook)
.SaveAs newWorkbookName ' rename it
.Close
End With
Application.DisplayAlerts = True
'next trick
'larger bunny from smaller hat
'reuse destBook
destBook = Right(masterBook, Len(masterBook) - _
InStrRev(masterBook, Application.PathSeparator))
'see if Master Book is already open
On Error Resume Next
Windows(destBook).Activate
If Err < 0 Then
'wasn't open, open it
Err.Clear
On Error GoTo 0
Workbooks.Open Filename:=masterBook
End If
On Error GoTo 0

masterBookLastRow = ActiveWorkbook.Sheets(masterSheet). _
Range(sourceKeyColumn & MaxLastRow).End(xlUp).Row + 1
If masterBookLastRow MaxLastRow Then
MsgBox "Master Sheet is Full. Cannot add data." _
& vbOKOnly + vbCritical, "Aborting"
'exits, leaving both workbooks open
GoTo ExitCTNW
End If
Set destRange = Workbooks(destBook).Worksheets( _
masterSheet).Rows(masterBookLastRow & ":" & _
masterBookLastRow)
destRange.Value = sourceRange.Value ' new data added
Set destRange = Nothing
Application.DisplayAlerts = False
With Workbooks(destBook)
.Save
.Close
End With
ExitCTNW:
Set sourceRange = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


"Carlee" wrote:

In your first miracle, is it possible to save the new workbook created as
"Copreco Reading"
--
Carlee


"JLatham" wrote:

Glad the first wand-wave worked. Now, I presume that we are now dealing with
copying row 2 from the new workbook, where we just copied the heading row and
one row of data into (Sheet1) and are now going to copy that to yet a 3rd
workbook?

If that's the case we can modify the previous code just a little to see if
that other workbook is open, open it if it isn't, find a specific sheet in it
and take what we pasted into row 2 of the new book into the last row on the
sheet in your master book. While I'm dealing with that, I'll wait for your
response to this. Got a couple of VERY hot irons in the fire right now, so I
can deal with those until I get notice you've replied and then I can put the
code together properly for you.

"Carlee" wrote:

This worked beautifully. Now...I have yet another issue...

This new book and data, I have created (thanks to you) will be sent via
email to a user. I need to be able to extract the data sent (minus column
headings) and import it into the last line of a master file.

Can you wave your magic wand, once again?

--
Carlee


"JLatham" wrote:

First thing I see is that the With Sheets() instruction has a different sheet
name than you mention in your description. That sheet name should be the
actual name of the sheet that your source data is on. You would also need a
sheet named sheet16 in the workbook for it to function properly.

But this all takes place in the same workbook, not between two separate
workbooks (.xls files).

The code here will copy 1st and last row of data on a sheet to Sheet1 in a
new workbook. It works to some degree because we know that in a new workbook
there is always a sheet named "Sheet1" and that any sheet has a row 1 and a
row 2. I show this code within a regular Sub declaration, but you could copy
the code between Sub ... and End Sub and replace the code in your
Button2_Click routine with it. It works based on Button2 being in the same
workbook with the source data.

Where you see an instruction end with " _" it means that the instruction
continues on the next row. Don't worry - Excel will deal with it just fine.
I just wanted you to know what it means.

Two things that may need changing: the Const sourceSheet should be set to =
the exact name of the sheet that you want to copy row 1 and last row from.
Later in the code where you see "A" - change that (if needed) to refer to a
column that will always have information in that last row so that the program
can find it. That line of code will find the last cell in the referenced
column with information in that column.

Sub CopyToNewWorkbook()
Const sourceSheet = "Site Master Log" ' change??
Dim sourceBook As String
Dim destBook As String
Dim sourceRange As Range
Dim destRange As Range

Application.ScreenUpdating = False
sourceBook = ThisWorkbook.Name
Workbooks.Add ' create new book
destBook = ActiveWorkbook.Name
Windows(sourceBook).Activate
Worksheets(sourceSheet).Select

Set sourceRange = ActiveSheet.Rows("1:1")
Set destRange = Workbooks(destBook).Worksheets( _
"Sheet1").Rows("1:1")
destRange.Value = sourceRange.Value
'change "A" to column with data in last row
Range("A" & Rows.Count).End(xlUp).Select
Set sourceRange = ActiveSheet.Rows( _
ActiveCell.Row & ":" & ActiveCell.Row)
Set destRange = Workbooks(destBook).Worksheets( _
"Sheet1").Rows("2:2")
destRange.Value = sourceRange.Value
Set sourceRange = Nothing
Set destRange = Nothing
End Sub



"Carlee" wrote:

Hi there,
I am pretty new to MS Excel, but this is what i want to do. I have asked
for help on this already, but haven't had much luck. I apprevciate everyones
patiences.

I have a 'Site Master Log' sheet which contains site readings. There are
many site readings.

I want to be able to click a button on my user form and have the last row of
'Site Master Log' copied (including column headers) and pasted into new
workbook. This new workbook will be used as an attachment for a subsequent
email.

The code i was given (see below) stated the 'subscript was out of range',
but i don't know what that means.

Thanks for your help

Sub Button2_Click()
With Sheets("Site Reading Log") 'source
.Rows(.Cells(Rows.Count, "a").End(xlUp).Row).Copy Sheets("sheet16").Rows(1)
'destination

End With


End Sub

--
Carlee

Yes, this is what i need.

--
Carlee


"JLatham" wrote:

Glad the first wand-wave worked. Now, I presume that we are now dealing with
copying row 2 from the new workbook, where we just copied the heading row and
one row of data into (Sheet1) and are now going to copy that to yet a 3rd
workbook?

If that's the case we can modify the previous code just a little to see if
that other workbook is open, open it if it isn't, find a specific sheet in it
and take what we pasted into row 2 of the new book into the last row on the
sheet in your master book. While I'm dealing with that, I'll wait for your
response to this. Got a couple of VERY hot irons in the fire right now, so I
can deal with those until I get notice you've replied and then I can put the
code together properly for you.

"Carlee" wrote:

This worked beautifully. Now...I have yet another issue...

This new book and data, I have created (thanks to you) will be sent via
email to a user. I need to be able to extract the data sent (minus column
headings) and import it into the last line of a master file.

Can you wave your magic wand, once again?

--
Carlee


"JLatham" wrote:

First thing I see is that the With Sheets() instruction has a different sheet
name than you mention in your description. That sheet name should be the
actual name of the sheet that your source data is on. You would also need a
sheet named sheet16 in the workbook for it to function properly.

But this all takes place in the same workbook, not between two separate
workbooks (.xls files).

The code here will copy 1st and last row of data on a sheet to Sheet1 in a
new workbook. It works to some degree because we know that in a new workbook
there is always a sheet named "Sheet1" and that any sheet has a row 1 and a
row 2. I show this code within a regular Sub declaration, but you could copy
the code between Sub ... and End Sub and replace the code in your
Button2_Click routine with it. It works based on Button2 being in the same
workbook with the source data.

Where you see an instruction end with " _" it means that the instruction
continues on the next row. Don't worry - Excel will deal with it just fine.
I just wanted you to know what it means.

Two things that may need changing: the Const sourceSheet should be set to =
the exact name of the sheet that you want to copy row 1 and last row from.
Later in the code where you see "A" - change that (if needed) to refer to a
column that will always have information in that last row so that the program
can find it. That line of code will find the last cell in the referenced
column with information in that column.

Sub CopyToNewWorkbook()
Const sourceSheet = "Site Master Log" ' change??
Dim sourceBook As String
Dim destBook As String
Dim sourceRange As Range
Dim destRange As Range

Application.ScreenUpdating = False
sourceBook = ThisWorkbook.Name
Workbooks.Add ' create new book
destBook = ActiveWorkbook.Name
Windows(sourceBook).Activate
Worksheets(sourceSheet).Select

Set sourceRange = ActiveSheet.Rows("1:1")
Set destRange = Workbooks(destBook).Worksheets( _
"Sheet1").Rows("1:1")
destRange.Value = sourceRange.Value
'change "A" to column with data in last row
Range("A" & Rows.Count).End(xlUp).Select
Set sourceRange = ActiveSheet.Rows( _
ActiveCell.Row & ":" & ActiveCell.Row)
Set destRange = Workbooks(destBook).Worksheets( _
"Sheet1").Rows("2:2")
destRange.Value = sourceRange.Value
Set sourceRange = Nothing
Set destRange = Nothing
End Sub



"Carlee" wrote:

Hi there,
I am pretty new to MS Excel, but this is what i want to do. I have asked
for help on this already, but haven't had much luck. I apprevciate everyones
patiences.

I have a 'Site Master Log' sheet which contains site readings. There are
many site readings.

I want to be able to click a button on my user form and have the last row of
'Site Master Log' copied (including column headers) and pasted into new
workbook. This new workbook will be used as an attachment for a subsequent
email.

The code i was given (see below) stated the 'subscript was out of range',
but i don't know what that means.

Thanks for your help

Sub Button2_Click()
With Sheets("Site Reading Log") 'source
.Rows(.Cells(Rows.Count, "a").End(xlUp).Row).Copy Sheets("sheet16").Rows(1)
'destination

End With


End Sub

--
Carlee