Create a macro which takes a column name and width and sets the column width to what it should be

  • Thread starter Thread starter Ag
  • Start date Start date
A

Ag

I want to create a macro whcich can be calles from a different sheet
and would change the column with to the parameter passed for the
column for whcich it is pased.The column name and width may be
mentioned in the second sheet from whcich they are read by the macro.

Aj
 
Enter the cell address in one cell and the width to the cell to the right.
Make the active cell the left of the two cells. for example below make cell
E1 the active cell when calling my macro below

E1 F1
A1 2

The above will make cell A1 columnwidth 2
For a different sheet

E1 F1
sheet2!A1 2


Then use this macro

Sub changewidth()

CellAddress = ActiveCell.Text
Cellwidth = ActiveCell.Offset(0, 1).Value

Range(CellAddress).ColumnWidth = Cellwidth

End Sub
 
Enter the cell address in one cell and the width to the cell to the right.
Make the active cell the left of the two cells. for example below make cell
E1 the active cell when calling my macro below

E1 F1
A1 2

The above will make cell A1 columnwidth 2
For a different sheet

E1 F1
sheet2!A1 2

Then use this macro

Sub changewidth()

CellAddress = ActiveCell.Text
Cellwidth = ActiveCell.Offset(0, 1).Value

Range(CellAddress).ColumnWidth = Cellwidth

End Sub






- Show quoted text -
Click to expand...

Great this works,
Now if i want to extend this such that
i want to have first column as sheet name
ie
1.csv E1 F1
1.csv A1 2
2.csv E1 F1
Click to expand...
2.CSV A1 2
so that I can read each sheet and colun name so that the value of each
sheet and column name is null?
What would be the best way of doing this.Thanks in anticipation.

Ajay
 
Great this works,
Now if i want to extend this such that
i want to have first column as sheet name
ie


2.CSV A1 2
so that I can read each sheet and colun name so that the value of each
sheet and column name is null?
What would be the best way of doing this.Thanks in anticipation.

Ajay- Hide quoted text -

- Show quoted text -
Click to expand...

I get the error method range of object global failed.Am I doing
something wrong?

Ajay
 
Did you remember to put in the '!' character between the sheetname and the
cell address?

Sub changewidth()

CellSheet = ActiveCell.Text
CellAddress = ActiveCell.Offset(0, 1).Text
Cellwidth = ActiveCell.Offset(0, 2).Value

Range(CellSheet & "!" & _
CellAddress).ColumnWidth = Cellwidth

End Sub
 
Back
Top