Macro to copy and paste new data into the next blank cell WITHIN a range

**MattRNR** · 10-15-2015, 11:50 PM

G'day Excel Forum

I'm attempting to automatically copy down a new data value into the next blank cell within a given range (in the attached example that range is C6:C13 and the next blank cell would be C10).

There is however a range of data below this target range, and my macro pastes the data in the first blank cell in this second range of data rather than in the blank cells in the first, as this is the last blank cell in the entire sheet (see attached for this to make any sense).

Has anyone any idea how to rectify this such that my macro will only paste into the next blank cell between C6 and C13?

Alas I'm fairly new to macro's and haven't yet got my head around a lot of this.

Regards MattRNR

P.S. is it possible also to paste as values so it doesn't bugger up the formatting of the destination cell? i.e. wipe out my boarders?

**protonLeah** · 10-16-2015, 12:11 AM

Here's one way:

Please Login or Register  to view this content.

You need to check if LR = 8 then you are out of space

**MattRNR** · 10-16-2015, 12:27 AM

G'day Ben

That looks as though it should work, however when I ran it (attached) it ended up pasting over C5...

Have I missed something?

**protonLeah** · 10-16-2015, 01:14 AM

Sorry, I forgot it starts six rows down:

Please Login or Register  to view this content.

**MattRNR** · 10-16-2015, 01:31 AM

Ah yes I see now.

Perfect I should be able to adapt that pretty easily to my purposes, really appreciate the help mate.

Just one more question (if you don't mind; I realize it must be getting late on your side of the world); Did you have any idea how to get it to paste as values to preserve the destination formatting?

Regards

MattRNR

**Keebellah** · 10-16-2015, 02:45 AM

Just paste values

**MattRNR** · 10-16-2015, 03:32 AM

G'day Hans, I can paste the values manually but I need the macro to paste as values automatically when I click the button.

Any thoughts?

**Keebellah** · 10-16-2015, 04:06 AM

Hi, yes, if you record a macro that does what you want you can edit it.

But tyou can try that later

Please Login or Register  to view this content.

User the LR which is the last row + 6 and the the range C3 to copy and then only paste the values, not the formatting

Is this what you're looking for?

**MattRNR** · 10-16-2015, 06:10 PM

Strange result; works more or less perfectly on the test sheet

However when I go to integrate it into my full spreadsheet (attached) it will only paste into the first of the 6 rows, when clicked again it pastes over whatever was in the first cell before.

The macro worked fine in the original test sheet so I imagine I've modified it incorrectly.

P.S. It may be worth mentioning that I will eventually be using 3 of these copy and paste macro's (for the pale orange cells) and running them all from the one button, I don't know if this is relevant but I thought I'd mention it to be safe.

Thank you again for your help

**Keebellah** · 10-16-2015, 06:18 PM

Hi,
I was about to turn my system off when your mail came in.
I've downloaded the file and will take a look at it this weekend and see if I can help.

**MattRNR** · 10-16-2015, 07:08 PM

Cheers mate, appreciate it.

Let us know if it's too much of a hassle, I don't want to waste your time (especially your weekend)

**Leith Ross** · 10-16-2015, 07:27 PM

Hello MattRNR,

You need to think about this problem using the objects available to to you in VBA and not like a worksheet formula.

Please Login or Register  to view this content.

**MattRNR** · 10-17-2015, 05:07 AM

G'day Leith

Alas I've only just started to become aware of how useful VBA can be and haven't yet learned to apply it very effectively.

As with Keebellah's solution I was able to apply it successfully to my test sheet however I can't seem to work out how to adapt it to the full version of my spreadsheet.

I've attached my attempt, are you able to tell me where I've gone wrong?

Regards

MattRNR

**Keebellah** · 10-17-2015, 08:25 AM

This will always place the data in the first empty row

Please Login or Register  to view this content.

**Leith Ross** · 10-18-2015, 02:56 AM

Hello MatRNR,

Thanks for posting the workbook. It is always good to see all that is going on in the workbook. There was more to this than simply adding the macro.

In this version of the workbook, I have made a few changes that will make this easier to maintain. Generally, it is not a good idea to include formulas in your database. The database should be clean of code to allow the most flexibility with the data. VBA can handle a large portion of the grunt work for you much more easily than formulas, as you will see.

The clear buttons for data to print all use the same macro now instead of separate macros. A little VBA magic makes this possible. Nothing really difficult, but not often seen in macro examples.

VBA now handles the data entered into cell $H$5 on the Search worksheet. The data is automatically entered when the user has finished entering data in the cell. In VBA there are many processes that happen when data is input. Many of the processes make certain information available at particular points in the process. These are called Events and what causes an Event to happen is known as a trigger.

Have a go with it and let me know if this will work for your needs.

Clear Button Macro

Please Login or Register  to view this content.

SEARCH - Worksheet_Change Event Code

Please Login or Register  to view this content.

**Keebellah** · 10-18-2015, 03:33 AM

@Leith: Nice go.
I agree with you that macro's do no belong in the 'database'
I didn't know what the poster really dues and the Add product etc etc and how he fills the range to prepare for the prints.
The existing macros no longer exist or have been renamed thus the button no longer works, it's easy to solve.
I write and develop 'user interfaces' for these cases and in such a way that the formulas are not altered as well.

@Matrnr: if you could attach a small explanation of how you work with this I could write a sample for you

**Keebellah** · 10-18-2015, 06:20 AM

Okay,

I did some modifications and stuff.
Not much comments added to the vba code. cleande the VBA from the unnecessary and empty modules, renamed the modules to what they do.
Give it a run.
Some things still need fixing but...

Have a nice weekend (it's almost over down under

**Keebellah** · 10-18-2015, 07:34 AM

Some minor cosmetic changes and a button

**MattRNR** · 10-18-2015, 08:28 PM

G’day again

Hans

That code works for my spreadsheet however only pastes into the lowest empty cell, rather than the next free cell in the range. If there is a gap above the last occupied cell in the range this would then be the next blank cell.
In the attached example it should paste into cell C17 rather than C19 as this is the highest blank cell in the range.
Can the code be modified somehow to achieve this?

Lieth

Wow… how long did this take you? I knew I was an amateur to this VBA stuff but I had no idea just how much of an amateur I was…

I don’t even know where to begin with it if I’m honest; every time I look at it I end up sitting back in a haze of awe and bewilderment… I kinda want to frame it…

In terms of whether or not it will work for my needs… maybe? I can’t seem to run it at present; I end up with an error message every time I try to hit the button;

mybrainhurts.png

Can you tell me what I’m doing wrong here?

It’s probably worth mentioning that the version of the spreadsheet I sent you was still incomplete, I’ve attached a more complete version to this post.

Really appreciate all the help guys… even if I can’t quite wrap my head around it at present.

Regards

MattRNR

P.S. Does anyone have any idea how to stop a combo box dropdown list from dropping down of its own accord every time I do anything else in the spreadsheet? It’s driving me mental.

P.S. P.S. I’ve only just noticed the ‘Topic Review’ part of this forum (after all these years)…

With regard to the macro’s in the database I guess I’m not really thinking of the ‘Active data for Printing’ table as a database, more of a visualisation of the data selected before the print macro is run, so I thought it would be important to include the clear macros there to selectively remove a tag if need be.

As for how I work with this; I’m putting this together as a favor for a mate in Melbourne in exchange for a slab of ice cold European brewed Heineken, ‘Euroheins’ if you will (because as Australians we basically use beer as currency). His company is wasting several hundred grand worth of paper a year by printing its price tags 1 to a page instead of using all the space on the page. I thought it might be a good way to start learning some basic code… plus I’m an Environmental Scientist so yah know “Save the tree’s man”.

My apologies for not seeing this sooner;

**MattRNR** · 10-18-2015, 08:34 PM

Oh actually I've only just seen the rest of the posts on here, bear with me... I will give this a look over ASAP

**Leith Ross** · 10-18-2015, 11:05 PM

Hello MattRNR,

Beer, mmmm! Aye, raise a pint - slainte math!

Back to the task at hand. You added a CombobBox to this workbook. The workbook I posted used the cell $H$5 as the input. I will need to make a few minor changes to this new workbook to get things working. No worries mate!

**MattRNR** · 10-18-2015, 11:56 PM

Ah yes I apologize, at the time I first post I hadn’t yet added the combo box but I should have mentioned I planned to in my previous post.

To clarify the formulas in the extra columns under the ‘Entry number’ title of the database are required for the functionality of the drop down search suggestions in that combo box. These will be hidden with formatting and locked in the final version so the user can’t modify them. This is based on the method detailed here: http://trumpexcel.com/2013/10/excel-...h-suggestions/

I started combining some of the new code into my most up to date version (attached) I haven’t added the ‘ClearPrintDataRow’ and ‘ClearPrintSelection’ as yet as I can’t work out how they differ in functionality from the originals below?
Sub Clear1a()
Range("C15:C15").ClearContents
End Sub
Sub ClearSelectiona()
Range("C15:C20").ClearContents
End Sub
I also couldn’t work out what ‘ClearTagLine’ and ‘setListRange’ were?

Otherwise this is brilliant, though far more complicated than I initially anticipated I’m really hoping I can work it all out later down the track.

The only issue I’m having with the ‘Add’ code (which I have not yet put in the newest version) is the next blank cell complication I flagged in my message to Hans above; that is it needs to paste in the next blank cell rather that the lowest blank cell as the user may choose to remove an entry midway through the table and replace it with the next search.

You may notice also that the 'add' I used in my most up to date version runs 3 copy macros to copy down the content in the helper cells J7 and J9 as well at the content of the combo box into the active data for printing table.

Cheers again mate, looking forward to seeing this all work

Regards

MattRNR

**Keebellah** · 10-19-2015, 12:49 AM

@Marttrnr Here's the file I uploaded earlier. Project Eomer V33 171017-Hans (colab Leith).xlsm'will attach it again
I was was also wondering about what you wanted to do and started playing around little with what I do and came up with another idea and the result is the second file.
I was wondering aboud the blank lines, no problem there to modify the code and as how you enter / add new products but thought I would come across as too much of a intruder if I would mention a Userform
I moved the PRODUCT_DATABASE to another sheet and made it an actual table, this makes it dynamic and when you add a new row the formulas and so are copied automatically, it's called a listobject.
if you select the entire table A1:O31 you'll notice you've gotten a new menu option in the ribbon

**Keebellah** · 10-19-2015, 01:02 AM

Replace the macro named CopySelectionAdd in the Module DataModule001 (last file named Project Eomer V33 171019.xlsm)
with the code below and you'll insert a row in the blank row

Please Login or Register  to view this content.

BTW I see that Template T3 hasn't been filled yet

**Keebellah** · 10-19-2015, 04:57 PM

I suppose I got carried away with this all.
I think you've got it solved and have your own ideas.
I won't interrupt anymore.

**MattRNR** · 10-20-2015, 05:05 AM

G’day Hans

Nah mate by all means have at it, you guys are the experts I appreciate any input; I’d always rather have more ideas on the table than less.

This new ‘CopySelectionAdd’ macro works perfectly; I’ve modified it slightly as I needed it to copy down even if the checkboxes were unchecked, but otherwise it’s exactly what I needed, can’t thank you enough.

I had intended to generate a user form later down the track to allow adding to the database however haven’t got to it yet, I’m assuming it’s a relatively simple thing to set up, I’ve found a few tutorials online I can work from.

Moving the database to another sheet and making it a dynamic table also seems like a great idea, I reckon I’ll do this as well.

I did notice one issue with the ‘printtemp’ macro; the message box that appears when I hit print indicates the number of items I’ve selected to print;

3tags.png

The number of items to print however is dependent on the template (i.e. the ‘Large’ template can only print 1 tag to a page as that is all the space available on the paper, so when large is selected only the top entry will print) I’ve added a function in the range E15:E20 to indicate which will be print for the selected template.
The message stating ‘You have selected 3 tags to print’ doesn’t fit here as only 1 tag will be print, are you able to show me how to modify this? I can’t seem to separate the code that this works on from the rest of the code.

Other than that; any thoughts on this dropdown list? It annoyingly drops down of its own accord whenever I do anything else in the spreadsheet…

Thank you again

Regards

MattRNR

P.S. I've attached the latest version

**Keebellah** · 10-20-2015, 05:25 AM

Hi,

I did take a look at the tags you were printing and made several changes to the macro.
I'm currently away from my system but what I did was the following:
When you select print you get a message box informing you what it's going to do:
If the template chosen is regular it will print the tags if there is only one the only one shows up. if you added 6 all the six will be printed on the 6-tag lay-out
The other two will print all the 1 through 6 on six different pages since only one tag per page is configured
In these cases if you Answer NO to print it will prompt if you wish to print one only and go though the list (max 6), so you do NOT have to delete the selection before you select to print, you can skip the one you don't want.
The form can be quite simple but I think you'll have to include a check in case you enter an existing code.
In the last version I added the button Add product so the PRODUCT_DATABASE sheet is activated, the you can either add the new product manually or you can use a form when you have it ready.
I won't be home 'til 18:00 Central European time and you'll be almost starting your new day.
I'll send / attach the file when I get home so you can see what I did.
I'll check your attached file and see what you've done and compare it my newer version.
Cheers,
Hans

**Keebellah** · 10-20-2015, 05:29 AM

P.S. I'll check the Dropdown issue too.

**Keebellah** · 10-20-2015, 05:45 PM

Well, it's me again

I looked at the file you attached but could not really find the reason the dropdown list fails, and, honestly, was soem way further with 'my development'and have therefore named is 'Project Eomer V37 211015.xlsm'
I did not add any comments to the vba code and if this really is the way you want it to work some extra actions will have to be added to the macro's to avoid data loss and so.
The main SEARCH sheet now works.
- Dropdown list is dynamically updated should you add new data to the PRODUCT_DATABASE worksheet which I have set to hidden but will be displayed when you press the 'Add Product' button
This button is only visible when the Search field is Cleared.
- Adding tags works like it should and if you clear a row in between, this one will be the first one to be filled the next time you Add a row
- While doing some searching I came across one of your posts on another Forum where you were asking how to select another printer via VBA, I also included this for printing.
When you press the Print button the first prompt is a message box showing the currently selected printer and you can press Yes to change it.
After you change it the same question will be popped just to check. Press NO for the next step.
- Depending on the selected template the print actions will be carried out
- Add Product will open the PRODUCT_DATABASE sheet and display a userform where you can add the data.
The first top 5 fields are compulsory and the first field, Product Code will be checked if it's already present.
When the 5 compulsory fields contain data and the product code is valid the SAVE button will become visible so you can save the data.
After data is saved, the userform is cleared and you can either add a new record or EXIT.
Selecting the SEARCH sheet will again hide the PRODUCT_DATABASE sheet
Give it a run and see if it works for you.

The PRODUCT_DATABASE is a Table in the strict sense.
If you select a cell inside thsi table (Columns a through O) and between Row 2 and Row 32 then if you press the right mouse button you can insert a row and the extra option there is tanble row below above.
The new cells will all contain the formulas with are in columns A-E, this is one of the advantages of using tables.
You should Google Excel working with Tables or ListObjects to see how this works.
It's quite interesting and makes life quite a lot easier

It's my bedtime (23:44) now while your starting the day (08:44) and by the time I wake up your day is about over.

Enjoy the file an VBA really is fun

**Keebellah** · 10-21-2015, 02:42 AM

Hi, Goodmorning (for me 08:41)
It's 17:10 on your side of the globe.
Did you have a chance to take a peek?

**Keebellah** · 10-21-2015, 07:39 AM

Just something for waking up

I'va attached a pdf file explaining how the changed file works and I also attached the 'Project Eomer V37 211015.xlsm' with some minor corrections.
Enjoy

**MattRNR** · 10-21-2015, 05:58 PM

G’day Hans

My apologies for not getting back to you yesterday, work ended up running long and by the time I got home I was too knackered to do anything.

This looks brilliant, I’ve only had time to look at the print function so far but its working beautifully, I’m off work today so I should have some time later to get my head around the other modifications.

I’ll let you know how I go ASAP…

Good point re my original question being solved, I will use your email.

Thank you again

Ben and Leith thank you both so much

I realize this post became a bit of a marathon in the end, so I really appreciate the effort you’ve all put in to get this one sorted… I would be lost without this forum.

Regards

MattRNR

**Keebellah** · 10-22-2015, 12:45 AM

Enjoy your day off

Be hearing from you.

Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Re: Macro to copy and paste new data into the next blank cell WITHIN a range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Copy / Paste range from one sheet to another, if cell in column A is not blank

[SOLVED] Need VBA Macro to Copy and paste column data in blank rows and repeat through spreadsheet

[SOLVED] Paste Special into next blank cell from Range of Column e.g. B2:CP2 - Macro Code

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions