Macro to address cells in the next column to selection

Hi All,

Newbie to this site.

I must say that I've always found help forums to be amazing places, and I never fail to get useful advice. To this end I have TWO MACRO QUESTIONS:

QUESTION 1.
I work on a cruise ship as a musician and recently helped out the admin office with a simple macro used on their daily planner. Effectively, we start with a blank timesheet where each cell=quarter hour and all cells are shaded grey. The macro merges the selected cells (eg 4 contiguous cells at A1, A2, A3 and A4; representing an hour to be worked by my band), makes them all white, removes the dividing lines, centres the text and applies a thick black border around the selection. I used MacroRecorder to create this, as my programming knowledge stopped at ANSI Basic and a bit of COBOL!

There is a further requirement which I hope you can help me with. For each cell selected and, errr, macro-ed (?), there needs to be a corresponding value of 0.25 put in the cell to the immediate right. This is for a head office requirement where hours worked (in quarter hour intervals, hence 0.25) need to be totaled in a separate column. I created another simple macro whereby all cells selected are filled with the correct value, but there must be some way to do this in ONE macro. Is there any way to reference the top cell of the selection (in my example A1) and the bottom cell of the selection (in my example A4, but this could be A5, A6……… Ann for longer work periods) and then fill ALL cells to the right (ie within the corresponding B column cells) with the value 0.25?

I’m sure this has something to do with Relative cell addressing, ranges and for…next loops, but as I’m on a ship I have limited access to the internet and therefore cannot trawl through all the Visual Basic sites until I find the answer. I’d appreciate someone completing the macro for me and tidying up the unnecessary lines which I’m sure are there! Also, if possible, could you explain in semi-layman’s terms why you’ve done what you’ve done, so I could try to understand the logic behind the coding.

Here’s the macro as recorded by MacroRecorder:

Please Login or Register  to view this content.

QUESTION 2.
I need to be able to copy and paste a range of cells (containing pictures and heavily formatted text) from one spreadsheet into another. Lets assume this is in a worksheet called ‘SOURCESHEET’ and the range is A1:E1 (ie 5 cells across, 1 cell deep.) I’d like to do this by creating a macro which selects a range of cells of the same area based on the currently selected cell (ie if I select A3 then the macro needs to know it’s working in the area A3:E3), resizes the depth of these cells to the same depth as the cells in SOURCESHEET, then inserts the picture/text cells at that point. Also, parts of A1:E1 have been merged, so the macro would have to format the destination area accordingly before the pictures/text can be inserted (I say this as I assume that there will be errors if I try to paste a 3-cell source across a 5-cell range). The pictures and text would need to be embedded into DESTINATIONSHEET, as the SOURCESHEET is held locally wheras DESTINATIONSHEET will be a network document that needs to be printed at various locations.

Am I right in assuming that there is a ‘copy source format’ function which will take care of the merging/re-sizing requirements, or is there another way round this?


Well, if you’ve read this far then hopefully you’re up for the challenge, so I thank you in advance and look forward to your reply.

Regards,

Miles_muso,
Musician on P&O Cruise ship
And reluctant ‘expert’ on computers (ie the only one who even knows what a macro is!)

miles_muso

Welcome to Exceltip forum

Please take a couple of minutes and read the Forum Rules then edit your thread title by following the instructions in the rules (Rule 1)

and wrap your VBA code (Rule 3)

Thanks for fixing your thread errors


For Question 1

Add this command after the last End With & before the End Sub of your macro

Please Login or Register  to view this content.