Problem with Pasting to Next Available Row

[toddncar]

Hi all,

Being new to VBA, I am struggling with pasting data to the next available row in a separate worksheet.


What I want to do is use one Worksheet ("HAT") as a form for people to enter data. Using a macro, I then want this data transferred into another Worksheet ("Data Sheet"). My problem is setting up the macro so that it does not overwrite information that is already in a row of the Data Sheet, instead, copies the data into the next available blank row in the Data Sheet whenever the macro is run.

This is what I have so far for one cell of data to be transferred:
Sheets("HAT").Select
Range("B5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data Sheet").Select
Range("C2").Select
ActiveSheet.Paste

My guess is that it involves- "NextRow" or "Offset", though despite aimlessly trying using other suggestions on the web, I have not been successful.

Appreciate any comments/ solutions for this.

Cheers
toddncar

 

[AlphaFrog]

Try something like this...

Sheets("Data Sheet").Range("C" & Rows.Count).End(xlUp).Offset(1).Value = _
    Sheets("HAT").Range("B5").Value

 

[toddncar]

Thanks AlphaFrog,

When I enter this, I come up with the error message at ("B5")

"Compile Error

Invalid outside procedure"

Any thoughts on how to rectify this?

Cheers

 

[AlphaFrog]

This worked for me.

Sub Macro1()

    Sheets("[COLOR="Red"]Data Sheet[/COLOR]").Range("C" & Rows.Count).End(xlUp).Offset(1).Value = _
        Sheets("[COLOR="Red"]HAT[/COLOR]").Range("B5").Value

End Sub

Make sure you have two sheets named exactly as in the code; "Data Sheet" and "Hat".

 

[toddncar]

Thanks again, AlphaFrog

You have solved the problem! I had to copy and paste the sheets into a new workbook as said it could not operate in "Break mode".

All seems to work now.

Much appreciated for your quick response.
Toddncar