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
 

malikumair56

New Member
Joined
Jan 22, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
The reason i ask for explanation, so i can do the same in the next sheet in the same workbook.
I want to do the same in the next sheet named "Retainer Fee" to drag formula from Row 14 from Column E and Column R to the next column F and S respectively.
1611491631704.png
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
I will write an explanation but it will have to wait until I feel a bit better.
 

malikumair56

New Member
Joined
Jan 22, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Thanks Heaps Mark. hope you feel better and well soon
Cheers
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Rich (BB code):
With Range(Cells(33, Cells(33, 25).End(xlToLeft).Column)
Cells is made up of a row reference and a column reference.
The 33 is straightforward and is the first row with the formula.

Rich (BB code):
Cells(33, 25).End(xlToLeft).Column
The 33 is as already stated is the first row with the the formula, the 25 is the last column that we are going to be filling to so Y33.

The End(xlToLeft) is making the code look from Y33 to the left until it finds some data i.e. the column with the formulas. So on the first run it will find N33. The .Column just returns the column number.
So at the moment we have Range(Cells(33, 14),


Rich (BB code):
, Columns(Cells(33, 25).End(xlToLeft).Column).Find("*", , xlFormulas, , xlByRows, xlPrevious))
The Cells(33, 25).End(xlToLeft).Column is the same as above and returns 14 on the first run (and I could have/should have assigned it to a variable).
So you now get Columns(14).Find("*", , xlFormulas, , xlByRows, xlPrevious) The Find part is finding the last used cell in column 14

So you now have Range(
First cell with formula in Column N, Last cell with formula in column N)

Rich (BB code):
.Offset(, 1).Formula = .Formula
.Offset(, 1) Refers to the next column and so it is just putting the formula in the next column.

Rich (BB code):
.Value = .Value
Is just converting the original column with the formula to Values
 

malikumair56

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

ADVERTISEMENT

Thanks Mate you are a legend:)
This helps alot
Cheers
 

malikumair56

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

ADVERTISEMENT

HI Mark, hope you are well, me again with help, please.
I have added some of the new columns so change some of the number from the code, also as a safetly added a message ask before code to ensure its not mistakenly click.
NOw i need help as now the VBA is not exactly workly which i believe due to data already exist in the cells.
The formula is now in Column P, which needs to be dragged to next column Q, as formula which i understood from your explanation picks where until it has data now data is in all the cells.
As for example Jan is actual but in Feb its forecast number, when formula is dragged it will replace the data in column Q with the formulas so actual number of Feb is populated.
IS this something you can help please?

1611819783867.png



The VBA is have in there is below
Sub Rollover()
response = MsgBox("Are You Sure You Are Rolling Over?", vbYesNo)

If response = vbNo Then
MsgBox ("VBA Ending")
Exit Sub

End If
With Range(Cells(34, Cells(34, 27).End(xlToLeft).Column), Columns(Cells(34, 27).End(xlToLeft).Column).Find("*", , xlFormulas, , xlByRows, xlPrevious))
.Offset(, 1).Formula = .Formula
.Value = .Value
End With

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


Thanks a ton
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What row number is the row with the words "Actual" and "Forecast" on? (it would be a lot easier if you could use the boards XL2BB app to post screenshots rather than post partial images as it avoids such questions)
 

malikumair56

New Member
Joined
Jan 22, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
HI Mark, Thanmks your reply.
I tried using XL2BB but i think its my company laptop so wont be able to use:(


1611821712743.png
 

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 (please note that the code will error if the word Actual isn't within the range on row 5).

VBA Code:
Sub Rollover()
    Dim StartCol As Long, StartCol2 As Long, response As String

    response = MsgBox("Are You Sure You Are Rolling Over?", vbYesNo)

    If response = vbNo Then
        MsgBox ("VBA Ending")
        Exit Sub
    End If
    
    StartCol = Range("P5:AA5").Find("Actual", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
    With Range(Cells(34, StartCol), Columns(StartCol).Find("*", , xlFormulas, , xlByRows, xlPrevious))
        .Offset(, 1).Formula = .Formula
        .Value = .Value
    End With
    
    StartCol2 = Range("AD5:A05").Find("Actual", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
    With Range(Cells(34, StartCol2), Columns(StartCol2).Find("*", , xlFormulas, , xlByRows, xlPrevious))
        .Offset(, 1).Formula = .Formula
        .Value = .Value
    End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,106
Messages
5,622,766
Members
415,926
Latest member
jerrynababa

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