Excel Scripting - Xtreme Visual Basic Talk

10-17-2005, 12:15 PM

gbargsley

Centurion

Join Date: Jul 2003

Posts: 119

Excel Scripting

Hello all...

I am not sure how to accomplish this task, so I thought I would ask.

I have a export of a large database. The export is an Excel file that has about 100 columns. Well I only need about 10 of those columns.

So what I am doing now is finding the column then cutting and pasting it in to a blank worksheet. Then when I am done I change the column headings and delete the original worksheet and I am done.

What I am wanting to find out is if there is a way to write some code in Excel that will create a blank worksheet, then go to the main document find column 10 cut the whole column and past to column 1 in new worksheet. Then go back to main worksheet and find column 20 and cut and paste, etc, etc.

Then when done rename column 1 / row 1 to Site, column 2, row 1 to School, etc, etc...

Then finally delete the original worksheet.

Any ideas or suggestions would be great.

Thanks
Garry B

10-17-2005, 12:47 PM

shettydatta shettydatta is offline

Centurion

Join Date: Aug 2005

Posts: 158

Garry,
You can record a Macro with the Steps you want. This will create a VBA Subroutine that can then be run when you need to perform the steps.

Or
You can code in VBA the Steps you need.

I think recording the macro might be the easy thing to do.

For doing that Using a New Workbook, Goto Tools -> Macro -> Record. Starting doing your steps i.e
1) select the Data file to be opened.
2) Select each Column you want and copy paste it to new sheet.
3) Delete the Original Sheet.

Once complete, go back to Tools -> Macro and Stop Recording.

you can see the Steps by doing ALT + F11, this will open the VBE, you should be able to see the code and play with it.

HTH
Datta.

10-18-2005, 01:42 PM

Malik641

Freshman

Join Date: Oct 2005

Posts: 29

Hey Garry

See if this is what you were looking for:

Code:

Option Explicit

Sub CopyPaste()
Application.ScreenUpdating = False
Dim iLastRow    As Long
Dim i           As Long
Dim j           As Long
Dim WS          As Worksheet

Set WS = ThisWorkbook.Worksheets.Add
WS.Name = "New Data Sheet"
j = 1

For i = 10 To 100 Step 10
    With Workbooks("Book2.xls").Sheets("Sheet1").Activate
        iLastRow = Cells(Rows.Count, i).End(xlUp).Row
        Range(Cells(1, i), Cells(iLastRow, i)).Copy
    End With
    
    With Workbooks("Book1.xls").Sheets(WS.Name).Activate
        Sheets(WS.Name).Range(Cells(1, j), Cells(iLastRow, j)).PasteSpecial xlAll
    End With
    j = j + 1
Next
Application.ScreenUpdating = True
End Sub

Just go into the VBE by pressing ALT+F11, right-click on VBAProject(Book1) (the book what will have the new sheet) and select Insert Module and paste this in there.

Be sure to change "Book1.xls" and "Book2.xls" accordingly (Book2 has the data to be copied, BTW) and make sure they're in the same folder when you do this.