Looking to do a simple copy formula

Alexpeppard

New Member
Joined
Nov 30, 2015
Messages
1
My problem is frustrating especially because I know there has to be an easy fix. I have columns of peoples information (name, birthdate ect) and I am looking to print it out. it is formulated like this:

First NameJohnJaneJimect
Last NameDoeDoeDoeect...
Birthday19941994ect
Birth locationCanadaCanadaect
AllergiesNoneNoneect

<tbody>
</tbody>

But I have 75 people, and many more rows...... What I am looking to do is to repeat Column A but pair it with a different column, such A and B, A and C, A and D, A and E.... ect:

Like this:
First NameJohn
Last NameDoe
Birthday1994
Birth PlaceCanada
AllergiesNone

<tbody>
</tbody>

First NameJane
Last NameDoe
Birthday1994
Birth placeCanada
AllergiesNone

<tbody>
</tbody>


Ect. The point being, I can have everyone's information printed on separate pages


Looking for any help possible.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Try this:
This script will take your data from Sheet (1) and copy it over to sheet (2) the way you want it.
Remember Sheet(1) is the sheet on the far left side of your tab bar and sheet(2) is next sheet to right.
Code:
Sub Test()
Application.ScreenUpdating = False
Dim i As Integer
Dim b As Integer
Dim Lastrow As Long
Dim Lastrowa As Long
Dim LastColumn As Long
Sheets(1).Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row
Lastrowb = Sheets(2).Cells(Rows.Count, "B").End(xlUp).Row
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column

    For b = 2 To LastColumn
        For i = 1 To Lastrow
            Sheets(1).Cells(i, 1).Copy Destination:=Sheets(2).Cells(Lastrowa, 1)
            Sheets(1).Cells(i, b).Copy Destination:=Sheets(2).Cells(Lastrowb, 2)
            Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
            Lastrowb = Sheets(2).Cells(Rows.Count, "B").End(xlUp).Row + 1
        Next
        LastColumn = LastColumn + 1
    Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
To add to @MAIT's script, the following will add page breaks so individual information can be "printed on separate pages"...

Code:
[COLOR=#808080]    For b = 2 To LastColumn
        For i = 1 To Lastrow
            Sheets(1).Cells(i, 1).Copy Destination:=Sheets(2).Cells(Lastrowa, 1)
            Sheets(1).Cells(i, b).Copy Destination:=Sheets(2).Cells(Lastrowb, 2)
            Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
            Lastrowb = Sheets(2).Cells(Rows.Count, "B").End(xlUp).Row + 1
        Next
        [/COLOR]Sheets(2).Activate
        Sheets(2).Cells(Lastrowb, 2).Select
        ActiveWindow.SelectedSheets.HPageBreaks.Add before:=ActiveCell[COLOR=#808080]
        LastColumn = LastColumn + 1
    Next[/COLOR]


And be sure to add the missing "Dim Lastrowb as Long" statement otherwise the code will generate an error.

Cheers,

tonyyy
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top