Hello All. I use the following code to copy the first row, and last
populated row to a new work book. It works great.

Question: How can I modify this code so that it takes the SECOND row, not
the first, and last populated row.

Your help is always appreciated.

***************CODE**********************************

Sub ExportRaglanDailySubmission()
'these have to do with THIS workbook
'name of the sheet to get data from
Const sourceSheet = "Site Reading Log"
'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 = "Raglan Daily Reading.xls"
'******

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

'we are in pre-Excel 2007 version
MaxLastRow = Rows.Count

'keeps screen from flickering
'speeds things up also
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
Set sourceRange = Nothing

Application.DisplayAlerts = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

--
Carlee

Change this line

Set sourceRange = ActiveSheet.Rows("1:1")

to

Set sourceRange = ActiveSheet.Rows("2:2")

However your code is not detecting the first used row it is taking row 1 but
if that is OK the above change should work.

--

Regards,
Nigel
(E-Mail Removed)



"Carlee" <(E-Mail Removed)> wrote in message
news:65011ED5-D5A8-48F5-8510-(E-Mail Removed)...
> Hello All. I use the following code to copy the first row, and last
> populated row to a new work book. It works great.
>
> Question: How can I modify this code so that it takes the SECOND row, not
> the first, and last populated row.
>
> Your help is always appreciated.
>
> ***************CODE**********************************
>
> Sub ExportRaglanDailySubmission()
> 'these have to do with THIS workbook
> 'name of the sheet to get data from
> Const sourceSheet = "Site Reading Log"
> '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 = "Raglan Daily Reading.xls"
> '******
>
> Dim sourceBook As String
> Dim destBook As String
> Dim sourceRange As Range
> Dim destRange As Range
> Dim MaxLastRow As Long
> Dim pathToUserDesktop As String
>
> 'we are in pre-Excel 2007 version
> MaxLastRow = Rows.Count
>
> 'keeps screen from flickering
> 'speeds things up also
> 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
> Set sourceRange = Nothing
>
> Application.DisplayAlerts = False
> Application.DisplayAlerts = True
> Application.ScreenUpdating = True
>
> End Sub
>
> --
> Carlee