Copying to first empty row?

[terrypin]

I finally have the copying aspects of my macro working, after much help here thanks. To discuss the next stage I've attached a much simplified version of both source and destination workbooks used.

The macro currently copies data from Source-Simplified.xlsm to fixed row 8 of WalkIndex-Extract.xlsm

Please Login or Register  to view this content.

I want to edit that to copy instead to the first row in which col A is empty, row 4 in my example. The first run would therefore enter two cells into row 4, instead of row 8. And so on, as the number of rows increases.

This code finds the first empty row OK.

Please Login or Register  to view this content.

But attempts to use 'FirstEmpty' in Sub Extract_Simplest() have so far defeated me and any help would be appreciated please

[Sintek]

Please Login or Register  to view this content.

[terrypin]

Many thanks @sintek, appreciate the fast reply.

Your code correctly copies source B5 to C4, but B27 should go to D4, not C5. However, you would not have been able to spot this by running your macro because I must have saved the source while B27 was empty, sorry!

As you've done the hard part I was able to make the two minor edits and it now works fine, thanks. I've attached updates of the sheets.

Please Login or Register  to view this content.

Next minor step will be to add a new line each time, to keep TOTALS below the last entry. And then the slog of extending the simplified to the full version. But I'm optimistic of having a working macro soon, which I can then apply to a hundred or two source files.

[Sintek]

TOTALS below the last entry

Totals of what...Nothing to sum?

[terrypin]

Totals of what...Nothing to sum?

That's just text at present, for this simplified version. The full macro counts all rows from row 2 downward to the last one.

I've added a line that keeps the space.

Please Login or Register  to view this content.

[Sintek]

Okey dokie...Thanks for rep +

[terrypin]

This line, to insert a blank row after the last entry was pasted, worked fine in the simplified version I posted earlier. But in the full version it now highlights 'xlFormatFromRightOrAbove' with the error "Compile error: Variable not defined".

No variable was defined before. And isn't this a parameter, not a variable anyway?

Please Login or Register  to view this content.

[Sintek]

Think you should upload a workable file with your actual requirement for this TOTAL...Your solution might not be the best alternative...
Perhaps you should start a new thread depicting your requirement, as it no longer pertains to this thread...

[terrypin]

Not exactly sure how, but I found a winning combination after a couple of hours of experiment and happily that snag is now resolved thanks.

You’re right, I realise now that I should have started a new thread, given that I’d already set this one as solved. There are a few others and I’ll post separately if I can’t fix them myself.