Copy and Paste in the first empty available line.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All,

I am starting to write a VBa code in order to copy 4 cells on the same line
in another sheet in order to keep the history. Right now, the present code
below just overwrite the previous data everytime I run it. I would like to be
able to have Excel copy on the next available empty line below the previous
one. How should I change my code?

Sub CopyandPaste()
Dim CopyRange As Range
Set CopyRange = Range("B5:D5")
CopyRange.Copy Destination:=Worksheets("Hist").Range("B7:D7")
End Sub

Thanks for your help.

Etienne
 
This isn't test...so give it a shot.

Sub CopyandPaste()
Dim CopyRange As Range
Set CopyRange = Range("B5:D5")
CopyRange.Copy Destination:=Worksheets("Hist") _
.Range("B" & (Range("B65536").End(xlUp).Row) & ":D" &
(Range("B65536").End(xlUp).Row))
End Sub
 
I get an error message of compiling at &":D"

Do you know why?

Thanks,
 
Sorry, I made an error typing.

Yet, the macro works but keeps on overwriting the previous data. Excel does
not copy on the next available line.

Etienne
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA set thick borders around non empty cells in range 0
Copy To New Sheet 5
help with dates in the past 5
Simple macro 3
Problem with pivot index 2
Simplify code copy based on condition 2
Copy rows where cells contain red and black font. 2
Copy and Paste part II 2

Back
Top