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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
That error says the word isn't there Check for spaces around the word.
 

malikumair56

New Member
Joined
Jan 22, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
It worked thanks, i believe i had a data validation between actual and forecast which might be causing the e
i removed data validation and it works great now. thanks Mate.
You are a gem:)
 

malikumair56

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

ADVERTISEMENT

Sorry one more issue now. Billing side is working but Revenue side is not working


1611825009890.png
Billing and revenue side works when i run VBA the first but when i run it agai after changing forecast to Actual billing side is fine but revenue side stops working:(
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
That is because you have multiple Actuals after the one that you want. On the left hand side you have 4 Actuals before the Forecast, on the Right you have 7.
I am afraid I can't do anything with that as I can't code for inconsistent layouts.
 

malikumair56

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

ADVERTISEMENT

Sorry, screen shot wont do justice, actuals are the same, reeason you are seeing 4 on left as i have freeze pane, left one is April if you count from Jan til July its 7 as well.
Layout is same
In this screen shot its same
1611825638117.png
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Afraid I would need to see the actual workbook rather than an image to see what your issue is.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Happy that you worked it out.
 

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