Investor · ST. Augustine, FL · Member since 2013 · 92 posts · 31 votes
I need a little help building a quick and easy macro, and im having a tough time (im no programmer). Basically it works like this:
Sheet 1 is a list of 10,000 property addresses and other info.
Sheet 2 is a template .enex file i exported from evernote.
What I want to do is create a macro that copies the template into sheet 3, then auto populates the info from each row of sheet 1 into a row on sheet 3 with the pre created template.
This way I can make my 10,000 properties individual notes to be uploaded into evernote.
I can pretty much create the macro to do one template, but I dont understand how to look it and make it go through the entire list from sheet 1.
You can record the macro procedures to do what you want, which sounds like some copy and paste event type thing. Then you can place those instructions in a loop that searches tests for the last record in the sheet.
Here is one example which searches for the an empty cell to determine if the loop should continue. This will allow the macro to be dynamic and work in a sheet with 10 or 10,000 data lines.
Sub ReportMaker()
Range("A2").Select ' Select cell with first set of data only replace inside the " "
Do Until IsEmpty(ActiveCell) ' Set Do loop to stop when an empty cell is reached.
' Insert your code here.
ActiveCell.Offset(1, 0).Select ' Step down 1 row from present location moving to the next record. If the records are multi line, increase the "1" by number of rows. This will require the data to be formated in terms of rows to be similar.
Loop
End Sub
You can create some code to automate the Evernote export but that might be a little overkill for what you are doing. That code is a bit more advanced and long. For the sake of simplicity, I would simply export the Evernote file into excel or import into excel manually.
You should be able to just RECORD your steps and that creates the macro. Within the record feature you will just have to go through all of the steps on sheet1. Then just add a macro button.
You can record the macro procedures to do what you want, which sounds like some copy and paste event type thing. Then you can place those instructions in a loop that searches tests for the last record in the sheet.
Here is one example which searches for the an empty cell to determine if the loop should continue. This will allow the macro to be dynamic and work in a sheet with 10 or 10,000 data lines.
Sub ReportMaker()
Range("A2").Select ' Select cell with first set of data only replace inside the " "
Do Until IsEmpty(ActiveCell) ' Set Do loop to stop when an empty cell is reached.
' Insert your code here.
ActiveCell.Offset(1, 0).Select ' Step down 1 row from present location moving to the next record. If the records are multi line, increase the "1" by number of rows. This will require the data to be formated in terms of rows to be similar.
Loop
End Sub
You can create some code to automate the Evernote export but that might be a little overkill for what you are doing. That code is a bit more advanced and long. For the sake of simplicity, I would simply export the Evernote file into excel or import into excel manually.
The excel functions VLookup or alike would not work for the desired application. Those functions only return the value of the looked up cell and does not create a loop of any kind.
Wow, I thought I was pretty tech saavy, but this is beyond what I can do. Thanks for the help guys, going to have to find someone that can program it for me.