Compressing multiple sheets onto one...
Posted by Dave on January 15, 2002 3:02 AM
i have an output which has numerous sheets, all in the same format, which i'd like to move onto one sheet.
each sheet can have any number of rows.
I expect this is quite simple but, i can't figure out the details.
Any hints on which functions to use (in excel vba) greatfully recieved!
Dave
Posted by Jacob on January 15, 2002 2:13 PM
Hi
Lets say you want to put all sheet data on a new sheet called "NewSheet" each page below the other on the new page with one blank row seperator. Modify as needed
Sub CombineItAll()
Dim x as Integer
Dim c as Range
Dim LastRow as Integer
Dim LastColumn as Integer
On error resume next
For x = 1 to sheets.count
If sheets(x).name = "NewSheet" Then
Else
sheets(x).activate
set c = .Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
LastRow = c.row
set c = .Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns)
LastColumn = c.column
Range("A1:" & Cells(LastRow, LastColumn).Address).Select
selection.copy
Sheets("NewSheet").activate
set c = .Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
if c is nothing then
range("A1").select
else
range(c.address).select
activesheet.paste
next x
End Sub
HTH
Jacob
Posted by Dave on January 17, 2002 2:04 AM
I get errors...
Errr...
i'm getting "invalid or unqualified reference" errors at the first ".find" line.
i tried adding "application" to the front of the .find (which may or may not be right...) but then i get "block if without end if" errors at the "next i" line.
Any ideas?
Cheers,
Dave
Posted by Dave on January 17, 2002 2:12 AM
PS i'm using excel 97 if that helps... (nt)
