VBA to drag formulas into next blank column and hard paste the figures in previous section

malikumair56

New Member
Joined
Jan 22, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Hi guys,

I am a noob in VBA but love playing with it, i am after a code which can copy a formula from one column and paste the formula in the next column, once done then hard paste the figure in previous column where the formula was first dragged from.
I tried using MACRO by recording but that wont help as that will only repeat from the same column rather then moving to next column.
FOr example, i have billing per month and also revenue per month, Billing for Jan is in Column N and Revenue for Jan is in column AB, once the month end is done, to start a new month, i want the VBA to drag the formula from Column N to column O and same for Column AB to Column AC and once the ula is dragged, hard pasted the figures in Column N and Column AB.
Pic is attached for reference. Thanks in advance
1611324177302.png
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
If N5 had =A5 in it what would you want in O5 =B5 or =A5?
 

malikumair56

New Member
Joined
Jan 22, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
HI Mate, thanks for your reply, O5=A5.
As formulas all absolute so it stays the same that's why want to hard-paste once formula is dragged after each month end to start a new month

Thanks
Cheers
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What row is the first row with formula on?
 

malikumair56

New Member
Joined
Jan 22, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What row is the first row with formula on?
for Billing, N33 is the cell which has the cell and VBA should auto pick last Cell in the Column to drag the formula to M33 before hard paste in N33
Also for Revenue, Formula Sits in AB33 and same as above VBA picks last cell in AB33 and dragging the formula to AC before hard past in AB33
1611475876533.png

Thanks again
 

malikumair56

New Member
Joined
Jan 22, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Currently last cell is N1179 and AB1179 but this will keep on growing as i move on with months due to new data
1611475978839.png
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

I didn't ask about the last cells, I asked for the first row with the formula. From your picture it looks like row 13 but I need it confirmed.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Try the code below on a copy of your data

VBA Code:
Sub malikumair56()
    With Range(Cells(33, Cells(33, 25).End(xlToLeft).Column), Columns(Cells(33, 25).End(xlToLeft).Column).Find("*", , xlFormulas, , xlByRows, xlPrevious))
        .Offset(, 1).Formula = .Formula
        .Value = .Value
    End With

    With Range(Cells(33, Cells(33, 39).End(xlToLeft).Column), Columns(Cells(33, 39).End(xlToLeft).Column).Find("*", , xlFormulas, , xlByRows, xlPrevious))
        .Offset(, 1).Formula = .Formula
        .Value = .Value
    End With
End Sub
 

malikumair56

New Member
Joined
Jan 22, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Hi Mark, thanks a ton, it works like a charm.
I know it serves my purpose now, but for my own learning, can you please dissect each step and explain what exactly its doing.
I try to see it clicking F8 but it won't show.
As l love, BAs but not so great in writing, so really appreciate if you can explain pls so it can help in my learning
Thanks a Mil again
Malik
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

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
Top