Macros

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
583
Office Version
  1. 2013
Platform
  1. Windows
iI have a Workbook which has about 7 sheet in it. I have created a macro which reformats cells in the spreadsheet. The problem is the range is different in each sheet. Basically the macro either needs to be tailored for each spreadsheet or intelligent to detect what spreadsheet it is and apply the appropriate range.

Has anyone got a easy solution to my problem?

Can macro be attached to individual spreadsheet in a workbook?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
If there is a pattern to the different ranges, like the last ROW or something, then it would be a simple fix.

If it is always the same range, but a different range on each sheet that does not change then the Select Case structure and a For Next loop will do.

If at any one time the cells that get worked upon are different and their is no pattern or algorithm to the pattern of change, then no.

Give more information for a better answer!
 

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
583
Office Version
  1. 2013
Platform
  1. Windows
Yes the last row is different in each spreadsheet
 

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
583
Office Version
  1. 2013
Platform
  1. Windows
So the macro processes row I do not want it to
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Dim lngMyBotR&
Dim ws As Object

For Each ws In Worksheets
lngMyBotR = ws.Range("A65536").End(xlUp).Row

ws.Range(Cells(lngMyBotR, 1), Cells(ingMyBotR, 5)).Select

Selection."Here is where you put what you want to do to the last row on any sheet, columns A through E"

Next ws
 

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
583
Office Version
  1. 2013
Platform
  1. Windows
I think I have posed the question right i will try again i am reformatting range AD13;Ax13 to AD17:ax17 in one sheet but in the other it's Ad13;ax13 to ad52;ax52 so in the first sheet when the macro is run it formats cells that i do not want it to.

i hope i have made the question clearer
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
What is the actual range to be formatted on each sheet, knowing that each sheet will have different cells in the formmating range. If these change on any given sheet at any time once the code is set, how does it change?
 

Forum statistics

Threads
1,181,648
Messages
5,931,213
Members
436,784
Latest member
amuljono

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