Is this really possible?
 

I want to create macro which will allow to extract whole columns from
"input Excel file (File 1)" and copy to "Output Excel file". This macro
will read a 'Setup' TAB in Excel which lists the columns to be extracted
in the order required.
This list also gives the possibility to specify, optionally, for each
column, a New Title (otherwise keep the old title), column width and
title attributes (mainly font size).
Finally, this list can give the font size of the column (Outside title)

Is this really possible?
 

Is it possible - certainly.

The devil is in the details of course.

Dim sh as Worksheet, shi as Worksheet
Dim sho as Worksheet
Dim setrng as Range, cell as Range
Dim i as Long
set sh = worksheets("SetUp")
set shi = Workbooks("Input.xls").Worksheets(1)
set sho = Workbooks("Output.xls").worksheets(1)
set setrng = sh.Range("A1").currentRegion.Rows(1).Cells
i = 1
for each cell in rng
shi.columns(cell.Value).copy _
Destination:=sho.Columns(i)
sho.columns(i).columnWidth = cell.offset(1,0)
if not isempty(cell.offset(2,0)) then
sho.Cells(1,i).value = cell.offset(2,0)
end if
if not isempty(cell.offset(3,0)) then
sho.Cells(1,i).Font.Size = cell.offset(3,0).Value
end if
i = i + 1
Next

--
Regards,
Tom Ogilvy


"reena" wrote in
message ...

I want to create macro which will allow to extract whole columns from
"input Excel file (File 1)" and copy to "Output Excel file". This macro
will read a 'Setup' TAB in Excel which lists the columns to be extracted
in the order required.
This list also gives the possibility to specify, optionally, for each
column, a New Title (otherwise keep the old title), column width and
title attributes (mainly font size).
Finally, this list can give the font size of the column (Outside title)
.


Is this really possible? I am completely blank.. Someone please help me
for this.


--
reena
------------------------------------------------------------------------
reena's Profile:
http://www.excelforum.com/member.php...o&userid=30440
View this thread: http://www.excelforum.com/showthread...hreadid=501028

Is this really possible?
 

Hi,

I have made some changes in the above given code. Now this is workin
fine. But there are two things I want in this.
1. Currently this is copying only one column in the Output tab. I wan
to copy all the columns which are specified in the SetUp tab
2. Creating a new workbook for output.

How to do this? I have given the modified code for referance.

Dim sh As Worksheet
Dim shi As Worksheet
Dim sho As Worksheet
Dim setrng As Range
Dim cell As Range
Dim i As Long
Set sh = Worksheets("SetUp")
Set shi = Workbooks("Input.xls").Worksheets(1)
Set sho = Worksheets("Output")
Set setrng = sh.Range("A1").CurrentRegion.Rows(1).Cells
i = 1
For Each cell In setrng
shi.Columns(cell.Value).Copy Destination:=sho.Columns(i)
'sho.Columns(i).ColumnWidth = cell.Offset(1, 0)
If Not IsEmpty(cell.Offset(2, 0)) Then
sho.Cells(1, i).Value = cell.Offset(2, 0)
End If
If Not IsEmpty(cell.Offset(3, 0)) Then
sho.Cells(1, i).Font.Size = cell.Offset(3, 0).Value
End If
i = i + 1
Nex

--
reen
-----------------------------------------------------------------------
reena's Profile: http://www.excelforum.com/member.php...fo&userid=3044
View this thread: http://www.excelforum.com/showthread.php?threadid=50102