Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Copy range from several worksheets for printing (xppro 2003)

    Hi

    I am wanting to create a summary report of data from the same range in many worksheets. The way I thought to do this would be to locate the data in each worksheet, copy it, paste it to a new worksheet with a row between each, print it and then delete the new worksheet, but I am not sure how to go about this.

    Can anyone help?

    cheers
    cheers

    Phil Carter

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy range from several worksheets for printing (xppro 2003)

    Can you be a little more specific about how to "locate the data?" What rows and columns on what sheets?
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Re: Copy range from several worksheets for printing (xppro 2003)

    Legare hi

    I am creating an employee record that generates a new sheet for every employee with information in the same cells in each sheet.

    I wish to generate a summary report which will include the same information for each employee copied to a new sheet with a row between each employees information and then printed.

    cheers
    cheers

    Phil Carter

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy range from several worksheets for printing (xppro 2003)

    1- So, are the employee sheets the only sheets in the workbook, and if not how can you tell which are and which are not employee sheets?

    2- What are the cells on the employee sheets that you want copied to a new sheet?
    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Re: Copy range from several worksheets for printing (xppro 2003)

    Legare hi again

    The first sheet is a summary sheet and the last will always be a template sheet that is used to create all new employee sheets.

    The information I want to extract is always in the range A1:L16.
    cheers

    Phil Carter

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy range from several worksheets for printing (xppro 2003)

    This macro should do what you asked.

    <code>
    Option Explicit


    Public Sub GenRpt()
    Dim oWSRpt As Worksheet, oWS As Worksheet, oWSAct As Worksheet
    Dim I As Long, J As Long
    Set oWSAct = ActiveSheet
    On Error Resume Next
    Set oWSRpt = Worksheets("Report")
    On Error GoTo 0
    Application.DisplayAlerts = False
    If Not oWSRpt Is Nothing Then oWSRpt.Delete
    Application.DisplayAlerts = True
    Set oWSRpt = Nothing
    Set oWSRpt = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oWSRpt.Name = "Report"
    I = 0
    For J = 2 To Worksheets.Count - 2
    Set oWS = Worksheets(J)
    If oWS.Name <> "Report" Then
    oWS.Range("A1:L16").Copy
    oWSRpt.Paste Destination:=oWSRpt.Range("A1").Offset(I, 0)
    I = I + 17
    End If
    Next J
    oWSRpt.Range("A:L").EntireColumn.AutoFit
    oWSAct.Activate
    End Sub
    </code>
    Legare Coleman

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Re: Copy range from several worksheets for printing (xppro 2003)

    Legare thanks for that.

    This works just great.

    2 additional questions.

    1 Can I include a statement to only copy the range to the last cell in a row containing data

    2 Can I change the macro to copy 2 ranges from each sheet and paste to the report sheet?

    cheers
    cheers

    Phil Carter

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy range from several worksheets for printing (xppro 2003)

    The answer to your first questions is probably yes, but I would need a better explaination of what you want. Are you saying that all of the columns out to L may not contain data, and you don't want to copy columns with no data. If that is what you are saying, I must ask what difference it makes?

    The answer to the second question is yes, if you can define what the two ranges are.
    Legare Coleman

  9. #9
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Re: Copy range from several worksheets for printing (xppro 2003)

    Legare

    1 I am recording the dates of events for each employee. With the current setup I have allowed for 10 events/annum i.e B - L, which should be enough but may not be. This is why I would like to copy up to and including the last "event date".

    2 The ranges I require are A4:B4,A14:L16 (again this would have the same requirement as above). I tried inserting this into your code but it gave me a range error.
    cheers

    Phil Carter

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy range from several worksheets for printing (xppro 2003)

    I am not sure I totally understand.

    1- What exactly do you want copied? If you might need more columns, is there any problem if the entire row is copied? The code below is modified to copy the range A1:IV16. Would that solve the problem?

    <code>
    Option Explicit

    Public Sub GenRpt()
    Dim oWSRpt As Worksheet, oWS As Worksheet, oWSAct As Worksheet
    Dim I As Long, J As Long
    Set oWSAct = ActiveSheet
    On Error Resume Next
    Set oWSRpt = Worksheets("Report")
    On Error GoTo 0
    Application.DisplayAlerts = False
    If Not oWSRpt Is Nothing Then oWSRpt.Delete
    Application.DisplayAlerts = True
    Set oWSRpt = Nothing
    Set oWSRpt = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oWSRpt.Name = "Report"
    I = 0
    For J = 2 To Worksheets.Count - 2
    Set oWS = Worksheets(J)
    If oWS.Name <> "Report" Then
    oWS.Range("A1:IV16").Copy
    oWSRpt.Paste Destination:=oWSRpt.Range("A1").Offset(I, 0)
    I = I + 17
    End If
    Next J
    oWSRpt.Range("A:L").EntireColumn.AutoFit
    oWSAct.Activate
    End Sub
    </code>

    2- Where do you want A4:B4 and A14:L16 copied to? A4:B4 to one row with A14:L16 immediately below it? When you say "this would have the same requirements as above," do you mean for both areas or just the second?
    Legare Coleman

  11. #11
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Re: Copy range from several worksheets for printing (xppro 2003)

    Legare good morning (7:00am here)

    1 I am not sure about this solution. After creating the summarised report i wish to print this and was hoping to do this from the macro without having the nominate a specific print area. Elsewhere in my project I use this code,

    ActiveCell.Offset(0, 1).Select
    Do While Not IsEmpty(ActiveCell)
    ActiveCell.Offset(0, 1).Select
    Loop

    and was hoping that something similar could be used to search for the last filled cell in the row and then copy the row up to that cell. My concern with the printing is, if I copy to cell IV16 how do I specify the print area?

    2 you are correct in your assumption.

    A4:B4
    A14:L16

    and the "same requirements" only to the second area.

    cheers
    cheers

    Phil Carter

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy range from several worksheets for printing (xppro 2003)

    Is this what you want?

    <code>
    Option Explicit

    Public Sub GenRpt()
    Dim oWSRpt As Worksheet, oWS As Worksheet, oWSAct As Worksheet
    Dim I As Long, J As Long
    Set oWSAct = ActiveSheet
    On Error Resume Next
    Set oWSRpt = Worksheets("Report")
    On Error GoTo 0
    Application.DisplayAlerts = False
    If Not oWSRpt Is Nothing Then oWSRpt.Delete
    Application.DisplayAlerts = True
    Set oWSRpt = Nothing
    Set oWSRpt = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oWSRpt.Name = "Report"
    I = 0
    For J = 2 To Worksheets.Count - 2
    Set oWS = Worksheets(J)
    If oWS.Name <> "Report" Then
    oWS.Range("A4:B4").Copy
    oWSRpt.Paste Destination:=oWSRpt.Range("A1").Offset(I, 0)
    oWS.Range("A14:IV16").Copy
    oWSRpt.Paste Destination:=oWSRpt.Range("A1").Offset(I + 1, 0)
    I = I + 5
    End If
    Next J
    oWSRpt.Range("A:IV").EntireColumn.AutoFit
    oWSRpt.PrintOut
    Application.DisplayAlerts = False
    oWSRpt.Delete
    Application.DisplayAlerts = True
    On Error Resume Next
    oWSAct.Activate
    On Error GoTo 0
    End Sub

    </code>
    Legare Coleman

  13. #13
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Re: Copy range from several worksheets for printing (xppro 2003)

    Legare

    Again thanks very much for your help, that works just great. My customers, including my wife, are very happy.

    Regards
    cheers

    Phil Carter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •