Pate data into multiple existing worksheets

[Siddhu11011]

I have data in "Pivot" sheet from column A to E. I have existing multiple sheets. based on sheet name, filter out column A in "pivot" sheet; copy out put and go to respective sheet to paste the data. I have existing code but don`t know the pasting part. Please help me out

Sub Macro1()
Dim y As Range
Dim rng1 As Range
Dim last1 As Long
Dim sht1 As String
'
'specify sheet name in which the data is stored
sht1 = "Pivot"
last1 = Sheets(sht1).Cells(Rows.Count, "A").End(xlUp).Row
Set rng1 = Sheets(sht1).Range("A1:E" & last1)
For Each y In Range([AA2], Cells(Rows.Count, "AA").End(xlUp)) 'AA column contains all exhisting sheet name

With rng1
.AutoFilter
.AutoFilter Field:=1, Criteria1:=y.Value
.SpecialCells(xlCellTypeVisible).Copy

'Which code can help me to search sheet name and paste"

End With
Next y

End Sub

 

[HaHoBe]

Hi Siddhu11011,

you should get used to qualify the ranges to meet the sheets as otherwise they would always work on the active sheet. Maybe

Sub MrE_1227877_1701812()
' https://www.mrexcel.com/board/threads/pate-data-into-multiple-existing-worksheets.1227877/
Dim rngCell As Range
Dim rngWork As Range
Dim wsPivot As Worksheet
'
'specify sheet name in which the data is stored
Set wsPivot = ThisWorkbook.Worksheets("Pivot")
With wsPivot
  Set rngWork = .Range("A1:E" & .Cells(.Rows.Count, "A").End(xlUp).Row)
  If Not rngWork Is Nothing Then
    For Each rngCell In .Range([AA2], .Cells(.Rows.Count, "AA").End(xlUp)) 'AA column contains all exhisting sheet name
      With rngWork
        If .AutoFilterMode Then .AutoFilterMode = False
        .AutoFilter Field:=1, Criteria1:=rngCell.Value
        .SpecialCells(xlCellTypeVisible).Copy Worksheets(rngCell.Value).Range("A1")
      End With
    Next rngCell
  End If
End With

Set rngWork = Nothing
Set wsPivot = Nothing
End Sub

All data located on Sheet "Pivot" in workbook with code.

Ciao,
Holger

 

[Siddhu11011]

Hi Siddhu11011,

you should get used to qualify the ranges to meet the sheets as otherwise they would always work on the active sheet. Maybe

Sub MrE_1227877_1701812()
' https://www.mrexcel.com/board/threads/pate-data-into-multiple-existing-worksheets.1227877/
Dim rngCell As Range
Dim rngWork As Range
Dim wsPivot As Worksheet
'
'specify sheet name in which the data is stored
Set wsPivot = ThisWorkbook.Worksheets("Pivot")
With wsPivot
  Set rngWork = .Range("A1:E" & .Cells(.Rows.Count, "A").End(xlUp).Row)
  If Not rngWork Is Nothing Then
    For Each rngCell In .Range([AA2], .Cells(.Rows.Count, "AA").End(xlUp)) 'AA column contains all exhisting sheet name
      With rngWork
        If .AutoFilterMode Then .AutoFilterMode = False
        .AutoFilter Field:=1, Criteria1:=rngCell.Value
        .SpecialCells(xlCellTypeVisible).Copy Worksheets(rngCell.Value).Range("A1")
      End With
    Next rngCell
  End If
End With

Set rngWork = Nothing
Set wsPivot = Nothing
End Sub

All data located on Sheet "Pivot" in workbook with code.

Ciao,
Holger

I understood you advice and will work on that part. I got the debug and removed code

If .AutoFilterMode Then .AutoFilterMode = False

Replace with .AutoFilter and working well now
Thanks for you help. Means a lot...