vba

[rlee11]

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

 

[Norie]

Oops, on my phone didn't mean to post that.

 

[Tim_Excel_]

VBA

For a sec I thought that was your answer to the question :D
"What am I doing wrong?" - "VBA."

 

[Tim_Excel_]

What is the error you are getting?

EDIT
Actually, I think the error is due to a typo.
ActiveSheet.PasteSpecial x1PasteAll --> ActiveSheet.PasteSpecial xlPasteAll

 

[rlee11]

Thanks for catching that typo!

What is the error you are getting?

The error I get is "PasteSpecial method of Worksheet class failed"

I changed it back to ActiveSheet.Paste and now it works but it pastes the data over the header column instead of finding the first empty row.

 

[Tim_Excel_]

As you have the code now, the ActiveSheet.PasteSpecial xlPasteAll line always gets executed. You'd need to integrate it with an If function to check whether the sheet exists or not.

 

[rlee11]

Moving it into an If statement worked after I simplified the 'Find end of Data' section

Thank you!