Recorded Macro too specific

M

mistrellis

Board Regular
Joined
Mar 19, 2015
Messages
53
I have recorded my first macro on my data.
My data is 1000 rows.

Everything works fine.

When I recorded my macro I pasted my 1000 rows then double clicked on my formulas so that they copied down to calculate the 100 rows.

My problem is:

Today's data is 1500 rows long and my macro has recorded specifically to 1000 rows.

This would be the same if my data was 500 rows long.

How do I record a macro that isn't so specific ie. It copies the formulas comparable to how many rows are in the data.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Can you show the full code?

Using (edited to your preferences):
Code: 
Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Formula = 'formula here
would work
 
Last edited:
Upvote 0
Hi Tim
Do I need to research relative references?
Code: 
Sub Macro1()'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+u
'
    Range("AM2:BM2").Select
    Selection.AutoFill Destination:=Range("AM2:BM22992")
    Range("AM2:BM22992").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    Columns("W:AJ").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Rows("1:1").Select
    Range("O1").Activate
    ActiveWindow.SmallScroll ToRight:=12
    Columns("AM:AY").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1:AL1").Select
    Range("AL1").Activate
    Selection.AutoFilter
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A2").Select
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
End Sub
 
Upvote 0
First of all, get rid of the Scroll lines, they're useless and byproducts of scrolling when recording. Same for activating or selecting, try to avoid those. For example, instead of
Code: 
Range("AM2:BM2").Select
    Selection.AutoFill Destination:=Range("AM2:BM22992")
use
Code: 
Range("AM2:BM2").AutoFill Destination:=Range("AM2:BM22992")


Now secondly, I don't understand your question.
 
Upvote 0
If you want the copy to be down any number of rows which is yet undefined then try something like this. If column BM is not a good reference then please alter this code to suit.

Make sure and try it on a test workbook

Two versions

Code: 
Sub Macro1() '
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+u
'
Dim lst As Long
lst = Range("BM" & Rows.Count).End(xlUp).Row
    Range("AM2:BM2").AutoFill Destination:=Range("AM2:BM" & lst)
    Range("AM2:BM" & lst).Value = Range("AM2:BM" & lst).Value
    Columns("W:AJ").Delete Shift:=xlToLeft
    Columns("AM:AY").Delete Shift:=xlToLeft
    Range("A1:AL1").AutoFilter
    Cells.EntireColumn.AutoFit
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
End Sub


Sub Macro2() '
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+u
'
'*******************************************************
'If range AM to BM contains formulas then this will be better
'*******************************************************
Dim lst As Long
lst = Range("BM" & Rows.Count).End(xlUp).Row
With Range("AM2:BM" & lst)
    .Formula = Range("AM2:BM2").Formula
    .Value = .Value
End With
    Columns("W:AJ").Delete xlToLeft
    Columns("AM:AY").Delete xlToLeft
    Range("A1:AL1").AutoFilter
    Cells.EntireColumn.AutoFit
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
End Sub
 
Upvote 0
When I recorded my Macro there were 22992 rows of data
When I run my Macro the range is from AM2 to BM22992

If (tomorrow) my data has 30K rows then the Macro would stop at 22992 where I want it to copy down to the relevant amount (depending on that day's data).

When recording the Macro I simply double clicked the corner cell. This duplicated the formulas relative to the data. ie down to row 22992.

i was expecting the Macro to replicate this action as opposed to it being specific ie copy down to BM22992

Is that any clearer?
 
Upvote 0
I addressed your question in my first reply, but didnt understand your second question. Use that code to the correct range (which seems to be ("AM:BM")) and fill in the formula. It works the same as mrhstn's comment
 
Upvote 0
I apologise in advance for my simplness:

I'm unsure what "fill in the formula" means.

Range("AM2:BM2").Select

Contains different formula.

Selection.AutoFill Destination:=Range("AM2:BM22992")
This is what needs correcting.


I am afraid my VBA is not good enough to follow your code.
 
Upvote 0
mistrellis said:
I apologise in advance for my simplness:

I'm unsure what "fill in the formula" means.

Range("AM2:BM2").Select

Contains different formula.

Selection.AutoFill Destination:=Range("AM2:BM22992")
This is what needs correcting.


I am afraid my VBA is not good enough to follow your code.
Click to expand...

Did you run Macro2 from my post above? If there is something not working let us know what and we can guide you adjust it.
 
Upvote 0
Mistrellis,

What I mean to say is, my line of code simply pastes the formula you are using (you mentioned a formula in your first post) in to every cell in range AM2:BM2 until the last row. So what rests you is pasting the right formula (record the formula) in my line of code
Code: 
Range("AM2:BM2" & Range("AM" & Rows.Count).End(xlUp).Row).Formula = 'formula here

either that or just use mrhstn's code
 
Upvote 0
You must log in or register to reply here.

Similar threads

Guna13
  • Question
How can Igenerate a workbook consisting of over 1000 worksheets, each derived from the data in a specific cell, given that you have 1000 records in?
Replies
5
Views
129
Guna13
Guna13
C
  • Solved
Recording a Macro that Copies & Pastes into Other Sheets
Replies
1
Views
308
AlanY
A
C
  • Question
I need Macros help!!!
2 3
Replies
23
Views
670
mumps
mumps
P
  • Question
Applying conditional formatting to merged cells
Replies
1
Views
329
Joe4
Joe4
W
  • Question
How to monitor cells in a column for changes, to execute macro on a row?
Replies
5
Views
108
mumps
mumps

Forum statistics

Threads
1,215,516
Messages
6,125,285
Members
449,218
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top