VBA to paste data in new tabs

[ALAMZEB]

Hi
I need to find sheet from column M and then copy/paste data to relevant sheet.
It’s giving me error when trying to paste data in new tab in column A
Thanks in advance

 &

Sub copyPasteData()

Dim strSourceSheet As String
Dim strDestinationSheet As String
Dim lastRow As Long

strSourceSheet = "Drop"

Sheets(strSourceSheet).Visible = True
Sheets(strSourceSheet).Select

Range("M11").Select
Do While ActiveCell.Value <> ""
strDestinationSheet = ActiveCell.Value
ActiveCell.Offset(0, -9).Resize(1, ActiveCell.CurrentRegion.Columns.Count).Select
Selection.Copy
Sheets(strDestinationSheet).Visible = True
Sheets(strDestinationSheet).Select
lastRow = LastRowInOneColumn("A")
Cells(lastRow + 1, 1).Select
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets(strSourceSheet).Select
ActiveCell.Offset(0, 9).Select
ActiveCell.Offset(1, 0).Select
Loop


End Sub
Public Function LastRowInOneColumn(col)
'Find the last used row in a Column: column A in this example
Dim lastRow As Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
End With
LastRowInOneColumn = lastRow
End Function

 

[Asheesh]

Why do you wanna loop through all the cells..and not use autofilter option then copy paste visible cells..

 

[Asheesh]

Hi Alamzeb, this is what I am talking about..

Insert a module and run the codes on the attached worksheet

Option Explicit

Sub test()
Dim DumpSheet As Worksheet
Dim c As Range
Dim ReqRng As Range
Set DumpSheet = Sheets("Drop")
DumpSheet.Activate
Set ReqRng = DumpSheet.Range("D9").CurrentRegion
For Each c In Range(Range("P3"), Range("P65536").End(xlUp))
ReqRng.AutoFilter Field:=12, Criteria1:=c.Value
ReqRng.SpecialCells(xlCellTypeVisible).Copy
Sheets(c.Value).Range("A1").PasteSpecial Paste:=xlPasteValues

Next c
End Sub