LabRadar macro to combine report data with individual shot data

[JCCinOhio]

I got tired of having to go in and open each shot track separately so I wrote a short macro that will copy/paste all the individual shots into their individual sheet within the main sheet. So the first sheet will have the usual shot data (average, max, min, etc), second sheet will be Shot 1, third will be shot 2, and so on. I’ve tested it to double digit shots, and it works fine. Should also work for 100+ shots, but would be pretty cumbersome at that point.
To use it:

• Copy from ‘Sub’ to ‘End Sub’ below.
• Open the report sheet (like “SR0003 Report), make sure you are on the sheet with the report data. Note: TRK folder has to be left as subfolder for this to work.
• Hit ALT+F11 (or Function+Alt+F11 if you don’t have dedicated function keys like on a laptop) to bring up the visual basic editor.
• On the menu bar, go to ‘Insert’ and hit ‘Module’.
• Paste what you copied into the module.
• You should see a green ‘play’ arrow in the toolbar, click that and it should Run. Or you can go to Run on the toolbar and hit ‘Run Sub’.

Make sure you save the new workbook as an Excel Workbook because *.csv doesn’t allow multiple sheets, and all the data will be erased except for 1 sheet. Or, if you know you are going to shoot more (add more shots to that report) then you can save as macro enabled workbook and you can use the macro again on the added data.
Hope this helps someone else…
John

Edit.. changed "Range("a1:d150").Select" to "Range("a1:d350").Select" so it will copy all the data over.


Sub combine_it()

Dim zz As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False

num_pages = Cells(4, 2)

For n = 1 To num_pages

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Shot " & n

Next n

the_path = Application.ActiveWorkbook.Path

For m = 1 To num_pages

a = Len(m)
If a = 1 Then zz = "000" & m
If a = 2 Then zz = "00" & m
If a = 3 Then zz = "0" & m
If a = 4 Then zz = m

Dim y As Workbook
Set y = Workbooks.Open(the_path & "/trk/shot" & zz & " Track.csv")

Range("a1:d350").Select
Selection.Copy

y.Close True

Worksheets("Shot " & m).Activate
ActiveSheet.Cells(1, 1).Select
ActiveSheet.Paste

Next m

Application.DisplayAlerts = True
Application.ScreenUpdating = True

msgbox("Done")

End Sub

 

[JCCinOhio]

There was some confusion on another forum on what the macro does.. see pics below.

Also.. For people that find this useful.. Once you paste it into the module sheet, go up to 'File' and hit 'Export file'. Save it to a place you remember, and name it something appropriate (like LBR Macro). Then next time you want to use the macro, just hit the ALT+F11 (or whatever you did to open the editor), go to 'File', and hit 'Import File', open the macro you saved, then hit run.

 

[waveslayer]

Can you translate for someone like me the, your first post you did

My wife thinks I only have 3 guns

 

[JCCinOhio]

You have to have Excel loaded on your computer I believe.. not sure if it will work with Excel Online.

1. Copy from 'Sub combine_it()' all the way to 'End Sub'..
2. Open one of the Labradar output files (the one that looks like the first pic above, with the Average data in it)
3. Hit ALT+F11 (or Function+Alt+F11 if you don’t have dedicated function keys like on a laptop) to bring up the visual basic editor.
- if something else other than a window that says 'Microsoft Visual Basic' in the upper left comes up you might have hit the wrong buttons, or you don't have excel. See pic
4. On the menu bar at the top, go to ‘Insert’ and hit ‘Module’. see pic..
5. Paste what you copied into the module, the big blank space on the right.
6. Hit the green ‘play’ arrow in the toolbar, click that and it should Run. Or you can go to Run on the toolbar and hit ‘Run Sub’. See pic
- don't click outside the module before you hit run, or it won't run... just paste the module.. and click the arrow..

 

[Lowlight]

Can you make a template, and email it, I will save it to a public location and that way people can download it and run it ?

That might help with the confusion for the non -Excel users in the groups

 

[Lowlight]

Here are the links to the Labradar Macros

LabRadar Macro

LabRadar Macro .bas file

Labradar Macro Instructions

 

[flynbye]

My computer skills suck

 

[dylanmj]

This is awesome! Thank you for putting this together!

 

[JCCinOhio]

Made a small oversight... already fixed it in the main original code at top.. If you have already saved the macro, change "Range("a1:d150").Select" to "Range("a1:d350").Select" and save/export it again, that will make sure it copies all the way to 130+ yds if your data goes that far. Sorry for adding to the confusion..

 

[missed]

Subscribed to come back later