dslreports logo
 
    All Forums Hot Topics Gallery
spc

spacer

Search Topic:
uniqs
910
share rss forum feed

OldAuditor

join:2007-03-24
Oklahoma City, OK

[Excel] VBA question

I use a program called OmniPage, which converts various formats to, in this case, Excel 2010. The only real problem is that it puts each sheet of the document in a separate sheet of the workbook. Obviously, consolidating a 100 page workbook into one sheet is very time-consuming.

I tried to automate it through a macro:

Sub Macro1()
'
' Macro1 Macro
' To consolidate sheets from Omnipage
'
Sheets("Sheet2").Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("Sheet1").Select
Range("A10").Select
Selection.End(xlDown).Select
Range("A56").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
End Sub

This works fine for the second sheet.

The problem is that it references “Sheet2”. Once the sheet-delete code runs, the reference becomes invalid.

I’m sure this can be done through incrementing a variable, but I don’t know how to do it.

Any help would be appreciated.

TIA

Old


H2OuUp2
Happy to be here
Premium
join:2002-03-15
Oklahoma City, OK
Remove this: ActiveWindow.SelectedSheets.Delete

You could use some code like this.

For Each sh In Sheets
sh.Select
If sh.Name "Sheet1" Then 'your code goes here
Next sh

For Each sh In Sheets
sh.Select
If sh.Name "Sheet1" Then ActiveWindow.SelectedSheets.Delete
Next sh
--
He is no fool who gives up what he cannot keep, to gain what he cannot lose. - Jim Elliot

OldAuditor

join:2007-03-24
Oklahoma City, OK
I got a reply from 2kmaro from a direct email. If he agrees, I will post the code.

OldAuditor


H2OuUp2
Happy to be here
Premium
join:2002-03-15
Oklahoma City, OK
reply to OldAuditor
No problem, mine was just getting you in the right direction


2kmaro
Think
Premium,ExMod 1 BC
join:2000-07-11
ColossalCave
kudos:1

1 edit
reply to OldAuditor
Since some may want to see the code, here it is, hopefully well commented for your understanding. There is a very nice "Find last used row number, column number or cell address" function by Ron de Bruin, a fellow Microsoft Excel MVP, that I tossed into the mix. It can be used in other VBA projects where you may need to continually find the 'last' on a sheet.

The code: