Select specific range in last row with data

Hi everyone, macro noob here so please be gentle.

I tried searching for examples of what I need but kept getting search returns on finding last row or columns instead.

I am trying to get a macro to (assuming data starts on row 4 and currently last row with data is row 15):

1) Locate last row of cells containing values or formula for columns E:G (i.e. cells E15:G15)

2) Paste formula into next empty range (i.e. cells E16:G16)

3) Skip/ignore column H

4) Locate last row of cells containing values or formula for columns I:J (i.e. cells I15:J15)

5) Paste formula into next empty range (i.e. cells I16:J16)

6) Skip/ignore column K

7) Locate last row of cells containing values or formula for columns L:O (i.e. cells L15:O15)

8) Paste formula into next empty range (i.e. cells L16:O16)

I have multiple worksheets with similar needs but every sheet needs to be updated at a different time of the month. So looks likely that I need the macro to apply to ActiveSheet so that I can use the macro for any worksheet at any time depending on which worksheet I am working on.


Would appreciate any help on this. Thank you!

Paste formula into next empty range

I don't see any formulas in you file? This macro will copy everything from the row above. To copy in a specific way change "xlPasteAll" to "xlPasteFormulas" or "xlPasteValues".

Please Login or Register  to view this content.

Alf

This works perfectly! Thank you!

Glad to be of help.

Thanks for feed back and rep.

Alf

You're very welcome.

Sorry for the silly question. But what does the last command do (i.e. what is the purpose of having this)?

Application.CutCopyMode = False

Just so I learn properly. Thanks!!

Macros as well as the “normal” copy and paste operation do the copy/cut and paste in separate statements and use the clipboard as an intermediate buffer. "Application.CutCopyMode = False" clears the clipboard.

Without that line you will get the "annoying" marching "ants" around the last copied cell and the text bottom left "Select destination and press ENTER or chose Paste".

You may also get the warning "There is a large amount of information in the Clipbord ...' when you close the workbook with a large amount of data on the clipboard.

I've also had macros that stopped running because I managed to "flood" the clipboard buffer when doing a copy paste loop with a number of files.

Alf

Very clear. Thank you.