New to Macros: Automatically Populate Data to Different Spreadsheet

I have extremely limited macro knowledge, literally learned about a week ago from watching various youtube videos and reading through forums. It feels like Excel can accomplish anything, however I was wondering if it’s possible to automate data to populate to a different formatted spreadsheet. Here is what I’m trying to do:

• I export raw data from a program but it is too messy with extra columns or useless information, it needs a lot of cleanup (let’s call this RawSpreadsheet). I managed to record a macro in a new spreadsheet (CleanupSpreadsheet) that will clean it up and sort it alphabetically.
• I then copy the output and paste into my formatted spreadsheet (FormattedSpreadsheet). However, does excel have functionality for the raw data to automatically populate into the formatted spreadsheet?

I feel I’m still having too many steps by copying data from RawSpreadsheet, pasting to CleanupSpreadsheet, and after the cleanup pasting again to the final FormattedSpreadsheet.

Is it possible to record a macro and when I open RawSpreadsheet I just enter a shortcut that will automatically clean up the data right from that spreadsheet and then automatically enter it to FormattedSpreadsheet?

To make things more complicated, every time I export raw data the spreadsheet's name will always change, so I wouldn't know how to create a macro in which the name will always be different. Can any of you point me in the right direction of what I need to learn to make this possible? Is it possible?

Yes it can be done. You can also put your code in your personal file that is always available to you and run the macro from there. Once you have a working macro, you can assign a shortcut, like ctrl+t to run it, too. I f you can share your raw spreadsheet and your formatted spreadsheet we can assist you in writing that code. Follow the instructions in the yellow banner above.

Hi!

Thank you for the response, I'm attaching a sample of what I'm working with. The only thing about the RawSpreadsheet is that it will always change name and based on the limited macro knowledge I have, it seems you always need to enter a source reference in the macro. How would I do that if I don't know what the raw data export will be called?

Here is what I'm trying to do:

1. • Export applicant raw data.
   • Sort out unnecessary fields.
   • Manipulate some entries (change answers to a simple 'Y' or 'N').
   • Sort alphabetically by applicant name.
   • Paste data into formatted spreadsheet, specifically only: Candidate name, Date applied, Veteran, Foster Child.

My spreadsheets are very primitive as it was my first try at recording and editing Macros. I'm sure there are better and more efficient ways of obtaining the results I need, and I'm looking forward to learning!

We can create the macro to request one find the raw file. Is the raw file always in the same folder?

It automatically saves to the 'Downloads' folder.

This is amazing, thank you so much!! I will definitely break down the code and compare to what I previously recorded so I can learn more about Macros.

If that takes care of your original question, please select*Thread Tools*from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

I will make sure to do so! I actually have another question. I realized I made a mistake in my original cleanup and I was supposed to remove column titled 'require visa sponsorship'. I believe that changes the code to:

Rows("1:7").Delete Shift:=xlUp
Columns("B:J").Delete Shift:=xlToLeft
Columns("C:R").Delete Shift:=xlToLeft
Columns("E:Q").Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Replace What:="I am not a Veteran", Replacement:="N", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="Veteran", Replacement:="Y", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("D:D").Select
Selection.Replace What:="Yes", Replacement:="Y", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="No", Replacement:="N", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Rows("1:1").AutoFilter
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range _
("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveSheet.AutoFilter.Sort
.Header = xlYes
.Apply


I am still trying to figure out the rest on how it copies to the Formatted Spreadsheet, I will need to populate the cleaned data as follows:

• Cleaned Data, Column A to Formatted spreadsheet, B25 (and on)
• Cleaned Data, Column B to Formatted spreadsheet, C25 (and on)
• Leave Formatted spreadsheet, D25 and on blank
• Cleaned Data, Column C to Formatted spreadsheet, E25 (and on)
• Cleaned Data, Column D to Formatted spreadsheet, F25 (and on)

I'm realizing recording a macro adds alot of extra code in the equation and that's why it seems so confusing at first. The code you created seems simpler, however, I have yet to learn the rules on how everything is separated and what can go together and what can't.