Deepak has attended:
Excel VBA Introduction course
Macros
Can you record a macro where it moves to the next cell across everytime you hit the macro button
RE: Macros
Hi Deepak
You'll probably need to tell me more about the context of the question.
Here's a macro that moves from the current cell (active cell) one to the right and highlights a range of 6 cells.
Running the macro again highlights the next adjacent range.
Sub Macro1()
ActiveCell.Offset(0, 1).Range("A1:A6").Select
End Sub
Product Jan Feb Mar
P0001 5 19 7
P0002 15 12 8
P0003 15 17 11
P0004 20 1 14
P0005 20 18 2
P0006 1 14 18
for instance clicking cell B2 (the Jan value 5) and running the macro selects range C2:C7. Click again and D2:D7 is selected.
Let me know a bit more about your example. Thanks
Regards
Doug
STL
RE: Macros
Thank you for your reply.
Here is my example
Jan Feb Mar
100 200 300
There are formulas to calculate the 100 200 300 within the spreadsheet for Jan Feb Mar respectively
To include April you need to highlight the Mar cells then copy and paste one column across
This needs to be done every month to include a new column/new month
However doing a macro it is set on a fixed cell so if I was to receord a macro, excel would just copy and paste april everytime and not move on to the next column. What if I can record a macro where it will goto the next column everytime.
RE: Macros
Hi Deepak
Thanks for your example.
I've set up a similar one where there are monthly headings and 3 rows of data for Jan, Feb and Mar in Sheet1. The formulas link back to cells in Sheet2.
I'll try to upload the file but in case you can't open it here is the code: (Created by recording with Relative Record switched on). Where it adds "A1:A3" it means the block relative to the active cell which moves to the right each month.
Sub Macro1()
Range("A2").Select
Selection.End(xlToRight).Select
ActiveCell.Range("A1:A3").Select
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
Here is my data (hard to read on the post but can if you copy to Excel then use Text to Columns and tick space delimiter).
Sheet1
Jan Feb Mar Apr May Jun Jul
100 200 300
40 80 120
60 120 180
Sheet2
Jan Feb Mar Apr May Jun Jul
Sales 100 200 300 400 500 600
Costs 40 80 120 160 200 240
Profit 60 120 180 240 300 360
The important part is to select Use Relative REcord before recording the macro.
Hope that gets you a bit further.
Doug
STL
