Macro to paste data to the bottom of a table

[gmmom00]

I have a scorecard from a survey and keep a datasheet with all the results from every scorecard submitted. I am trying to write a macro that copies data from scorecard to next empty row in datasheet table so that charts update automatically, but cannot figure out how to have macro paste to next row of the table of data.

Thanks!

 

[trunten]

To find the last used row on a sheet:

Dim lastRow as long
With Sheets(Sheet Name/Index)
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row '+ 1 for next available row
End With

Obviously that code assumes there's data in column a, if there isn't change the lettering appropriately.
hth.

You can then use that in your copy/paste macro like so

Range("Whatever").Copy Destination:=Sheets(whatever).Cells(lastRow, "A")

 

[gmmom00]

I am very new to macros, I am only using record to write them, so I am learning how to edit them after. You are correct with column A. My data is on tab DATASHEET and the file is Survey. Do both items from above go into the macro? What goes in place of "whatever"?

Thanks.

 

[trunten]

Ok, you tell me the name of the sheet, and the range(s) that you need to copy from, then tell me then name of the sheet that you want to copy to and I'll put together an example for you.

 

[gmmom00]

Thanks so much!
3 tabs in workbook: SCORECARD, DATA and CHARTS.

SCORECARD tab (want to copy data from A1:A8)
Col A Col B
Albert Name
2/14/2011 Date of Stay
24 location
3 Cleanliness
2 Check-In
3 Breakfast
2 Décor
3 Bed

DATA tab (Paste data to next row, for example $A$X-$H$X)
Col A Col B Col C Col D Col E Col F Col G Col H
Date of Stay Location Guest Name Cleanliness Check-In Breakfast Decor Bed
6/4/2011 33 Smith 5 3 4 2 3
5/10/2011 24 Jones 4 5 2 5 5

 

[trunten]

Sorry, I had to pop out for a bit:
Here you go

Sub copyData()
    With Sheets("DATA")
        .Cells(.Rows.Count, 1).End(xlUp)(2, 1).Resize(1, 8).Value = _
            Application.Transpose(Sheets("SCORECARD").Range("A1:A8").Value)
    End With
    'Uncomment to clear after copy
    'Sheets("SCORECARD").Range("A1:A8").ClearContents
End Sub

Uncomment (remove the ' at the start of the line) the last line to clear a1:a8 after it's been copied.

 

[gmmom00]

Sooo close, so I thought. I keep getting a syntax error for the sub below. What am I not seeing?

Sub copydata()
'
' copydata Macro
' copy data
'
' Keyboard Shortcut: Ctrl+Shift+D
'
With Sheets("DATA")
.Cells(.Rows.Count, 1).End(x1Up)(2, 1).Resize(1, 8).Value=_Application.Transpose(Sheets("SCORECARD").Range("A1:A8).Value)
End With
End Sub

 

[trunten]

Sooo close, so I thought. I keep getting a syntax error for the sub below. What am I not seeing?

Sub copydata()
'
' copydata Macro
' copy data
'
' Keyboard Shortcut: Ctrl+Shift+D
'
With Sheets("DATA")
.Cells(.Rows.Count, 1).End(x1Up)(2, 1).Resize(1, 8).Value=_Application.Transpose(Sheets("SCORECARD").Range("A1:A8).Value)
End With
End Sub

Hi, you must remove the underscore, or copy and paste my code exactly. Space + underscore allows you to have line breaks in code without ending the statement.

i = _
1

is the same as

i = 1

(note how it's actually " _" for the line break not just "_")

 

[gmmom00]

Thanks so much!

 

[dianamruelas]

Could you help me with something similar? I would like to cut and paste A6:Q6 on Sheet1 to the bottom row of A:Q on Sheet2. I daily enter new data on Sheet1 A6:Q6 and would like my list on Sheet2 to continuously grow every time I run this Macro.

Thanks!