Hi there,

You provided me with the below code and instructed me that 'everything I
need to deal with should now be declared as a Const.' Can you ellaborate? I
am not sure what you mean.

Sub ExportCoprecoReadingData()
Const sourceSheet = "Site Reading Log" ' source

Dim sourceBook As String
Dim destBook As String
Dim sourceRange As Range
Dim destRange As Range
Dim ans As Variant

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

Const is a constant which is a variabble that will not changge in your
program. It makes it simple to make one change at the beginning of your
program rather than making the change a lot of times in the program.

In the example below the sourcesheet is a constant. In a long program it
may be used 20 - 30 times. if you change the source sheett name with the
constant you only havve to change one line.

Const is very useful if you reuse code from one project to another. On one
progject the work sheet name can be master and on a new project the worksheet
name could be judges. It makes it easy to make the schange of the worksheet.

When people post problems often they leave out the details of the worksheet
names. By writing code using Const they can easily adapt the code to their
own project.


the code below The Workbook name can be made a Const if it will not change.
the worksheet name should be made a const so if someone starts the macro from
a diffferent worksheet the code will still work.


"Carlee" wrote:

Hi there,

You provided me with the below code and instructed me that 'everything I
need to deal with should now be declared as a Const.' Can you ellaborate? I
am not sure what you mean.

Sub ExportCoprecoReadingData()
Const sourceSheet = "Site Reading Log" ' source

Dim sourceBook As String
Dim destBook As String
Dim sourceRange As Range
Dim destRange As Range
Dim ans As Variant

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

I think JL meant that you only have to worry about the lines that start with
"Const".

In this example:
Const sourceSheet = "Site Reading Log" ' source

If your sheet changes names, just change this line to something like:
Const sourceSheet = "Anothersheetnamehere" ' source



Carlee wrote:

Hi there,

You provided me with the below code and instructed me that 'everything I
need to deal with should now be declared as a Const.' Can you ellaborate? I
am not sure what you mean.

Sub ExportCoprecoReadingData()
Const sourceSheet = "Site Reading Log" ' source

Dim sourceBook As String
Dim destBook As String
Dim sourceRange As Range
Dim destRange As Range
Dim ans As Variant

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

--

Dave Peterson