Extracting specific data from one spread sheet, but editing the data at the same time, then repeating the process to a third one


New Member
Jul 2, 2020
Office Version
  1. 2010
  1. Windows
Hi everyone! I just started learning VBA excel coding. VBA excel for dummies and tutorials have helped a lot but I am still stumped on how to create a macro to do everything in one go. Right now, it takes me about 1+ hours to manually transfer data even with the few formulas and codes that I have been able to create to assist. I need to make it auto populate as much as possible to not only reduce the amount of time involved but also so that my co-worker who covers me when I am away can do it, as she is not as tech savvy.

We get data from one of our instruments that we manually transfer into a spread sheet (“Case Load Report”; Fig.1). I need to pull specific data for my techs (highlighted in green in Fig. 2) and paste into another spread sheet (“HZ report”; Fig.3). After the data is double checked (sometimes there is no case pathologist provided) I then take specific data from that spread sheet (highlighted in blue Fig.4) and paste it into a third spread sheet for my pathologists (“Path Case Distribution”; Fig.5)

The 1st transfer process gets even more complex as I also need to auto edit the ID# data when it is transferred to the tech sheet (“HZ report”; Fig.3). Each case ID (Ex: SP-20-12345) has one or more parts to it represented after the “.” (Ex: part one = SP-20-12345.1; part two = SP-20-12345.2, etc.) with samples of each part represented by letters (Ex: SP-20-12345.1A; SP-20-33456.11.BC). I need to extract just the alpha characters after the “.” for each ID # (Ex: changing SP-20-3945.1A to SP-20-3945.1; SP-20-4008.1AB to Sp-20-4008.1) and then get a count of each duplicate in order to get a total block count for each part of the ID# to reduce the amount of paperwork as the original way is too much (Example of completed sheet Fig.6).

I have only been able to figure out how to remove everything after the “.” with the below formula.

=IF(LEFT('Case Load Report'!A8,10)="","",CONCAT(LEFT('Case Load Report'!A8,IFERROR(FIND(".",'Case Load Report'!A8,1)-1,""))))

I have tried the Pivot Table but I could not get that information to work with my next transfer process.

Once everything has been double checked in the “HZ Report” spread sheet, then it gets even more confusing. I wanted to create a button that would start the next transfer process. It needs to auto edit the ID# data again to only the case ID# (EX: from SP-20-3945.1 to SP-20-3945) and also give me an overall total block count for that case by counting the duplicates, but then also do a search to locate the column for a specific pathologist and then paste the edited information in their section (Ex: Fig.7).

Besides removing all of the cassette information I have no clue on how to go about doing this part, or if it is even possible with VBA.

I defiantly picked a big project to start off with as a beginner so any suggestions would be great or if you know of any tutorials that would help I would greatly appreciate it!


  • FIG.1.png
    85.2 KB · Views: 10
  • FIG.2.png
    107.1 KB · Views: 8
  • FIG.3.png
    24.8 KB · Views: 7
  • FIG.4.png
    21.6 KB · Views: 7
  • FIG.5.png
    24.7 KB · Views: 6
  • FIG.6.png
    11.3 KB · Views: 5
  • FIG.7.png
    19.3 KB · Views: 5

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.


MrExcel MVP, Moderator
Jun 12, 2014
Office Version
  1. 365
  1. Windows
Glad we could help & thanks for the feedback.

Watch MrExcel Video

Forum statistics

Latest member