Hi all
I am new to this group so I hope someone can kindly assist..
To say I am a novice in macros is an understatement so please forgive my
ignorance..
I tried to solve this but do not have the knowledge to do so and therefore
appreciate your help...
Currently my Macro should:
1. Turn off any filters
2. Number column A from 1 to 1000 (starting in A14)
3. Drags formula from K14-O14 down to last row of data shown in column A
4. then puts cursor in last empty cell in column B ready for user to
enter data
On point 3 - I want the range to be K14-O14 if active worksheet equals
"EXCHANGES" but if its on the "VALUATIONS" tab the autofill range should be
L14-P14..there are other sheets where the range changes also but if I at
least have the format I can add those in.
I have found bits and pieces of macros on the internet and put them
together so please excuse if my macro below is not the most effective for
my needs but here it is in it's current state:
Here is my macro:
Sub AddNewEntry()
'TURNS OFF FILTER IF FINDS ONE ON
Dim wks As Worksheet
ActiveSheet.Select
For Each wks In ActiveWorkbook.Worksheets
If ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("b13").AutoFilter
End If
Next wks
'Numbers column A from 1 to 1000 so that formulas drag down to end
Range("A14").Select
ActiveCell.FormulaR1C1 = "1"
Range("A15").Select
ActiveCell.FormulaR1C1 = "2"
Range("A14:A15").Select
Selection.AutoFill Destination:=Range("A14:A1013")
'drags formulas based on last entry in column A
Dim LR As Long
LR = Range("A2000").End(xlUp).Row
Range("K14:O14").AutoFill Destination:=Range("K14:O" & LR)
'Find the last used row in a Column: column B in this example
Dim FirstBlankCell As Range
Set FirstBlankCell = Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
FirstBlankCell.Activate
Dim LastRow As Long
Dim NextRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
NextRow = LastRow + 1
End Sub
Many thanks in advance!
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.
