Programming a Macro to operate over multiple sheets or workbook

[AW101]

Hi all,

I'm trying to set up a way to format column widths and row heights in a macro (so that it can read a value from a cell and format a column to that width).
I've used the code below whcih can be repeated for the number of rows and columns desired below

Worksheets("Sheet1").Range("d1").ColumnWidth = Range("d1")

However, I want to be able to do this on a workbook that could potentially have 40+ sheets. Is there a way of applying this code to operate over the whole workbook rather than just one sheet at a time?

Thanks for any suggestions,

 

[mmmarks]

try something like this

dim sh as worksheet
for each sh in sheets
  sh.Range("d1").ColumnWidth = Range("d1")
Next sh

 

[rs2k]

Have a look at this;

http://www.mrexcel.com/archive/VBA/3889.html

HTH
Colin

 

[AW101]

Thanks for the feedback Colin,

I applied this code, which worked in applying the values from a single worksheet to format the entire workbook. However, for the tables I'm setting up the format will vary from sheet to sheet. As such I'm looking for some code that would take the values from 'sheet 1' and apply them to the formatting for 'sheet 1', then this process repeated for 'sheet2' etc.

Is this possible with VB?

I have no background of VB so I'm cut and pasting and experimenting to try and get a solution, so apologies if I'm missing something simple.

 

[rs2k]

Can you post your code?

Just guessing here, but I think you may need something along the lines of what mmmarks suggested, but add the ws.select statement to it;

dim ws as worksheet
For each ws in Worksheets
ws.select
'code to run on all worksheets is here
Next ws

HTH
Colin

 

[AW101]

Hi Colin,

Code I'm currently using is:

Sub ColumnWidth()

Worksheets("2.3").Range("a1").ColumnWidth = Range("a1")
Worksheets("2.3").Range("b1").ColumnWidth = Range("b1")
Worksheets("2.3").Range("c1").ColumnWidth = Range("c1")

Worksheets("2.3").Range("a2").RowHeight = Range("a2")
Worksheets("2.3").Range("a3").RowHeight = Range("a3")
Worksheets("2.3").Range("a4").RowHeight = Range("a4")

End Sub

I tried removing "Worksheets("2.3")." and starting the code from "Range("a1") - but this wasn't working.

Thanks,
Alex

 

[rs2k]

Hi Alex,

Try this, it uses the values from a1,b1 & c1 AND a2,a3 & a4 on each sheet it is running the code on;

Sub ColTest()
Dim ws As Worksheet
'Stop the screen flickering
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Select
'code to run on all worksheets is here
ws.Range("a1").ColumnWidth = Range("a1")
ws.Range("b1").ColumnWidth = Range("b1")
ws.Range("c1").ColumnWidth = Range("c1")
ws.Range("a2").RowHeight = Range("a2")
ws.Range("a3").RowHeight = Range("a3")
ws.Range("a4").RowHeight = Range("a4")
Next ws
Application.ScreenUpdating = True
End Sub

HTH
Colin

 

[AW101]

Hi Colin,

Brilliant - it worked! - Thanks Colin and mm for your suggestions!!
Alex