On May 27, 8:21*pm, Derek Johansen
wrote:
Bernie,

What I am doing is breaking apart an imported spreadsheet based on the
contents of a certain column. *I'm creating a new workbook for each different
ID in the column, and using a loop then going through and copying, pasting,
and deleting specific rows into the new workbooks. *Before I do the copy any
pasting however, I am pasting the original Column Headers from the first
workbook into the new workbooks. *I don't have the code at home, but I can
post it tomorrow for you. *

Essentially, the spreadsheet I get raw automatically adjusts column widths
on import, and I would like to be able to transfer these widths into the new
workbook i create. *Is this possible without having to specify the width of
each indivudual column? (Code to come tomorrow if needed)



"Bernie Deitrick" wrote:
Derek,

Column width is a property of a column rather than a range, so you can set
it explicitly:

Worksheets("Sheet1").Columns("D:D").ColumnWidth =
Worksheets("Sheet2").Columns("D:D").ColumnWidth

Help beyond that would depend on what your code actually is....

Width is also part of the formatting of the entire column, so if you copy
the entire column / paste entire column you will get the width.

HTH,
Bernie
MS Excel MVP

"Derek Johansen" wrote in message
...
You sir, are the man! Thank you very much! *Since you seem to know your
stuff, I have one more question while I have your attention:

I am using the following commands to paste data into a new worksheet:
Selection.PasteSpecial Paste:=xlValues
Selection.PasteSpecial Paste:=xlFormats

But my new worksheet is loosing the column widths. *I would have thought
that would have been taken care of with "xlFormats" but apparently that is
incorrect. *Any way to past columns keeping their same width, or would i
have
to set each column individually?

"Don Guillett" wrote:

Sub findColumnName()
Dim mc As Long
Dim lr As Long
Dim c As Range
Dim firstaddress
mc = Rows(1).Find(What:="Name", LookIn:=xlValues, _
LookAt:=xlwhole, SearchOrder:=xlBycolumns, _
SearchDirection:=xlNext, MatchCase:=False).Column
'MsgBox mc
lr = Cells(Rows.Count, mc).End(xlUp).Row

With Range(Cells(1, mc), Cells(lr, mc))
* *Set c = .Find(What:="BCI", LookIn:=xlValues, _
* * LookAt:=xlPart, SearchOrder:=xlByRows, _
* * SearchDirection:=xlNext, MatchCase:=False)
* * If Not c Is Nothing Then
* * * * firstaddress = c.Address
* * * * Do
* * * * c.Offset(, 1).Value = "SOEWP"
* * * * c.Offset(, 8).Value = "EM"
* *Set c = .FindNext(c)
* * * * Loop While Not c Is Nothing And c.Address < firstaddress
* * End If
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Derek Johansen" wrote in
message
...
Mr. Guillett:

Thank you very much for your help, that works as desired, now the only
thing
I would like to change, is instead of using column "b" i would like to
use
a
variable. *Because the spreadsheet is not always formatted as desired,
I
have
my macro check the headings. *when it finds the column headed "Name"
(USUALLY
B, but not always) it sets a variable "name_column." *I would like to
use
this variable instead of the letter B if at all possible. *Here is the
code i
use to establish the variable:

Rows(1).Select
Selection.Find(What:="Name", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
name_column = ActiveCell.Column

"Don Guillett" wrote:

I would do it this way (If Not c Is Nothing Then )to avoid looking at
all
rows and avoid your stated problem

Sub betterfincbci()
With Range("b1:b" & Cells(Rows.Count, "b").End(xlUp).Row)

* Set c = .Find(What:="BCI", LookIn:=xlValues, _
* * LookAt:=xlPart, SearchOrder:=xlByRows, _
* * SearchDirection:=xlNext, MatchCase:=False)

* * If Not c Is Nothing Then
* * * * firstAddress = c.Address
* * * * Do
* * * * c.Offset(, 1).Value = "SOEWP"
* * * * c.Offset(, 8).Value = "EM"
* *Set c = .FindNext(c)
* * * * Loop While Not c Is Nothing And c.Address < firstAddress
* * End If

End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Derek Johansen" wrote in
message
...
I am using the following code to search through column B for all
cells
containing "BCI" and when it finds them it replaces columns C and J
with
certain things. *This works fine, until BCI does not appear anywhere
in
column B. *At this point I get a run-time error. *What I would like
to
do
is
say:
If "BCI" is part of cell in column B then fill columns c and j with
specific
criteria.

Here is the code I'm working with:

Columns("B").Select
For k = 2 To Cells(Rows.Count, "b").End(xlUp).Row
* *Selection.Find(What:="BCI", After:=ActiveCell,
LookIn:=xlFormulas, _
* *LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
* *MatchCase:=False, SearchFormat:=False).Activate
* *ActiveCell.Offset(0, 1).Value = "SOEWP"
* *ActiveCell.Offset(0, 8).Value = "EM"
Next

any help will be MUCH appreciated!- Hide quoted text -

- Show quoted text -

Derek,

..PasteSpecial has a "Column Widths" option (Excel 2003: Copy the cells
and then execute Alt + e + s + w; Excel 2007: Copy the cells and then
execute Alt + h + v + s + w -- as a side note, Excel 2003 hotkeys will
also work for this operation in Excel 2007).

Selection.PasteSpecial Paste:=xlPasteColumnWidths

Best,

Matthew Herbert