Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: switching through workbooks when extracting cells (xcel 00)

    Your code refers to wbk.Worksheets("CBM Summary2"). If your worksheet is named "Coil Sumary2", as you say, you should use that instead of "CBM Summary2".

  2. #2
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: switching through workbooks when extracting cells (xcel 00)

    yes i made that correction and removed the wbk.Close SaveChanges:=False after cbm summary1, seems to work much better

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: switching through workbooks when extracting cells (xcel 00)

    I run into a problem when some of the workbooks do not have cbm Summary 2 worksheets how could i handle this allowing the loop to skip it and move to the next wbk?

  4. #4
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    switching through workbooks when extracting cells (xcel 00)

    I was wondering if there is a piece of code that I can add to this macro which will allow me to move to the next worksheet called coil summary2. As it is now it gets an automation error when it steps into this part of the code.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: switching through workbooks when extracting cells (xcel 00)

    Since he already has error handling, I would use

    On Error GoTo ErrHandler

    instead of

    On Error GoTo 0

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

    Re: switching through workbooks when extracting cells (xcel 00)

    Slmething like this:

    Dim oSh As Worksheet
    On Error Resume Next
    Set oSh = wbk.Worksheets("CBM Summary2")
    On Error GoTo ErrHandler
    If Not oSh Is Nothing Then
    oSh.Range("A23:K26").Copy
    wsh.Range("Y" & i).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

    oSh.Range("A30:K31").Copy
    wsh.Range("Y" & (i + 4)).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

    oSh.Range("A34:K35").Copy
    wsh.Range("Y" & (i + 6)).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

    oSh.Range("A37:L37").Copy
    wsh.Range("Y" & (i + 8)).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

    oSh.Range("A41:K42").Copy
    wsh.Range("Y" & (i + 9)).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    End If

    Edited by Legare Coleman to correct what Hans pointed out. Thanks Hans.
    Legare Coleman

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

    Re: switching through workbooks when extracting cells (xcel 00)

    No you do not have to. You can if yout want to, but it is not necessary.
    Legare Coleman

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

    Re: switching through workbooks when extracting cells (xcel 00)

    Absolutely correct, I did not see that. I have corrected my post.
    Legare Coleman

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: switching through workbooks when extracting cells (xcel 00)

    Thanks
    do i need to declare the worksheet where the data it to be pasted after it copied after i add this conversion? When I run it, it seems to skip over the data in cbm summary 2 and not paste it at its destination.

  10. #10
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: switching through workbooks when extracting cells (xcel 00)

    when the program steps into the next worksheet, it copies it but does not paste it anywhere. Is there some like paste destination need?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: switching through workbooks when extracting cells (xcel 00)

    The code will copy cells from column A in the Summary2 sheet to column Y in the target sheet. Are you sure there are data in column A?

    You can check what the code actually does: click in the first line that acts on the CBM Summary2 sheet, and press F9 to set a breakpoint. When you run the code, it will pause at the breakpoint. You can single-step through the code by pressing F8 repeatedly, and switch back and forth between Excel and the Visual Basic Editor. You can press F5 to let the code run on again. If you want to remove the breakpoint, click in the line and press F9 again.

  12. #12
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: switching through workbooks when extracting cells (xcel 00)

    yes there is data in the a23 cell, I had to change some of my code though which had a little typo which was apparent using the debug. Thanks for takinf a look at my question

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

    Re: switching through workbooks when extracting cells (xcel 00)

    The code for pasting that is in my post was copied from your post and was not altered. If your code was pasting, then my code should do the same. As Hans said, the code looks like it should be pasting starting in column Y. If that is not what is happening, I think that you are going to have to upload the two workbooks with the exact code for us to look at.
    Legare Coleman

  14. #14
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: switching through workbooks when extracting cells (xcel 00)

    if I wanted to add yet another worksheet to legare code, oSh2 so CBM summary3, would i simply rename it osh2 instead of osh and place the same code below the cbm summary2? or do I need to call on another peice of code?

  15. #15
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: switching through workbooks when extracting cells (xcel 00)

    thanks but I realized that i just had a typo, so its fixed, next time I ll just use paste, but find that if I type it out I have a better learning of it

Page 1 of 2 12 LastLast

Posting Permissions

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