I have a code that will copy data from a sheet "Import" then search for a sheet by a name referenced in A6. If it does not exist, it will copy over the header row, if it already exists, it pastes the data to the end of the dataset. I keep getting an error on the final paste command with everything I try. What am i doing wrong?
Sub Copy1() Dim sName As String sName = Sheets("Import").Range("A6").Value 'Name Columns If Sheets(sName).Range("A1") = "" Then Sheets("Import").Select Range("A5:M5").Select Selection.Copy Sheets("P3").Select Range("A1").Select ActiveSheet.Paste End If 'Copy Data Sheets("Import").Select Range("A6:M6").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy 'Find End of Data Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer Dim currentRowValue As String Sheets(sName).Select sourceCol = 1 'column A has a value of 1 rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row 'for every row, find the first blank cell and select it For currentRow = 1 To rowCount currentRowValue = Cells(currentRow, sourceCol).Value If IsEmpty(currentRowValue) Or currentRowValue = "" Then Cells(currentRow, sourceCol).Select End If Next 'Paste Data ActiveSheet.PasteSpecial x1PasteAll End Sub