Is this really possible?

[reena]

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.

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[reena]

Hi,

I have made some changes in the above given code. Now this is working fine. But there are two things I want in this.
1. Currently this is copying only one column in the Output tab. I want 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
Next