Find Column without Changing Refs in vb
 

I have the following macro that fillsdown specific columns with formats,
validations, etc. I had to use seperate techniques because I couln not figure
out how to copy an entire row's formats, validations, and formulas without
carring other columns in that rows VALUE data with it (columns without
formats, validations or formulas - data only). This code works good, however,
I am still adding new columns as I see fit. Of course then I have to change
the references in the code when needed. One is there a way to supress columns
with values and copy the row's formats, validations, and formulas only. If
not, is there a way I can scan my column headers in row 1 in each column to
locate the header of choice and then apply my code to the column. This would
prevent having to change the references each time I move a column. Thanks!

Dim lastRow As Long
lastRow = Range("AD65536").End(xlUp).Row
Range("A3:B3").AutoFill Destination:=Range("A3:B" & lastRow)
Range("C3:E3").AutoFill Destination:=Range("C3:E" & lastRow),
Type:=xlFillFormats
Range("I3").Copy
Range("I3:I" & lastRow).PasteSpecial Paste:=xlPasteValidation
Range("K3:L3").AutoFill Destination:=Range("K3:L" & lastRow)
Range("M3:O3").Copy
Range("M3:O" & lastRow).PasteSpecial Paste:=xlPasteValidation

Range("P3:Q3").AutoFill Destination:=Range("P3:Q" & lastRow)
Range("R3:T3").Copy
Range("R3:T" & lastRow).PasteSpecial Paste:=xlPasteValidation

'Range("A3:A" & lastRow) = Range("A3").FormulaArray
Application.ScreenUpdating = True
Sheets("Tracker").Range("A2").Select



End Sub

Find Column without Changing Refs in vb
 

I'm a bit confused as to what you want to do? You seem to be copying ranges
in row 3 down to the last row? Is this correct? How do you determine which
columns to copy? You could probably create a sub that copies it for one
column and call that repeatedly, but I'm not sure how you determine which
column to work on.

Barb Reinhardt



"Kenny" wrote:

I have the following macro that fillsdown specific columns with formats,
validations, etc. I had to use seperate techniques because I couln not figure
out how to copy an entire row's formats, validations, and formulas without
carring other columns in that rows VALUE data with it (columns without
formats, validations or formulas - data only). This code works good, however,
I am still adding new columns as I see fit. Of course then I have to change
the references in the code when needed. One is there a way to supress columns
with values and copy the row's formats, validations, and formulas only. If
not, is there a way I can scan my column headers in row 1 in each column to
locate the header of choice and then apply my code to the column. This would
prevent having to change the references each time I move a column. Thanks!

Dim lastRow As Long
lastRow = Range("AD65536").End(xlUp).Row
Range("A3:B3").AutoFill Destination:=Range("A3:B" & lastRow)
Range("C3:E3").AutoFill Destination:=Range("C3:E" & lastRow),
Type:=xlFillFormats
Range("I3").Copy
Range("I3:I" & lastRow).PasteSpecial Paste:=xlPasteValidation
Range("K3:L3").AutoFill Destination:=Range("K3:L" & lastRow)
Range("M3:O3").Copy
Range("M3:O" & lastRow).PasteSpecial Paste:=xlPasteValidation

Range("P3:Q3").AutoFill Destination:=Range("P3:Q" & lastRow)
Range("R3:T3").Copy
Range("R3:T" & lastRow).PasteSpecial Paste:=xlPasteValidation

'Range("A3:A" & lastRow) = Range("A3").FormulaArray
Application.ScreenUpdating = True
Sheets("Tracker").Range("A2").Select



End Sub

Find Column without Changing Refs in vb
 

This sub copies the seperate things that i need for these columns; however,
everytime I add a new column I have to make changes to the referenced columns
in this code. My question is how can I make the code follow the columns as I
make changes to add columns to the worksheet. Such as have the code to search
my header in row 1 & 2 to find the right column and then apply the code to
the right column?

"Barb Reinhardt" wrote:

I'm a bit confused as to what you want to do? You seem to be copying ranges
in row 3 down to the last row? Is this correct? How do you determine which
columns to copy? You could probably create a sub that copies it for one
column and call that repeatedly, but I'm not sure how you determine which
column to work on.

Barb Reinhardt



"Kenny" wrote:

I have the following macro that fillsdown specific columns with formats,
validations, etc. I had to use seperate techniques because I couln not figure
out how to copy an entire row's formats, validations, and formulas without
carring other columns in that rows VALUE data with it (columns without
formats, validations or formulas - data only). This code works good, however,
I am still adding new columns as I see fit. Of course then I have to change
the references in the code when needed. One is there a way to supress columns
with values and copy the row's formats, validations, and formulas only. If
not, is there a way I can scan my column headers in row 1 in each column to
locate the header of choice and then apply my code to the column. This would
prevent having to change the references each time I move a column. Thanks!

Dim lastRow As Long
lastRow = Range("AD65536").End(xlUp).Row
Range("A3:B3").AutoFill Destination:=Range("A3:B" & lastRow)
Range("C3:E3").AutoFill Destination:=Range("C3:E" & lastRow),
Type:=xlFillFormats
Range("I3").Copy
Range("I3:I" & lastRow).PasteSpecial Paste:=xlPasteValidation
Range("K3:L3").AutoFill Destination:=Range("K3:L" & lastRow)
Range("M3:O3").Copy
Range("M3:O" & lastRow).PasteSpecial Paste:=xlPasteValidation

Range("P3:Q3").AutoFill Destination:=Range("P3:Q" & lastRow)
Range("R3:T3").Copy
Range("R3:T" & lastRow).PasteSpecial Paste:=xlPasteValidation

'Range("A3:A" & lastRow) = Range("A3").FormulaArray
Application.ScreenUpdating = True
Sheets("Tracker").Range("A2").Select



End Sub