macro to paste into next empty row

[mart10]

I am using Excel 2010

I have a workbook with several sheets in it. I want to paste one static row of data in row 2 in sheet 1 into sheet 2. sheet 2 though is building up a history of data so the paste must go into the next available (empty) row in sheet 2

How can this be done?

 

[kjv1611]

There are a couple of ways I can think of to do this. 1 is a slow way, but might be the safest way. The other way would be the quickest, and I'd recommend it unless you might have random bits of data being added outside your table/range on Sheet2.

For the slow way, you'd basically loop through row-numbers until you find a blank value in say column A.

Here's how I'd do the fast/best(I think) method:

Sub CopyValues
[indent]
    ActiveWorkbook.Worksheets("Sheet1").Select
    ActiveWorkbook.Worksheets("Sheet1").Rows(2).Select
    Selection.Copy
    
    ActiveWorkbook.Worksheets("Sheet2").Select
    ActiveWorkbook.Worksheets("Sheet2").Range("A65000").End(xlUp).Select
    ActiveWorkbook.Worksheets("Sheet2").Range("A65000").End(xlUp).PasteSpecial xlPasteAll
[/indent]
End Sub

You could use variables for the workbook and worksheets as well, but I figured for a quick simple process, we didn't really need it. I know when you tell Excel to record a macro it instead just uses system variables anyway.

Try it out and let us know how that works. Let us know what questions you might have.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Also, if you could possibly have multiple workbooks open when this is triggered, and the workbook you're dealing with could possibly not be the active workbook, then I'd suggest creating a variable, and pointing it to your specifically named workbook to be certain which is having the code run on it. You wouldn't want to mess up a different unrelated workbook.

And the names were the default system names matching your description. You'd of course want to change the names to match what you're using, or else use the numeric sheet designators.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[mart10]

The code you gave worked the first time the paste took place but not after that

 

[kjv1611]

Questions:
1. Is the data on the first sheet constantly changing, or staying the same?
2. Are you trying to run this in a loop, or just on demand?
3. How are you running it multiple times? Are you just telling it to run, and then run, and then run, or else how? And how do you intend to use it?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[mart10]

1. constantly changing - its exchange rates that are snapped daily from external system onto sheet 1 overwriting row 2 but want to store in sequential lines in another sheet2 (a history), so the macro must do this - as well as some other things I have already sorted

2. On demand , once data is snapped onto 1st sheet I am pressing a macro button -(Hope the user will only do once!)
3. 1 and 2 explain I hope

 

[SkipVought]

The code you gave worked the first time the paste took place but not after that

You realize that the macro ONLY COPIES row 2 on Sheet 1

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

try this...

Sub CopyValues()

    ActiveWorkbook.Worksheets("Sheet1").Rows(2).Copy
    
    ActiveWorkbook.Worksheets("Sheet2").Range("A65000").End(xlUp).Offset(1).PasteSpecial xlPasteAll
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mart10]

Nope non of the above working so far. I have named the sheets correctly, I have used both above coding methods and not worked. I think I may have had issue with having freeze screen settings on both tabs so have now taken these off both tabs

 

[mart10]

SkipVought Yes its working now - it WAS the screen freeze screwing up one of the sheets

Tha\nks

 

[kjv1611]

Can you test to be sure - are both methods working, or just the 2nd? I'd like to know the difference if there is a difference in effect.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

By the way... if you have VBA macros running that are causing the freeze-ups, then you can try slipping in this command:

DoEvents

at various spots.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Steve,

It's your code WITHOUT Select, HOWEVER, it's been corrected to include [highlight #FCE94F].Offset(1)[/highlight], so it won't overwrite the last row.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[kjv1611]

Gotcha. Okay. I was wondering if I needed an Offset. I was under the impression that it was working w/o it. Either way, glad it's working.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57