Need Help - Small Macro

[comeonoverto]

Hi,

Unfortunatly, I am not very well versed in VBA.

Here is what I need to be used within Excel 2007.

** 1 spreadheet has 30 worksheets.

1. I need a macro that will automatically go to the first worksheet,
identify the data and put it into a new worksheet called "FINAL".

2. Then it is to go to the second worksheet and do the same as above,
but place the data in the next A?? cell directly below where the data
stopped from worksheet 1.

3. The same applies to worksheet # 3 all teh way to worksheet # 30.

- Take note that each of the 30 worksheets has it's own name and can
change.
- Take note that some of the 30 worksheets may not contain any data at
all.

I think that's it.

Thanks in adanvce.

 

[Bob Phillips]

Public Sub Test()
Dim sh As Worksheet
Dim newSh As Worksheet
Dim i As Long
Dim NextRow As Long

NextRow = 1
Set newSh = Worksheets.Add(after:=Worksheets(Worksheets.Count))
newSh.Name = "FINAL"
For Each sh In Worksheets
If sh.Name <> "FINAL" Then
sh.UsedRange.Copy newSh.Cells(NextRow, "A")
With newSh.UsedRange
NextRow = .Row + .Rows.Count + 1
End With
End If
Next sh
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Otto Moehrbach]

You don't provide information about the data to be copied except "identify
the data", so this macro does not include any copy/paste. This macro will
loop through all the sheets, skipping the "Final" sheet. The destination
cell (Dest) is updated with every loop (sheet) to be the next empty cell in
Column A. Post back if you need more. HTH Otto
Sub CopySheets()
Dim ws As Worksheet
Dim Dest As Range
Set Dest = Sheets("Final").Range("A" & Rows.Count).End(xlUp).Offset(1)
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Final" Then
'Insert your code for what you want copied from each
sheet.
'Dest is the cell in the Final sheet in which to paste.
'This may need some work if you want to copy/paste more
than one row per sheet.
Set Dest = Sheets("Final").Range("A" &
Rows.Count).End(xlUp).Offset(1)
End With
Next ws
End Sub

 

[qcan]

I copied and pasted Bob's code and ran it. It works !! No issues. I'm
sorry Otto, I copied your code, but I assume I am missing something as
I can not get it to run properly. .

Thank you both !