Excel question!

Fed up talking videogames? Why?
User avatar
<]:^D
Member
Joined in 2008

PostExcel question!
by <]:^D » Mon Aug 17, 2015 2:51 pm

Hello all,

I'm looking to convert our current database 20-30 entries from Microsoft Access to Excel, as I hate Access and no one knows how to use the damn thing.

The question is: is it possible to have people's entries that then feed into a template, that can then be printed with each person's entries on a different sheet.

E.g. at the moment we use Access to have a range of people's numbers, e.g.

John Doe 115 / 120 / 90 / 65
Joe Bloggs 120 / 140 / 100 / 90
etc.

which then feed into a template sheet that prints out formulas based on each person, so we get 30 sheets each with say:

John Doe

(115) * .9
(120) * .75

and then another printed sheet with

Joe Bloggs

(120) * .9
(140) * .75

and so forth.

Wanted to know, is this possible in Excel?

Thanks!

User avatar
Rightey
Member
Joined in 2008

PostRe: Excel question!
by Rightey » Mon Aug 17, 2015 3:10 pm

I think it's possible, keep in mind I'm no Excel wizard, but it might require more workthan your current set up.

I think you would need one workbook with the names and user info. Then another template workbook, into which you would import the data into an invisible worksheet and use that to populate your templates. Keep in mind if you have 20 users and 30 templates each these are some large workbooks, and I'm not sure if excel can handle something with 600 sheets. Also you might want need to dip into VBA for automating the population of everything.

Pelloki on ghosts wrote:Just start masturbating furiously. That'll make them go away.

Image
User avatar
<]:^D
Member
Joined in 2008

PostRe: Excel question!
by <]:^D » Mon Aug 17, 2015 3:33 pm

yeah i thought it might be a bit messy on the printing side of things!

p.s. it wouldn't be 30 templates, more like 3-5 templates with 20-30 users.

User avatar
satriales
Member
Joined in 2008

PostRe: Excel question!
by satriales » Mon Aug 17, 2015 7:30 pm

This code will turn each row into a new worksheet:

Code: Select all

Sub RowToSheet()
Dim LR As Long, i As Long
With ActiveSheet
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Row " & i
        .Rows(i).Copy Destination:=Sheets("Row " & i).Range("A1")
    Next i
End With
End Sub


You would need to modify it so that it overwrites sheets if run again, or creates a separate workbook instead, but I think it's doable.


Return to “Stuff”

Who is online

Users browsing this forum: chanticleer, FatDaz, Gemini73, Godzilla, ITSMILNER, Jam-Master Jay, jawafour, Jezo, Met, Minty14, OrangeRKN, Saint of Killers, suzzopher and 70 guests