Macro Help

[jhculbert]

I have found out how to create a macro in the hopes of moving data from one column to several columns, however, after creating the macro, moving the data item in one set, stopping the recording, and then executing the macro, it does not work. How do I do this? Attached is a screenshot. I do not know the process to create a mini-sheet.
To explain the screenshot: The downloaded data is in column A. I want to move cell A41 to cell B39, A43 to C39, A45 to E39, A47 to F39, A49 to G39, and A51 to H39, the delete cells A40 to A52.
The deletion brings the next set of passenger data up so that the correct given name is in cell A40. I would then execute the macro again to rearrange the next set of passenger data.

 

[Wookiee]

Hello and welcome to Mr Excel!

Please give this code a try. It assumes that you have a list of imported information in Column A where there is a blank row in between each data element. This will drop down to the first row where the data is broken up like that and loop through all the records, writing them to the horizontal layout you described.

Sub CulbertReport()
'Crafted by Wookiee at MrExcel.com


Dim rngRecord  As Range
Dim lngRow     As Long

'Determine The Row Of Current Imported Record
lngRow = Range("B6").End(xlDown).Row + 1

Loopsy:
Set rngRecord = Range("A" & lngRow & ":A" & lngRow + 13)

Range("A" & lngRow) = rngRecord.Formula(1, 1)
Range("B" & lngRow) = rngRecord.Formula(3, 1)
Range("C" & lngRow) = rngRecord.Formula(5, 1)
Range("E" & lngRow) = rngRecord.Formula(7, 1)
Range("F" & lngRow) = rngRecord.Formula(9, 1)
Range("G" & lngRow) = rngRecord.Formula(11, 1)
Range("H" & lngRow) = rngRecord.Formula(13, 1)

'Continue To Collect Data From Subsequent Cells
If Not IsEmpty(rngRecord.Offset(14).Resize(1)) Then

  lngRow = lngRow + 1
  rngRecord.Offset(1).Resize(13).Delete Shift:=xlUp
  GoTo Loopsy

Else

  rngRecord.Offset(1).Resize(13).Delete Shift:=xlUp
  
End If

Set rngRecord = Nothing

End Sub

 

[jhculbert]

Wookie,
Hello, and thank you for your reply.
I'd love to try this, but have no clue what to do with this code.
Please advise.

 

[Wookiee]

Sorry about that. Allow me to explain the mechanics of the macro.

In the workbook where your data is saved, open the VB Editor (ALT+F11)

Right-click on the Modules folder, then select Insert, then Module

Copy the VBA code from my previous post into your module

Save the file. In fact, I suggest saving a testing copy before you run the macro so that you can make sure it works as intended with your actual data.

When you want to run the macro, activate the sheet with data that needs to be cleaned up. Press ALT+F8 to run a macro, choose This Workbook then select CulbertReport and click Run.

 

[jhculbert]

Thank you! I was able to insert the code, and run the Macro, but it screwed everything up.
The primary problem is the data in the last coumn is now in the first column, but that is not the only issue.
Good thing I did this on a copy of the data, and not the original!

I think part of the problem is I already had some of the data correctly sorted at the top of the file when I ran the Macro, so it tried to sort that data as well.
The Macro scrambled that data also.
Too bad there isn't an undo feature to this.
I'm going to try it again with just the unsorted data, right now before I post this reply, and then also report those results here......

Unfortunately, after removing the already sorted data, and just running the macro on the unsorted data, now I get a run error, and have no clue how to debug.
Attached is a screenshot of the error info.
Thanks.