Hi all, I am total newbie to Excel VBA but do know programmig a bit, just not any .NET/VBA stuff. I wish you can help me with this problem I have. I tried to search from old topics but was not able to find anything to suite my purposes.
We have tool(s) that generates reports as Excel sheets. So far we have combined the data manually, but now we want to automate the procedure a bit. Eventually we want to read data directly from the original Excel files into final-report table, but that can wait. Right now we just create manually new Excel file and copy/paste data there from multiple sources. This data is put so that one source = one sheet and before making report we need to combine it to the collection sheet.
First problem is how to copy entire column starting from some specific cell untill end of the Excel table (sheet one), and paste it to another sheet (sheet three). We do not know beforehand how many rows we have, it changes, and there may be empty cells in the middle.
Example: We have person names starting from column A10, where A10 is title and from A11 first name can be found. Name is like: Lastname Firstname, but like I said we do not know how many names we have totally and it can be also blank. We can assume that if name is left blank but we are not at the last row of the table there is more to come. We do not need to copy titles.
After we have copied all names from sheet1 we need to do same for the names from another sheet and put them into end of the names we just copied. So if we just copied names and last is in the cell A67, next name from another sheet comes into cell A68. This should be simple but for some odd reason the format of the name is now different. Now it is like: Fistname Lastname, so we need to change it to Lastname Firstname before we again copy the whole column and paste it into collection sheet.
Edit: after a bit of more head banging I realized that even if name is not mandatory we do have other fields that are:
We reduce one since last row is summary and we do not want to copy that one. Now I know first and last cell in column, since first is fixed. Next I need to figure out how to select this area and copy/paste it.Please Login or Register to view this content.
Bookmarks