Copy to first empty line on next Sheet.

**QualitySoup** · 08-06-2022, 11:04 AM

Hi, all

I'm working with a code that will copy contents of a row from one sheet to the last row on the next sheet however, the information on the latter sheet's rows will be removed in a random order. Is there a way to copy the contents to the first empty row, rather than the last row? There will be a lot of information over time removed latter sheet and I'd rather that sheet not have 200+ empty rows over time at the top.

Here's the code I'm working with:

Please Login or Register  to view this content.

**jolivanes** · 08-06-2022, 01:11 PM

Would it not be better to delete all empty rows in the 2nd sheet first and then put the data from the first sheet at the bottom?
That would eliminate the empty rows altogether as you mentioned that you don't want them.
Either way is no problem, just let us know.

Try this on a copy of your workbook.
I assume that you want the data of the selected cell(s) copied.
Change references as required.

Please Login or Register  to view this content.

**Mumps1** · 08-06-2022, 01:38 PM

Try:

Please Login or Register  to view this content.

**mjr veverka** · 08-06-2022, 02:08 PM

Originally Posted by QualitySoup

... to copy the contents to the first empty row, rather than the last row ...

Strange "needs" ... try:

Please Login or Register  to view this content.

**jolivanes** · 08-06-2022, 02:27 PM

My apologies. I have the nasty habit to go by what people explain without delving into the not working code. After all, the code is not doing what one expects.
I also noticed that you want a larger range copied/pasted.
Forget Post #2.
It can easily be changed to fit your needs but you have a few well respected helpers that have suggestions for you already.

**QualitySoup** · 08-07-2022, 09:54 AM

Originally Posted by Mumps1

Try:

Please Login or Register  to view this content.

This replaces the Rows on receiving sheet from top to bottom. If possible, I need it to find the first empty row on the receiving sheet.

**QualitySoup** · 08-07-2022, 09:56 AM

Originally Posted by mjr veverka

Strange "needs" ... try:

Please Login or Register  to view this content.

This bring a Run-time error '1004': We can't do that to a merged cell.
Is there a way to Upload the workbook or sheet here to give a better idea?

**QualitySoup** · 08-07-2022, 10:00 AM

Is there a way to Upload the workbook or sheet here to give a better idea?

Here's the workbook for reference.

**Mumps1** · 08-07-2022, 10:05 AM

I tested the macro using some dummy data and it worked properly. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

**QualitySoup** · 08-07-2022, 10:11 AM

Originally Posted by Mumps1

I tested the macro using some dummy data and it worked properly. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet...

I've uploaded the workbook above. Do you still need XL2BB?

**Mumps1** · 08-07-2022, 10:15 AM

Try:

Please Login or Register  to view this content.

**QualitySoup** · 08-07-2022, 10:23 AM

Originally Posted by Mumps1

Try:

Please Login or Register  to view this content.

I'm getting a Run-time Error on this one.
Run-time error '1004': We can't do that to a merged cell.

**Mumps1** · 08-07-2022, 11:28 AM

Try the attached file. I had to unmerge the cells in row 1 of each sheet. I have reformatted the range in row 1 as "CenterAcrossSelection". The cells look merged but are actually not merged.

**QualitySoup** · 08-07-2022, 11:47 AM

Originally Posted by Mumps1

Try the attached file. I had to unmerge the cells in row 1 of each sheet. I have reformatted the range in row 1 as "CenterAcrossSelection". The cells look merged but are actually not merged.

This is PERFECT! Thank you so much for your help!!

**Mumps1** · 08-07-2022, 12:01 PM

You are very welcome.

**QualitySoup** · 08-07-2022, 12:26 PM

So I found an issue. When Rows 3 and beyond are empty, The information goes to B1 and unformats the row.

**Mumps1** · 08-07-2022, 01:06 PM

Try:

Please Login or Register  to view this content.

**QualitySoup** · 08-07-2022, 02:03 PM

Originally Posted by Mumps1

Try:

Please Login or Register  to view this content.

Everything works except the Clear Contents. The information for that row stays on the original sheet.

**QualitySoup** · 08-07-2022, 02:06 PM

Originally Posted by Mumps1

Try:

Please Login or Register  to view this content.

Ok so, I removed the " ' " from before the " 'Rows(r).ClearContents " and it works perfectly.

**Mumps1** · 08-08-2022, 07:29 AM

Glad it worked out.

**QualitySoup** · 08-08-2022, 08:33 PM

Originally Posted by Mumps1

Glad it worked out.

Is there a way to implement this on the second sheet as well? I tried putting the code on the second sheet so that it'll copy to the third, but it had the same issue as before; replacing B2.

The way that it'll hopefully work in the end is that information will start on sheet 1, then when it meets criteria move it to sheet 2, then to sheet 3 when it meets criteria.

**Mumps1** · 08-09-2022, 10:16 AM

I'm not sure what you mean. Do you want to be able to double click on a value in column C in any sheet and copy the data to the next sheet? Please clarify in detail step by step, and attach an updated file to demonstrate what you mean.

**QualitySoup** · 08-09-2022, 06:31 PM

Originally Posted by Mumps1

I'm not sure what you mean. Do you want to be able to double click on a value in column C in any sheet and copy the data to the next sheet? Please clarify in detail step by step, and attach an updated file to demonstrate what you mean.

Hey disregard what I said (user error lol) This works great!

**QualitySoup** · 09-14-2022, 03:21 AM

Originally Posted by Mumps1

Glad it worked out.

Not to revive an old thread but...
Is there anyway to exclude column A when moving the contents in the below code?

Please Login or Register  to view this content.

Thank you in advance!

**Mumps1** · 09-14-2022, 07:48 AM

The code currently starts copying at column C so column A is not included.

**QualitySoup** · 09-15-2022, 01:20 AM

Originally Posted by Mumps1

The code currently starts copying at column C so column A is not included.

Sorry. What I meant to ask is when the code clears contents at the end, is there a way to not clear contents in column A?

**Mumps1** · 09-15-2022, 07:56 AM

Replace:

Please Login or Register  to view this content.

with:

Please Login or Register  to view this content.

Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Re: Copy to first empty line on next Sheet.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Empty sheet before updating but leave title line at top and totals line at bottom

[SOLVED] If one cell in line turn empty then entire line turns empty

[SOLVED] Copy and Paste in the first empty available line.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions