selecting specific columns with data and put it in a new sheet

  • Thread starter Thread starter A. Karatas
  • Start date Start date
A

A. Karatas

Hi,

A database is provided to me each month with approx 20.000 lines and
approx 140 columns. How could I extract (with a macro) certain columns
with their underlying data in them. In mind I had a sheet called
assumptions with in column A a selection of the columnheaders wich the
macro chooses from and puts it in a sheet called OLAP from column A
and further on.

Thankx in advance
 
Hi
Your question is too vague and I doubt anyone will reply. Make your
question clearer.
e.g. "Certain Columns" - which columns? does it change?
"underlying data" - do you mean the data in the columns?
"the macro chooses" - do you need a form with a listbox? Are column
headers chosen in some other way?
"puts it in a sheet" - puts what in a sheet? Column headers, columns
of data? Something else?
"and further on" - ??????

see the problem?
regards
Paul
 
Ok, I will try to describe it clearer :)
I am provided with a database which is approx 20000 lines and 140
columns. I don't need all the columns to produce a report on a monthly
basis. The last couple of months I build a excel document with various
pivottables wich selects data from certain fixed columns. Headquarters
decided to change the column layout of the database which effects the
pivottables I produce. To overcome changes in the future I want to
sort out the handed out database with the columns I need before I put
it into my mastersheet for the pivottables to do their job.
So the plan is that the macro looks in a range (in row format) on
sheet (for example) 'assumption' where the selected column names are
put in manually. It looks at cellvalue A1 and than tries to find this
column name in the database. When cellvalue is found in row 1 (of the
database) the macro than selects the complete column and copies it
into a new sheet in column A. By a loop it then looks for cellvalue A2
on sheet assumption and copies the found column in column B till the
end is reached of the searchrow in sheet assumption.

The idea is that instead of using the entire database, I am making a
pre selection of the columns needed. In this way the build
exceldocumnt is not effected by changes made by headquarters. With
the selection row in sheet assumption I will be flexible in choosing
rather than rebuilding the document again (pivottables etc).

Hopefully I am clearer now :)
 
I tried to put something together but the folowing doesn't work. Any
idea's why???


Sub CommandButton2_Click()

On Error GoTo ErrorHandler

'PG_Begin is the name for the selection A1: A10 and on sheet called
assumption

Application.Goto Reference:="PG_Begin"
Row = ActiveCell.Row

While ActiveSheet.Cells(Row, 1).Value <> ""
PG = ActiveSheet.Cells(Row, 1).Value


'the database called OLAP is activated and searced for
Sheets("olap").Select
Cells.Find(What:=PG, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Select
ActiveCell.EntireColumn.Select

ActiveCell.EntireColumn.Copy
ActiveSheet.Paste (Sheets("Previous"). _
Range("IA1").End(xlToLeft).Offset(, 1))

Sheets("Assumption").Select

Row = Row + 1

Wend

ErrorHandler:
Application.ScreenUpdating = True
MsgBox (Err.Description)

End Sub
 
Back
Top