Combine data from two sheets in two one
 

Dear experts,
My scenario isn't that complex but obviously I am missing something:

I have two worksheet containing similar information (same amount of columns
and same amount of column names but different amount of rows).
From the first worksheet I select specific rows using an advanced filter. I
copy those to a newly created sheet.
From the second I apply the same advanced filter and would like to copy the
rows to the formerly newly created sheet without deleting the previous pasted
rows.
Be aware that I do not care if the rows are copied before or after the
previous pasted rows. But think about that the first paste will also paste
the column headers which should be excluded in the second paste.

I appreciate your help!

Holger

Here my code so far which results in an error that the range is not
appropriate selected:

Sub Combine_Sheets()

Dim LastRow As Range, LastRowNumber As Long

Application.ScreenUpdating = False

' Get Range for Advanced Filter from Basic Data Sheet
Sheets("Basic Data").Select
MyRange_CC = "J9:J" & (Worksheets("Basic Data").Range("I4").Cells + 8)
'Get Source_Sheet1
Sheets(Source_Sheet1).Select
Set LastRow = Cells(Rows.Count, "A").End(xlUp)
LastRowNumber = LastRow.Row

MyRange_Dataset = "A1:AF" & LastRowNumber
' apply advanced filter for MyRange on Source_Sheet1
Columns(MyColumns).Select
Range(MyRange_Dataset).AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Sheets("Basic Data").Range(MyRange_CC), Unique:=False
' Copy & Paste Selection
With Selection
.CurrentRegion.Select
.SpecialCells(xlCellTypeVisible).Select
.Copy
End With
' Create Target_Sheet and paste
Sheets.Add Type:="Worksheet"
ActiveSheet.Name = "Target_Sheet"
Sheets("Target_Sheet").Select
ActiveSheet.Paste
Application.CutCopyMode = False

' apply advanced filter with same criteria on Source_Sheet2
Sheets("Source_Sheet2").Select
Columns(MyColumns).Select
Range(MyRange_Dataset).AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Sheets("Basic Data").Range(MyRange_CC), Unique:=False
' Copy & Paste Selection
With Selection
.CurrentRegion
.SpecialCells(xlCellTypeVisible).Select
.Copy
End With
Sheets("Target_Sheet").Select
' This is my try to paste it by shifting the existing cells downward
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=True, Transpose:=True

Application.CutCopyMode = False
End Sub