Copy Selection Sheet1, Paste Values First Open Row Sheet2

[sd2173]

Good afternoon all,

I am trying to develop a simple macro to on button click transfer the data on sheet1 (for example called "Notes") to the first open row on sheet2(for example called "Log"). Below is where I am at right now, which gives me the result after clicking where I can see it shifts to the Log page but doesn't past values in the open row under the header. I also see when going back to Notes that it did select and copy.

I also wouldn't mind "cutting" the data from Notes so it is empty after the transfer.

Sub Button1_Click() Dim NextRow As Range
Set NextRow = Range("a" & Sheets("Log").UsedRange.Rows.Count + 1)
Sheets("Notes").Range("a2:g6").Copy
Sheets("Log").Activate
NextRow.PasteSpecial Paste:=xlValues, Transpose:=False
Application.CutCopyMode = False
Set NextRow = Nothing
End Sub

Data1	Data2	Data3	Data4	Data5	Data6	Data7
123	x	aaa	111	a	5	543
123	y	aaa	222	b	6	453
123	x	bbb	333	a	8	765
123	y	bbb	444	c	4	543
123	y	aaa	555	a	2	234

<tbody>
</tbody>

Any guidance is much appreciated, I don't know where I'm astray.

Regards,
Spencer

 

[Michael M]

Try

Sub Button1_Click()
Dim NextRow As Long
NextRow = Sheets("Log").UsedRange.Rows.Count + 1
Sheets("Notes").Range("a2:g6").Copy
Sheets("Log").Range("A" & NextRow).PasteSpecial Paste:=xlValues
End Sub

 

[sd2173]

Excellent! Is there a simple way to do all cells in Notes because likely it will always be a different amount of rows. I tried replacing with "Sheets("Notes").Range("a:a:g:G").Copy", but when comes to pasting in Log unable to do so since "size" not same as from copy. Also, the majority of Notes columns will be based off formulas I plan to run down to row 1000 and add on to macro to replace after I cut pasted info. Sheets("Notes").Range("a:2:g:1000").Copy doesn't seem to do the trick either.

Appreciate the help!

Spencer

 

[Michael M]

try

Sub Button1_Click()
Dim NextRow As Long, lr As Long
NextRow = Sheets("Log").UsedRange.Rows.Count + 1
lr = Sheets("Notes").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Notes").Range("a2:g" & lr).Copy
Sheets("Log").Range("A" & NextRow).PasteSpecial Paste:=xlValues
End Sub

 

[Teeroy]

sd2173,

Personally I prefer to assign values directly rather than copy & pastespecial (where appropriate). To try this alternative approach (on a COPY of your data) use:

Sub Button1_Click()
Dim rFrom As Range
Set rFrom = Sheets("Log").Range("a2:g" & Range("A" & Rows.Count).End(xlUp).Row)
Sheets("Notes").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(rFrom.Rows.Count, 7).Value = rFrom.Value
rFrom.ClearContents
End Sub

MichaelM,

There's a possible pitfall in using UsedRange to count rows for LR. Look at what happens when your UsedRange doesn't start on Row 1.

Teeroy

 

[Michael M]

Hi Teeroy
Yep agreed and aware of the usedrange issue.
I generally avoid it and go with lastrow approach.

 

[Teeroy]

Hi Michael,

I don't mean to say I don't use UsedRange myself; its good when you don't know which column contains the most rows. I found a neater way to use UsedRange that doesn't have the same pitfall, e.g.

With ActiveSheet.UsedRange
    LR = .Rows(.Rows.Count).Row
End With

Troy

 

[Michael M]

Hmm.....haven't seen it used that way....like it !!...