Inserting Worksheet Values with a Macro

@Lynn,

The code you specify changes the selection which may or may not be required/desirable. But you can achieve the same result in single line of code without changing the selection, and so is faster and more efficient.

cur_month1= range("cur_month") 'to get the value stored in this cell

OR

range("Cur_month")= 1234 'to set the value (change 1234 for whatever value you want or . use a variable)

This is good for entering or getting values for a named range:

Application.GoTo Reference:="cur_month"
cur_month1 = ActiveCell.Value

This goes to the named range CUR_MONTH, then puts that value into a variable in your macro called CUR_MONTH1.

The ".Value" is not absolutely necessary.

Cells(1,1)=23

works as well.

Building on the tip for Named ranges, my recurring report projects have a worksheet named "Controls". In there I build a table that has labels in column A, and the values go in column B, then name the ranges with the Create from Selection command in the UI.

Macros use statements like the following to keep them current, then the values are used to control what people may do and validating inputs
Workbook.Names

For example, when starting a month's report, the following establishes the scope of the report for a specific month.

wkbkReport.Names.("Dept_Code").RefersToRange = frm1.DepartmentProfile.Department
wkbkReport.Names.("Reporting_Month").RefersToRange = frm1.DepartmentProfile.Month

Then as the daily input files are validated, a function can validate whether it should be included

Const InputDeptCodeLocation as String = "B2"
Const InputReportDateLocation as String = "B3"

If wkbkInput.Sheets(1).Range(InputDeptCodeLocation ) = wkbkReport.Names("Dept_Code").RefersToRange And Month(wkbkReport.Sheets(1).Range(InputReportDateLocation)) = wkbkReport.Names.("Reporting_Month").RefersToRange Then ...

Or my personal favorite:

Range("Address") = var

where var is a 2-dimensional, base-1 variant array the same size and shape as Range("Address")

The above will put these values into cells on the Activesheet. By prefixing the Cells or Range method with the worksheet name or index you can put a value in any worksheet with first having to activate it.

e.g. Worksheets("Sheet1").Cells(1, 1).Value = 23
or Worksheets(1).Range("C4").Value = "Address"