CODEWITHSUNDEEP
vbaexcel
Steve C.
Steve C.

Reputation: 1353

How to properly copy data from one sheet to another after first empty cell with vba in excel?

I have a button on my sheet that when clicked is supposed to copy data from my "template" sheet below the first blank cell. Unfortunately it instead creates a whole new workbook with the copied data. I have it set to copy the template data when a new sheet is created that works as expected but for some reason this button does not. Here is my code:

Sub paste_newcalc()
    Set WshSrc = ThisWorkbook.Worksheets("Template")

    Dim rFirstBlank As Range

    ThisWorkbook.Activate

    WshSrc.Copy

    Set rFirstBlank = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Offset(1)

    With ActiveSheet.Cells
        .PasteSpecial

    End With

End Sub

What I have used there is what I have found through searching the internet for a solution.

Upvotes: 2

Views: 210

Answers (2)

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

Sub paste_newcalc()
     Dim rFirstBlank As Range
     Set rFirstBlank = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Offset(1)
     Worksheets("Template").Range("A4:D40").Copy rFirstBlank
End Sub

Upvotes: 0

Steve C.
Steve C.

Reputation: 1353

Ok. With the help of the comments above, I altered my vba code and found a solution. If I have a default range of data on one sheet, let's call it "Template", and I want to copy that below the data I already have in my current sheet with the click of a button, the following code will do it.

Sub paste_newcalc()
    Dim rFirstBlank As Range

    Set rFirstBlank = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Offset(1)

    Worksheets("Template").Range("A4:D40").Copy

    rFirstBlank.PasteSpecial


End Sub

That will paste the range you want from another sheet below the data you already have in the first empty cell. Thank you to those that commented!

Upvotes: 1

Related Questions