Solved

Macro Help

Posted on 2013-12-04
4
172 Views
Last Modified: 2013-12-04
I have the attached spreadsheet which has about 300 lines.
I need to have the guest names under the employee names, basically leaving me with two columns instead of four. However, for example, guest F1 needs to be under employee F1 and guest L1 needs to be under employee L1 and so on.

I tried doing a macro myself but it seems to be stuck on cell c2. I have 300 lines in my original file and thus am not excited of doing it manually.

All help is appreciated.

Thank you
Book2.xlsx
0
Comment
Question by:squirrelzan
  • 3
4 Comments
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39695874
Can you upload the code you tried and we can maybe point out the error?

The way I am thinking of approaching is to insert a row between each employee line, then copy the guest columns and paste into the employee columns selecting Paste Special and SKip Blanks.

This can be automated as required.

Thanks
Rob H
0
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39695935
Try the code below:
Sub InsertRows()

Application.Calculation = xlCalculationManual
Do Until ActiveCell.Value = ""
    ActiveCell.Offset(1, 0).Select
    Selection.EntireRow.Insert
    ActiveCell.Offset(1, 0).Select
Loop
    ActiveCell.SpecialCells(xlLastCell).Select
    LR = ActiveCell.Row
    Range("C2:D" & LR).Select
    Selection.Copy
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False

    Columns("C:D").Select
    Selection.Delete Shift:=xlToLeft
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp
    Range("A2").Select
Application.Calculation = xlCalculationAutomatic

Open in new window


Thanks
Rob H
0
 

Author Closing Comment

by:squirrelzan
ID: 39696037
This worked perfectly. Thank you
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39696115
Glad to be of assistance.

Don't forget it assumes only one line per employee.

Thanks
Rob H
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question