LinkBack URL
About LinkBacks
I would like to create a macro that will copy the data in the current active cell into all rows bellow as far as the last row with data in column A (that way it stops at the last record).
Thanks,
James
I would like to create a macro that will copy the data in the current active cell into all rows bellow as far as the last row with data in column A (that way it stops at the last record).
Thanks,
James
Try something like this:
Make sure the cell of interest is selected before running the macro
Please Login or Register to view this content.
------------------------
W2
Hi, Cofad,
ActiveCell as well as Selection?
Ciao,Please Login or Register to view this content.
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
Good catch. I guess I changed it during my testing and didn't notice. Much more succinct code you provided.
Hi, Cofad,
make a Selection in 2 or more cells directly below each other with different values and run your code - result should be #NA in all other cells except the Selection. If you replace Selection with ActiveCell there should be the expected result.
Ciao,
Holger
It worked, however I have a fill color over most of my rows, and it appears to be messing with it (as it considering a cell with a fill color as a cell with data in it). Could be be changed to say require a number to be in the A column, that way it will ignore the background coloring?
Thanks
Hi, James,
Not what I would normally expect from a fill colour.as it considering a cell with a fill color as a cell with data in it
If this wonīt work please add a workbook as attachment to have a look at - I assumed here that there are numeric values in Column A. No error will be raised but no result as well if there arenīt any cells found in Column A.Please Login or Register to view this content.
Ciao,
Holger
It still doesn't seem to work. If you dont mind taking a look for me i would be much appreciated. The macro is called DuplicateData, and I have it set to a button on the ReturnData Sheet (at the top, labeled Duplicate Data). If you go to the last row on that page you will notice rows 326 to 334 are blank in the E column. I would like to be able to select the last cell that says 'Return' and press the Duplicate Data button, so that it copies this in all the rows below and stops at the last record (currently row 334).
Also if you have the time would you mind looking into another issue im having. The whole spreadsheet seems to have been acting very slow and sluggish recently. For example if you enter 11198 into the next available 'Serial number column (G), it takes quite a long time to do its action (find the item name and enter todays date). It didnt used to be this slow, is there anyway i can speed it up?
Many thanks,
James
Hi, James,
please give this code a try:
As soon as you enter any value all your formulas will be recalculated which takes itīs time - maybe you limit the number of filled formulas in Sheet Return Data to be 1, 5 or 10 cells in advance and not go down to 4980 as it is right now (actual youīre in Row 334). You could use an event to do so on every entry.Please Login or Register to view this content.
I donīt know if you need all the formulas to be active, maybe there is either a time or a return so that the formulas for those cells could be pasted as values thus reducing the number of formulas beíng calculated.
Ciao,
Holger
That formula worked, however I would like to use the same macro button to do the same thing for the H column. Thats why I would like to to the selected cell and work down form that (that way I can use it for both by just selecting the appropriate cell).
With regards to the speed issue, this is just my sample worksheet. The actually worksheet will create about 10,000 to 15,000 entries in the next 6 months (thats what im preparing for). I like the idea of having formulas for those rows being added as records get created (if im understanding you correctly). How would I do that?
Hi, James,
regarding the code, get the last used row in a variable and use that for the upper row to copy to:
I donīt know if you have a Database on your computer but I might prefer to use a RDBMS for that kind of job to do which will offer features Excel canīt due to the fact of being a spreadsheet.Please Login or Register to view this content.
Regarding the adding of lines with code that may be done after filling up the columns E and H while adding:
In your case you should usePlease Login or Register to view this content.
for the just shown sniplet as the formulas reach down way beyond where the actual entries are made. If you drop both the formulas as well as the Conditional Formatting to suit the area used and maybe run the codes without raising the event to recalculate you may save some time.Please Login or Register to view this content.
Ciao,
Holger
Its not quite what im looking for. I have attached my worksheet. On the ReturnData page, if you go to the last row (316) you will see in the E column there are some empty cells below 306. I would like to be able to select E306 and click on Duplicate Date (button at the top) and duplicate the entry in the active cell to all cells below to the last record (311) but without overwriting cells that already contain data (309 and 310). It should work independently to the H column, so it would require the user to then select H301 to do the same in that column.
The module is called DataDuplicate.
Thanks,
James
Hi, James,
this code assumes that the cursor will be set into the last thoroughly filled cell in Column E - if you want to place the cursor there by yourself omit the codeline doing the Select:
Ciao,Please Login or Register to view this content.
Holger
It doesnt seem to be working independently, it still copies the data in both E and H. It also only copies the data to the row below, not all the records below. So basically I could just click on one cell either in Column E or H and when I run the macro it then copies the data in that cell to all the rows below that are empty. If there is data already present in any of the cels below then it doesnt over write it, it just moves on to the next one. And it should only to one row at a time (the row with currently has the active cell).
Thanks,
Hi, James,
okay so we have to make a split for only certain columns to let the code work on:
Ciao,Please Login or Register to view this content.
Holger
That split it up so that it only duplicates the data in the E or H column with the active cell, however it still only duplicates it to one row below, I would like it to duplicate it to every row below (all the way to the last record).
Hi, James,
I can only test with my components (Windows 8 and Excel 2013), and the code I supplied works fine with that combination - for all empty cells in the respective row. You might need to add On Error Resume Next in case that no empty cells are available.
Ciao,
Holger
sorry, I dont know why but it seems to be working perfectly now (not sure why it wasn't yesterday). Thank you very much.
Hi, James,
if you were using a Windows-based system Iīd say that a reboot was the reason for that - glad if the command works by now. The other way of solving this issue would be to loop through the range in each column, check if the value of that cell is "" or vbNullString and then enter the value which might take a little longer to perform.
Ciao,
Holger
Sorry Holger I just discovered a small bug with the code. If I have more than one record with empty cells that the data is being copied to then it works fine. However if there is just one record below with an empty cell then it doesn't stop at the one record, it seems to copy it to every empty cell in the sheet.
Hi, James,
code works fine with only one cell on my windows system, no further copying down below the last filled row from Column A. An error will definitely occur if there are no emtpy cells in the area to fill up.
Ciao,
Holger
Thats strange. Do you mind trying it on the attached workbook. For me if you click on cell E317 and then select Duplicate Data at the top (to run the macro) it enters Return into every empty cell in the workbook from Column A to O and rows 1 to 944.
Hi, James,
regarding the range to filldown: thatīs due to the formula which is different from the one I answered to (or: the basic one I thought you had altered). You should settle down to use one formula throughout.
Ciao,
Holegr
Sorry its taken so long to get back (been out of town for a few days).
Im not sure Im following you, am I doing something wrong?
Hi, James,
you have different formulas in Column A in the workbooks in this thread, and they deliver quite different results.
Ciao,
Holger
It still has the same affect if Column A forumla is:
=IFERROR(IF($D7="","",INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1),"")
or:
=IFERROR(IF($D7="", " ", $A6+1), "")
I tried changing the following line so it references the serial number column to find the last row instead:
lngLastRow = Evaluate("=MATCH(0," & .Range("G:G").Address & ",-1)")
but that didnt work either.
It basically seems to be unable to find the last row if there is only one row left. when there are two or more rows it finds the last row no problem.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks