Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: Extract data from one workbook to another

  1. #11
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default



    [ This Message was edited by: brettvba on 2002-04-28 20:06 ]

  2. #12
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi George...
    You say it will only import from one sheet on the Job Code sheet?
    I tested the code as I sent it and it pulled data from the ranges you selected from every sheet?
    Have you stepped through the code to see what it is doing? Or not doing in this case...
    Tom

  3. #13
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom
    Yes I did. I tried the code as you sent it but it would not work. Something I did wrong perhaps. Did you run the code I just posted? It works well except for the problem I mentioned.
    George

    Learn to listen. Opportunity sometimes knocks very softly.

  4. #14
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom
    Just stepped through it and it loops through fine the first time but when it goes back to the top of the loop the active sheet does not change. Could it be something with the CurrentSheetName being active. I have tried setting it back (CurrentSheetName = Nothing) but that gives an error.
    George

    Learn to listen. Opportunity sometimes knocks very softly.

  5. #15
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    what about
    CurrentSheetName = Null

  6. #16
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK the error message is gone but the same problem still exists.
    Thanks for keeping the thread alive Brett
    George

    Learn to listen. Opportunity sometimes knocks very softly.

  7. #17
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi George.
    If I understand your code, you need to open a job code workbook, named by the user, and copy from the same range on each sheet, and then paste to Pay Verification...
    Is this correct?
    That us the way I tested it...
    Give me a few minutes.
    Will need to mock it up a bit.

    P.S. Edit

    I just re-read your post and you mentioned that the individual Job Code workbooks are already open?
    Also the only extraction will come from the default active sheet(Job Code Workbook)?
    If that is the case, why are we looping through the Job Codes workbook?
    Please explain...
    Thanks,
    Tom

    P.S. Edit

    Your quote:
    "This code will loop for as many sheets that are in JobCode but will only extract info from one sheet. Whatever sheet is active when it is opened."

    do you mean:
    Whatever sheet is active when it is activated."
    It is already open? Correct?


    [ This Message was edited by: TsTom on 2002-04-28 21:58 ]

  8. #18
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom
    First question. Yes the workbook named by the user (JobCode) is a group of houses and each sheet represents a single house. I need to extract the same range from each sheet (House) and paste it to Pay Verification to check the payroll against the budget. For some reason the loop cycles for as many sheets as JobCode has but only extract data from the sheet that is active. In other words in this case it extracts and pastes the same info 3 times because thats how many sheets are in this workbook. (Could be as many as 20 or 25)
    To further clarify it only copies from one sheet three times instead of cycling through all the sheets.
    _________________
    George

    Learn to listen. Opportunity sometimes knocks very softly.

    [ This Message was edited by: GeorgeB on 2002-04-28 22:10 ]

  9. #19
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I found that error
    Current sheet was for Pay Verification not Job Code...

    Here is some edited code which I just tested and it seems to work fine..

    Tom



    Sub ImportJob()
    'Imports all data for a new job into Pay Verification
    Dim JobCode As Variant
    Dim sht As Worksheet
    On Error GoTo errhand

    'Ask for the Job Code
    JobCode = InputBox(prompt:="", Title:="INPUT THE JOB CODE")
    If JobCode = "" Then
    MsgBox prompt:="", Title:="NOTHING ENTERED"
    Exit Sub
    End If
    Application.ScreenUpdating = False
    'Open the Job inputed by user
    'Loop through the workbook and extract the data
    Workbooks(JobCode).Activate
    For Each sht In Workbooks(JobCode).Worksheets
    'Copy the desired data
    sht.Range("B18:D37").Copy
    'Open the target workbook
    Workbooks("Pay Verification.xls").Activate
    With Workbooks("Pay Verification").Sheets("INPUT")
    .Range("E65536").End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    'Paste in the data
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    'Paste in the Job name (JobCode)
    .Range("B65536").End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    .Range(ActiveCell, ActiveCell.Offset(19, 0)).Select
    Selection.FormulaR1C1 = JobCode
    'Paste in the sheet name
    .Range("C65536").End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    .Range(ActiveCell, ActiveCell.Offset(19, 0)).Select
    Selection.FormulaR1C1 = CurrentSheetName
    'Extend the formulas down
    .Range("D65536").End(xlUp).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    .Range(ActiveCell, ActiveCell.Offset(19, 0)).Select
    .Paste
    .Range("H65536").End(xlUp).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    .Range(ActiveCell, ActiveCell.Offset(19, 0)).Select
    .Paste
    Application.CutCopyMode = False
    .Range("A1").Select
    End With
    Workbooks(JobCode).Activate
    Next sht
    Exit Sub

    errhand:
    MsgBox prompt:="", Title:="ERROR, TRY AGAIN"

    End Sub




    [ This Message was edited by: TsTom on 2002-04-28 22:12 ]

  10. #20
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you copied the code before I edited it, make sure you change this line:

    Range("B18:D37").Copy

    to:

    sht.Range("B18:D37").Copy

    Tom

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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