Rolling forward some columns

Hi there,

Hope everyone is well. My issue is regarding automatic rolling forward of an excel spreadsheet I have.

What I would like to do is copy and paste columns N:P three rows along, when I record the macro this works fine and the formula references move along with it. However when I run the macro for a second time it does not roll forward the new far right columns. Rather N:P again.

I understand this is due to my formula fixing the column N:P when copying, it's just I'm unsure how to code it so that the formula chooses the colums on the far right rather than these fixed columns. I also want it so that the formulas within the cells I am copying also move along.

As I'm sure you can tell I'm very new to macros so any help would be greatly appreciated! Have attached the excel document with the VBA for reference.

Thank you!

Attachment 800402

Hi and welcome

Your attachment doesn't work. Click "Go Advanced" below your reply, find the "Manage Attachment" links and it should be straightforward from there.

Full instructions may be visible in a banner at the top of the page. I don't know if it's still there because I blocked it from showing on my system.

BSB

Just posted a reply in the thread! please let me know if it doesn't work

Your attachment isn't viewable. If you're uploading a sample workbook, try following the directions in the yellow banner above the thread titled: HOW TO ATTACH YOUR SAMPLE WORKBOOK

How about now guys?

Try replacing your current code with this:

Sub Prepayments_roll_forward()
    Range(Cells(3, 14), Cells(3, Cells(3, Columns.Count).End(xlToLeft).Column)).EntireColumn.Copy Range("Q1")
End Sub

Does that do what you want?

BSB

That is absolutely what I want! Thank you so much I really do appreciate this! I've signed up to a VBA course so hopefully I won't need to post here too often lol.

Quick question. How long do you think it takes to get to a good working proficiency with VBA?

Glad I could help

As for how long to learn... Depends on what you mean by proficient, depends on how quick a learner you are, how naturally technical your brain is, many things.
I found it easier to learn VBA by using it in the real world rather than the classroom. I teach people to use it as part of my job and I reckon it could be picked up in a few weeks if you put your mind to it.

It's sort of a never ending learning curve though and you'll find you're always refining the way you do things with it, so you never really stop learning.

BSB