PDA

View Full Version : Macro to copy non-blank cells in 2 columns and paste values into first blank cells in



turkishjim
04-12-2017, 11:59 AM
Sorry: ran out of space in title. Need to paste in first blank cell in a range in another workbook. Apologies if this has been asked and answered before, but I couldn't find it.

I have a spreadsheet recording my stock, and I want to copy data from 2 columns in my invoice and paste special into the first blank row in my stock sheet.
It seems to me that I need to do the following:

Identify non-blank cells in the source range
copy these
identify the first blank cell in the target range
paste values there


I am confident with Excel but a total beginner with VBA. I suspect I might be able to find ways to do at least a couple of these steps, but even if I could I have no idea how to string things together. Any help or pointers would be much appreciated. TIA:dunno

p45cal
04-12-2017, 05:05 PM
Will both cells on a row always both be filled-in, or both blank? That is, will there ever be a blank cell and a filled-in cell on the same row in the 2 columns? Are the columns next to each other? Are the column letters the same in the destination range. Is the source range on the same sheet as the destination range.
Just a few of the questions that would instantly be answered if you were to provide a sample file.

turkishjim
04-13-2017, 03:39 AM
p45cal,

Many thanks for taking the time to reply.
If I can manage it I'll attach a sample workbook I've just cobbled together. It has 2 worksheets called invoice and stock_new: in real life stock_new is just one of the sheets on a separate workbook (always open when the invoice workbook is open).
What I need to do is copy whatever cells are not blank in the range B15:C28 on the invoice (and there will never be an entry in column B in this range without a corresponding entry in column C). I then need to paste the values of these cells into the first blank cell in column C in the stock_new worksheet.
You can see that the source range is static while the target range is dynamic: always the same sheet but moving down the rows as orders are shipped.
I hope that is clear but please feel free to ask if it is not.

Thanks in advance.

Jim

p45cal
04-13-2017, 06:38 AM
Significantly more involved than expected due to merged cells, cells which aren't really empty (they contain conditional formatting and #N/A) and trying to make it robust.

Here's the beginnings of some trial code:
Sub blah()
Set Qty = ActiveSheet.Columns(2).Find(what:="Qty", LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
If Qty Is Nothing Then
MsgBox "no cell in column B containing ""Qty"", exiting…"
Exit Sub
Else
Set myrng = Intersect(Qty.CurrentRegion.SpecialCells(xlCellTypeConstants, 1), Columns(2))
' myrng.Select
myrng.Areas(1).Resize(, 2).Copy
Application.Goto Sheets("stock_new").Cells(Rows.Count, "C").End(xlUp).Offset(1)
End If
End Sub
It currently starts by looking at the active sheet, looks for Qty in column B, it then takes the current region of that cell (what gets selected when on the spreadsheet you select that cell, press F5 on the keyboard, click Special…, and choose Current Region, and click OK), it then takes the plain numeric constants (not formulae resulting in numbers (what you get when you press F5 as before and choose Constants and tick only the Numbers checkbox), then it takes only column B cells of all that, which has (or not) multiple non-contiguous ranges, and takes only the first of these, widens it to 2 columns, copies that into the clipboard. Then it jumps to the expected destination.
Were you to press Enter on the keyboard at this stage the data will be copied to the selected cell. If instead, you switch back to the invoice sheet you'll see the shimmering outline indicating whch cells have been copied to the clipboard.

So the types of further questions which arise are:

Will there ever be any gaps in the invoice list (easy to imagine there could be, either for aesthetic purposes or because an extra item or two was entered by mistake and you've deleted them).
I'd readily guess you don't want the discount line to be copied - but will/could that discount line(s?) appear elsewhere in the list?
Can the list be longer than it is now (would you make room for more items by inserting rows into an invoice which needs it)?
Are there going to be multiple invoices that you will be working on (so the sheet may not always be called invoice)?
Which workbook are you putting the code into?

I can handle all of these but may not need to! (I could, for example, just start by using a hard-coded range: Sheets("invoice").range("B15:C28")
or ("B15:C29")??

Am I right in guessing that you have other code in the real workbooks?

Anyway, a start.

turkishjim
04-13-2017, 07:30 AM
So the types of further questions which arise are:

Will there ever be any gaps in the invoice list (easy to imagine there could be, either for aesthetic purposes or because an extra item or two was entered by mistake and you've deleted them).
I'd readily guess you don't want the discount line to be copied - but will/could that discount line(s?) appear elsewhere in the list?
Can the list be longer than it is now (would you make room for more items by inserting rows into an invoice which needs it)?
Are there going to be multiple invoices that you will be working on (so the sheet may not always be called invoice)?
Which workbook are you putting the code into?

I can handle all of these but may not need to! (I could, for example, just start by using a hard-coded range: Sheets("invoice").range("B15:C28")
or ("B15:C29")??

Am I right in guessing that you have other code in the real workbooks?

Anyway, a start.

Wow! Thank you so much for your work so far. I'll try your code shortly, but in the meantime I'll try to answer your bullet points.

There will never be gaps.
The discount line is static, and I deliberately excluded it from my range as it does not affect stock levels.
I wish! The average order uses up to 6 lines and I would happily manually copy data across for any order where I had needed to add extra lines.
Again, I wish. I only work on 1 order at a time so that I don't get confused.
Very good question: see my further thoughts below.


Not being a VBA expert I set things up with all my data spread across a number of sheets. One sheet is the invoice template, and I have 3 macros in my personal notebook which do the following:

Copy the invoice sheet to a new workbook.
Copy the latest invoice number from the accounts sheet of my master workbook and insert it in cell F5 of the new workbook.
Once I have completed the invoice (I fill in columns B and D, VLOOKUPS populate columns C and G, and simple formulas complete the other relevant cells) another macro copies columns B to H and pastes in situ as values, and then deletes columns I to L where my VLOOKUP data is stored.
A final macro saves the worksheet to my accounts folder using the formula in cell J3 (previously N3) =CONCATENATE("Invoice",F5).


I suspect that my macros are creating the #N/A in what I had thought were blank cells in column C of the invoice until I looked at it now after seeing your reply.

In answer to your last point, the hard-coded range("B15:C28") will always apply.

If I have unrealistic expectations about the "doability" of this, then please don't waste too much time on it and just tell me the plain truth! I am retired from the day job and just run my business almost as a hobby, so I can easily find the time to do things manually. The only reason I raised the query was because I was so pleased with being able to create the first macros that I wondered how much more I could automate things.

Thanks again,

Jim

p45cal
04-14-2017, 01:36 AM
Email sent to sales@…