Copying and hiding issues...

M

mjack003

Hi,

I need to create a macro which will copy A5:A500 on all grouped sheet
and paste them onto column A in a hidden worksheet "Shipped". The har
part for me is one, I need to omit the blank spaces, and two, thi
macro will be ran numerous times so I need it to continue from the las
cell containing data the next time it is ran. Awaiting some muc
needed help.

Best Regards,
Mjac
 
D

Dave Peterson

Something like this???
Option Explicit
Sub testme01()

Dim ShippedWks As Worksheet
Dim wks As Worksheet
Dim DestCell As Range

Set ShippedWks = Worksheets("shipped")

With ShippedWks
For Each wks In ActiveWindow.SelectedSheets
If wks.Name = ShippedWks.Name Then
'do nothing
Else
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
wks.Range("a1:a500").Copy _
Destination:=DestCell
End If
Next wks

On Error Resume Next
.Columns(1).Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End With
End Sub

Continues from the lastcell containing data -- in the Shipped worksheet or
what???
 
Top