Copy from one worksheet, paste to another worksheet... with a twist

[BBY-SPORT]

Hi guys,

I've been doing a bunch of looking and I'm so stuck on this it's not funny... so let me go over what I am doing, and lets see if I can't get some help getting over this hurdle.

Okay, so here is the situation...

I am trying to get Excel (2007 by the way) to copy a set range from one worksheet (Notes), to another worksheet (DataFile). This isn't the problem as I can certainly get VBA to do this... I am attempting to have VBA find the first available row to paste to, then to paste the contents.

Now both worksheets are formatted the same way, same amount of columns, same information between the two worksheets.

The range on the Notes worksheet is from A2:R7, this will never change in any way... so here is what I started at:

Private Sub btnSubmit_Click()

'Select copy range from "Notes" worksheet
Worksheets("Notes").Range(A2:R7).Copy

'Set Variables for empty row
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DataFile")

'Find first empty row
iRow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
Selection.PasteSpecial

End Sub

Unfortunately, this does not work, and gives me some pesky runtime errors, I am at a loss right now. If anyone can lend a hand and share some knowledge with me please?

 

[njimack]

Welcome to the board. Something like this:

Private Sub btnSubmit_Click()
Dim iRow As Long

iRow = Sheets("Datafile").Range("B" & Rows.Count).End(xlUp).Offset(1).Row

Sheets("Notes").Range("A2:R7").Copy Sheets("Datafile").Cells(iRow, 2)

End Sub