Run macro and paste each result to unique sheet in another workbook

[jocker_boy]

Hello,

I have this codes:

Sub John()
    ActiveSheet.Range("$A$1:$G$9").AutoFilter Field:=4, Criteria1:="John"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
End Sub
Sub Miranda()
    ActiveSheet.Range("$A$1:$G$9").AutoFilter Field:=4, Criteria1:="Miranda"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
End Sub

I would like to do this:

Run "John"
Create new workbook and add sheet "John" and paste what was copied
Run "Miranda"
Add sheet "Miranda" in workbook already created and paste what was copied
Save the new workbook in specified path.

Thanks for the help.
Gonçalo

 

[jocker_boy]

Any ideia?
Maybe the correct and easy way should be:

1º Create new workbook with name and path defined
2º Create Sheets "John" and "Miranda"
3º Run "John" and paste in sheet "John"
4º Run "Miranda" and paste in sheet "Miranda"

Thanks

 

[jocker_boy]

After some tries, I get this:

Sub Test()

    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim CopyRange As Range
    Dim LastRow As Long

    Set Sourcewb = Workbooks.Open("C:\Users\T0272578\Desktop\doc.xlsm")
    Set Destwb = Workbooks.Open("C:\Users\T0272578\Desktop\Report.xlsm")

    'Look for the range to copy and set it
    With Sourcewb.Worksheets("Sheet1")
        .Range("$A$1:$G$9").AutoFilter Field:=4, Criteria1:="John"
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set CopyRange = .Range("A1", .Cells(LastRow, .UsedRange.Columns.Count)).SpecialCells(xlCellTypeVisible)
        .AutoFilterMode = False
    End With
        
   'Paste it to the other sheet
    With Destwb.Worksheets("John")
        CopyRange.Copy .Cells(1, 1)
    End With


    'Look for the range to copy and set it
    With Sourcewb.Worksheets("Sheet1")
        .Range("$A$1:$G$9").AutoFilter Field:=4, Criteria1:="Miranda"
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set CopyRange = .Range("A1", .Cells(LastRow, .UsedRange.Columns.Count)).SpecialCells(xlCellTypeVisible)
        .AutoFilterMode = False
    End With
        
   'Paste it to the other sheet
    With Destwb.Worksheets("Miranda")
        CopyRange.Copy .Cells(1, 1)
    End With
    
End Sub

The issue is: it is pasting in "Sourcewb" and not in "Destwb".
This issue should be simple to resolve, but i'm not good with VBA.

Thanks,
Gonçalo