VBA - Checking if active cell is named, then....

[suunto]

Hi All,

I'm trying to write a macro to insert new rows once the final row has been reached. I've named that cell "FinalLine" and have compiled the relevant bit of code below. The code works if the Active cell is the last "FinalLine" cell, but breaks when the active cell is not "FinalLine". Can anyone point me in the right direction? Ideally, if the cell is not "FinalLine", I would like the code to do nothing and continue with the rest of the code.

If ActiveCell.Name.Name = "FinalLine" Then
​

Selection.End(xlUp).Select
ActiveCell.EntireRow.Select
Selection.Resize(2).Select
Selection.Copy
Selection.Insert Shift:=xlDown
ActiveCell.PasteSpecial xlPasteValues​

Else
​

ActiveCell.Select​

End If​

Thanks in advanced!

 

[baitmaster]

please post the rest of your code. You're almost certainly using the wrong approach because you just shouldn't need to select and activate anything - its a common issue caused by recording macros. If you can post the rest of your code then it will be easier to clean this up properly and make it do what you really want to do

 

[suunto]

Thanks for the reply. I will warn you my approach is quite bad, but it is almost doing what I need it to do. Without the above code I've asked about, everything runs fine, it just becomes an issue when the file I'm preparing runs out of available lines.

To clarify what I'm doing, I'm creating an invoice based on activity that comes from workbook "glwb". The intent is to pull values from cells PX and HX of "glwb", where X is the row reference, and place them in relative spots on UKFCInv (invoice file). Everything runs fine, unless the the number of lines being pulled over are greater than say 6 (not actually 6 lines). If there are more than say 6 lines, the code will overwrite important data on the invoice. My solution to this was to name the final cell, so that when the code arrives at "finalline", it inserts new rows for the data, instead of overwriting what's on the invoice. The problem is if the cell is not named, the code breaks. I would like it to continue until it hits this final cell.

Windows(glwb).Activate
Worksheets("UKFC").Activate
nrowsUKFC = Worksheets("UKFC").Cells(Rows.Count, 1).End(xlUp).Row
Windows(InvUKFC).Activate
If (Range("A1") <> "Glkco") Then
If (nrowsUKFC >= 1) Then
Dim x As Long
For x = 1 To nrowsUKFC
Windows(glwb).Activate
Worksheets("UKFC").Activate
Range("P" & x).Select
Selection.Copy
Windows(InvUKFC).Activate
Range("finalline").Select
Selection.End(xlUp).Select
ActiveCell.Offset(RowOffset:=2).Activate
If ActiveCell.Name.Name = "finalline" Then
Selection.End(xlUp).Select
ActiveCell.EntireRow.Select
Selection.Resize(2).Select
Selection.Copy
Selection.Insert Shift:=xlDown
ActiveCell.PasteSpecial xlPasteValues
End If

Windows(glwb).Activate
Worksheets("UKFC").Activate
Range("H" & x).Select
Selection.Copy
Windows(InvUKFC).Activate
ActiveCell.Offset(ColumnOffset:=4).Activate
ActiveCell.PasteSpecial xlPasteValues

Next x
End If
End If

 

[suunto]

So I managed to solve this if anyone is interested:

On Error Resume Next
​

dummy = ActiveCell.Name.Name

If Len(dummy) = 0 Then
​

ActiveCell.Select​

Else
​

Selection.End(xlUp).Select
ActiveCell.EntireRow.Select
Selection.Resize(2).Select
Selection.Copy
Selection.Insert Shift:=xlDown
ActiveCell.Offset(RowOffset:=2, ColumnOffset:=3).Activate​

End If
​

Like I said, I don't believe this is the best approach, but it gets the job done for me..