Copying and hiding issues...

  • Thread starter Thread starter mjack003
  • Start date Start date
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
 
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???
 
Back
Top