After Copying in a Worksheet And Renaming it Create a Range Using the Worksheet Name

[Shiseiji]

I'm working to automate as much as possible a rather simple process that starts after I download a report, never more than once a day. With a lot of learning by MUCH Googling, trial and many, many errors, I have a set of vba routines that run through about half the steps. Being ignorant and of bad memory (unable to recall what I did to use "Call" in an earlier project), what a joy, probably some unnecessary items, probably some redundant, but it's working LOL, I have them all in a routine:

Public Sub m_BiReport()

'Module name: bireport​

Application.Run "m_OpenCopySheetClose"
Application.Run "m_BiNameSheet"
Application.Run "m_BiCleanData"
Application.Run "m_BiHeadings"
Application.Run "m_BiOverDate"​

'Application.Run "m_​

End Sub


I open the .xlsm file.
I call the routine that asks for the file name to be opened.
It then copies out the single worksheet into the .xlsm file and closes the other workbook
The .xlsx worksheet has 5 merged header rows that include the created date/time.
I use the date to rename the worksheet

Public Sub BiNameSheet()
Range("A2").Select
Selection.NumberFormat = "m/d/yyyy"
Range("A1").Select
Selection.NumberFormat = "mm dd yy"
Range("A1").Select 'redundant?
ActiveCell.FormulaR1C1 = "=TEXT(R[1]C,""m dd yy"")"
ActiveSheet.Name = ActiveSheet.Range("A1").Value
End Sub
​

Then I delete the 5 header rows and the blank/unneeded columns.

I enter 3 columns on the left, enter column titles, and a formula that renders a "Yes" if the specified cell on the row has a date greater than or equal to 6 months earlier than today. Formula is copied down to the last row.
Copy and paste as values.
So far so good.
Now I want to name a range for reporting and I can't figure out how to name the filled range using the worksheet name and a prefix. Should I use code similar to what I used to rename the worksheet to create a string "11_9_16Ovr6" and use that to name the range before I delete the rows? And, uh, how would I do that?

Better ideas?
Batting zero, time to ask for "help"!

TIA

rON

Just in case anyone is interested or has more elegant code:

Public Sub m_OpenCopySheetClose()
Application.Calculate
'ThisWorkbook.Worksheets("Transition Input").Select
Dim GetFilenameFromPath As String
Dim i As Integer, wb As Workbook
Dim TransitionInput_FileNAME
Dim Path_TransitionInput
Dim Name_TransitionInput
Dim newFileName As String
TransitionInput_FileNAME = Application.GetOpenFilename
Set wbk1 = ActiveWorkbook
Set wbk2 = Workbooks.Open(TransitionInput_FileNAME)

'wbk1.Sheets(1) is the Report sheet​

wbk2.Sheets.Copy After:=wbk1.Sheets(1)
wbk2.Close savechanges:=False
End Sub
__________________________________________________________________

Public Sub m_BiHeadings()
Columns("A:C").Select
Range("C1").Activate
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "New to Report"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Dropped Off Report"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Over Six Months"
Range("D1").Select
Selection.Copy
Range("A1:C1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A:J").Select
Selection.WrapText = False
Selection.Columns.AutoFit
Selection.Rows.AutoFit
Range("A1").Select
End Sub
_________________________________________________
Public Sub m_bicleandata()
'bicleandata
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Cells.Select
Selection.UnMerge
Rows("1:5").Select
Selection.Delete Shift:=xlUp
Range("D:D,F:F,K:K,G:G,H:H,I:I,J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("I:I").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Clear
Application.Run "m_delem"
Cells.Select
Selection.WrapText = False
Selection.Columns.AutoFit
Selection.Rows.AutoFit
Range("A2:J" & LastRow).Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With ActiveWindow
.DisplayGridlines = True
.GridlineColorIndex = 15
.DisplayHeadings = True
End With
Range("E:H").Select
Selection.NumberFormat = "m/d/yy"
Range("A1").Select
End Sub

__________________________________________________________________________________
Public Sub m_delem()
'http://stackoverflow.com/questions/31092719/delete-empty-rows-using-vba-ms-excel?rq=1
Dim last As Long
Dim current As Long
Dim col As Long
Dim retain As Boolean


last = Cells(Rows.Count, "B").End(xlUp).Row
For current = last To 1 Step -1
retain = False
For col = 3 To 26
If Cells(current, col).Value <> vbNullString Then
retain = True
Exit For
End If
Next col
If Not retain Then Rows(current).Delete
Next current
End Sub
_______________________________________________________________
Public Sub m_BiOverDate()

'​

' BiOverDate Macro​

'​

Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
Range("C2").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 1
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = _
"=IF(RC[5]<=DATE(YEAR(NOW()),MONTH(NOW())-6,DAY(NOW())),""Yes"","""")"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C" & LastRow)
Range("A1").Select
End Sub

 

[RCBricker]

Firs when posting code please use code tags.

This is done by starting the code block with the word code with the open square bracket [ and then following it with the close square bracket ].

to end the code block is the same way except the word is /code.

Here is the code for naming a range

  RangeName = "Price"
  CellName = "D7"
  
  Set cell = Worksheets("Sheet1").Range(CellName)
  ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell

Now then. You look to be doing a lot of your coding by recording macros. Great place to learn VBA and usually a great place to get ideas about how to do things. Best place to record how to convert formulas to VBA. Also as you clean up the recording add variables, indents and comments. All of these will make it easier in six months when you want to make a change or have to make a change to the code.

here is an example of the difference

Public Sub m_BiHeadings()
 Columns("A:C").Select
 Range("C1").Activate
 Selection.Insert Shift:=xlToRight
 Range("A1").Select
 ActiveCell.FormulaR1C1 = "New to Report"
 Range("B1").Select
 ActiveCell.FormulaR1C1 = "Dropped Off Report"
 Range("C1").Select
 ActiveCell.FormulaR1C1 = "Over Six Months"
 Range("D1").Select
 Selection.Copy
 Range("A1:C1").Select
 Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
 SkipBlanks:=False, Transpose:=False
 Application.CutCopyMode = False
 Range("A:J").Select
 Selection.WrapText = False
 Selection.Columns.AutoFit
 Selection.Rows.AutoFit
 Range("A1").Select
 End Sub

My m_BiHeadings

Sub m_BiHeadings2()
Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Columns("C").Insert
    ws.Range("A1").Value = "New to Report"
    ws.Range("B1").Value = "Dropped Off Report"
    ws.Range("C1").Value = "Over Six Months"
    ws.Range("D1").Copy
    ws.Range("A1:C1").PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
    ws.Range("A:J").WrapText = False
    ws.Columns.AutoFit
    ws.Rows.AutoFit
End Sub

However, excel macro recorder is very inefficient. most of the lines can be deleted. The first step is understanding that you should never have to use .select or .activate in your code.

 Range("D1").Select
 Selection.Copy

is the same as

ws.Range("D1").Copy

All I did was delete everything between the .copy on the second line and the . in the first line (effectively deleting the select).

HTH

 

[Shiseiji]

Thanks for the assist. My struggles are that often what I want/need requires a foundation that quickly becomes quicksand when my head starts to swim . . . . I can read the code you provided:

 RangeName = "Price"  CellName = "D7"
  
  Set cell = Worksheets("Sheet1").Range(CellName)
  ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell

But your example is a single cell. I don't have the necessary knowledge/skills/abilities to use that example to assign a name, using the worksheet name, to a range of cells that will change in length from 400 cells today, to 425 on tomorrows report that's a new worksheet . . . .

The first step is understanding that you should never have to use .select or .activate in your code.

I "thought" so, but struggled with getting consistent results, probably because when fatigue sets in I realize I've utterly failed at note taking/copying to a remark first what I'm trying to change . . .

P.S. My brother is an engineer at Micron in Boise. Duh, never thought to ask if he programs in VBA . . .

Ron

 

[RCBricker]

if you know the range you need you can change the "D7" to contain that information. so if your range is A1 through C12 just change the

cell="D7"

to

cell="A1:C12"

or better yet don't use the cell variable and just set it as a range.

the best way to set a range is dynamically, however, in a pinch you can hard code.

Set cell=worksheets("sheet1").range("A1:C12")
set cell=worksheets("sheet1").range(worksheets("sheet1").cells(1,1),worksheets("sheet1").cells(12,3))

both lines set the range variable cell to a range that contains all cells from A1 through C12.


Dynamically is best because it allows for changes to data. to do this you would need to use variables to set the parameters of your data. Such as the last column number or last row number used.

I am not saying you should not record the macro. Do so. But each time you run into a line that ends with .select and the next line begins with selection., just combine the two lines by deleting the selection. and the .select. The resulting line will do the exact same thing the two lines did.

you can use that code and instead of the refersto: cell change it to the range you need

 RangeName = "Price"
  ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=Worksheets("Sheet1").Range("A1:C12")