XLS Macro to move data to columns, delete empty rows, delete duplicate columns

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

angelostudios

Thread Starter
Joined
Aug 25, 2011
Messages
18
I started out with the two previous posts about macros (post 1, post 2) that move rows into columns, but I'm having a little trouble getting it to work for what I need. First, I'm trying to do a data merge into Word, but in order to get the data merge to work/look the way I want it to, I need the XLS data to be in columns.

I'm attaching an example file that has one worksheet of the sample data (though my "real" data has about 300 rows) and the other worksheet is an example of the outcome I need.

Is there anyway I can do what I need? Exporting into another file or another worksheet within this file would be fine.

Thanks in advance.
 

Attachments

Joined
Aug 25, 2011
Messages
4
Is there any way I can get the student ID numbers - you can change the names if you would like but if you could post the orignal report with the names hidden i might have a solution. Hopefully it works.
 

angelostudios

Thread Starter
Joined
Aug 25, 2011
Messages
18
I will attach the original export in just a minute. The only issue I can see with what you're thinking is that this export will be done each month and the student IDs will change assuming that new children are enrolled, and some may leave. The other issue is that in my example attached above, there were only a weeks worth of information, but we will likely have a months worth in actuality (so 20 rows per student give or take). We will need to export the data each month and have the macro "clean up" the data to use with data merge.
 
Joined
Sep 4, 2003
Messages
4,912
This macro should work assuming that your data layout is the same as the latest workbook posted. The macro sorts by student ID before arranging the data so just make sure to re-sort however you like after finished. The macro also copies the sheet into the same workbook so that the orignal data is preserved

Code:
Sub CopyRows()

    ActiveSheet.Copy After:=Sheets(Sheets.Count)
    Cells.Sort Key1:=Range("A2"), Order1:=xlDescending
        
    For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
      
    Do While Range("A" & i).Value = Range("A" & i - 1).Value
    
    vLastCol = Left(Columns(Cells(i, Columns.Count).End(xlToLeft).Column).Address(0, 0), 2 + (Cells(i, Columns.Count).End(xlToLeft).Column < 27))
    
    
    
    Range("E" & i & ":" & vLastCol & i).Copy Destination:= _
    Cells(i - 1, Cells(i - 1, Columns.Count).End(xlToLeft).Column + 1)
    
    Range(Range(vLastCol & 1).Offset(0, 1).Address, Range(vLastCol & 1).Offset(0, 6).Address).Value = Range("E1:J1").Value
    
    Rows(i).Delete
    
    Loop
    
    Next i
    
End Sub


Rollin
 

angelostudios

Thread Starter
Joined
Aug 25, 2011
Messages
18
Thank you Rollin! It worked great! I appreciate this so very much! It makes my life so much easier. I don't want to ask too much, but is there a way for us to duplicate the header column cells E1-J1 across the top? It would be great if we could change the name to add a sequence like:

Punch In Date | Punch In Time | Punch In Date 2 | Punch In Time 2 | etc etc

It doesn't matter if it is done too many times (like do this 30 times across the top) because I only need it for the data merge later.

Thanks again!!!
 

angelostudios

Thread Starter
Joined
Aug 25, 2011
Messages
18
Thanks for the updated code. It seemed to add the titles when I used my dummy file (attached above) but when I used the REAL data, it did not work. It may have something to do with converting the almost 1500 rows of data down to 150 rows. I read somewhere that when a loop is so big, you may have to stop and save along the way. Is that right?

Also, when I look at the times on the spreadsheet they show up like "10:15 AM" but when I put them in my document they show up as 10:15:00 AM. It shows this value when I select the cell and look in the formula bar. Is there away to drop the seconds off?
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top