Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    add unhide worksheets to workbook (excel)

    I am extracting data from a workbook, most of the data i need is on worksheet 1. In the workbook there is hidden worksheets aswell, which hold data i need. I am trying to figure out a way after i extract data from worksheet 1,
    to move into the hidden worksheet called JobChartsData next, unhide it and extract data from a specified column (F) and a row.

    The tricky part comes when i need this column F to be controlled to pick the row of data is greater then 0. once column F show a value (ex. .1) the marco will want to move up 2 rows from the row holding .1 and copy this row. This will be pasted under my data extracted from worksheet 1. I ve been sitting on this for a day now, so maybe some other eyes can take a peek.

    the code below extracts the info i need from worksheet 1

    Sub Extract()
    Dim fso As Object
    Dim fld As Object
    Dim sfl As Object
    Dim strPath As String
    Dim strFile As String
    Dim i As Long
    Dim wbk As Workbook
    Dim wsh As Worksheet
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set wsh = ThisWorkbook.Worksheets(1)
    wsh.Range("A:B:C").Clear
    strPath = wsh.Range("K1")
    Set fld = fso.GetFolder(strPath)
    For Each sfl In fld.SubFolders
    i = i + 1
    wsh.Range("A" & i) = sfl.Path
    strFile = Dir(sfl.Path & "*.xls")
    Set wbk = Workbooks.Open(Filename:=sfl.Path & "" & strFile, AddToMRU:=False)
    wsh.Range("B" & i) = wbk.Worksheets(1).Range("C6")
    wsh.Range("C" & i) = wbk.Worksheets(1).Range("C8")
    wsh.Range("D" & i) = wbk.Worksheets(1).Range("C5")
    wsh.Range("E" & i) = wbk.Worksheets(1).Range("C14")
    wsh.Range("F" & i) = wbk.Worksheets(1).Range("C4")
    wbk.Close SaveChanges:=False
    Next sfl
    Set wbk = Nothing
    Set sfl = Nothing
    Set fld = Nothing
    Set fso = Nothing
    ThisWorkbook.Close SaveChanges:=True
    End Sub

  2. #2
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add unhide worksheets to workbook (excel)

    maybe adding something like this. to the code,

    Sub UnHideSheet()
    Sheets("JobChartData").Visible = xlVeryUnHidden
    End Sub

    or

    Application.CommandBars("Worksheet Menu
    Bar").Controls("Format").Controls("Sheet").Control s("Unhide...").Enabled =True

    I have seen this done, well kinda like that, as for loopin through and meeting a condition and moving back two rows, this aint my talent maybe someone else can help ya. I know this code should work though

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: add unhide worksheets to workbook (excel)

    Could you elaborate on exactly what you need the code to do. It is not clear to me what you are after.

    Perhaps, you could attach a file of what you have and what you want it to look like afterwards...

    Steve

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

    Re: add unhide worksheets to workbook (excel)

    I cant get this peice of code placed right to make it work, Im not too good at vb, but i like learning

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

    Re: add unhide worksheets to workbook (excel)

    You don't need to unhide a sheet to work with it in code. Try inserting the following lines immediately above the line wbk.Close SaveChanges:=False:
    <code>
    Dim j As Long
    j = 5
    Do While wbk.Worksheets("JobChartsData").Range("F" & j) = 0
    j = j + 1
    Loop
    j = j - 2
    i = i + 1
    wbk.Worksheets("JobChartsData").Rows(j).Copy Destination:=wsh.Range("A" & i)
    </code>
    It may not do exactly what you want, so make sure to create a backup copy of the workbook before trying it.

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

    Re: add unhide worksheets to workbook (excel)

    It should not be necessary to unhide the sheet to get the data from it. See if the code below will get you started. I could not test is since you did not upload the workbooks.

    <pre>Sub Extract()
    Dim fso As Object
    Dim fld As Object
    Dim sfl As Object
    Dim strPath As String
    Dim strFile As String
    Dim I As Long, J As Long, JMax As Long
    Dim wbk As Workbook
    Dim wsh As Worksheet
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set wsh = ThisWorkbook.Worksheets(1)
    wsh.Range("A:B:C").Clear
    strPath = wsh.Range("K1")
    Set fld = fso.GetFolder(strPath)
    I = 1
    For Each sfl In fld.SubFolders
    wsh.Range("A" & I) = sfl.Path
    strFile = Dir(sfl.Path & "*.xls")
    Set wbk = Workbooks.Open(Filename:=sfl.Path & "" & strFile, AddToMRU:=False)
    wsh.Range("B" & I) = wbk.Worksheets(1).Range("C6")
    wsh.Range("C" & I) = wbk.Worksheets(1).Range("C8")
    wsh.Range("D" & I) = wbk.Worksheets(1).Range("C5")
    wsh.Range("E" & I) = wbk.Worksheets(1).Range("C14")
    wsh.Range("F" & I) = wbk.Worksheets(1).Range("C4")
    wbk.Close SaveChanges:=False
    JMax = wbk.Worksheets("JobChartsData").Range("F65536").En d(xlUp).Row - 1
    If JMax < 3 Then
    MsgBox "No data in workbook " & strFile & "worksheet JobChartsData"
    Else
    For J = 2 To JMax
    If wbk.Worksheets("JobChartsData").Range("F1").Offset (J, 0).Value > 0 Then
    wbk.Worksheets("JobChartsData").Range("F1").Offset (J - 2, 0).EntireRow.Copy
    wsh.Paste Destination:=Range("A1").Offset(I + 1, 0)
    Exit For
    End If
    Next J
    If J > JMax Then
    MsgBox "No data in workbook " & strFile & "worksheet JobChartsData"
    End If
    End If
    I = I + 2
    Next sfl
    Set wbk = Nothing
    Set sfl = Nothing
    Set fld = Nothing
    Set fso = Nothing
    ThisWorkbook.Close SaveChanges:=True
    End Sub
    </pre>

    Legare Coleman

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

    Re: add unhide worksheets to workbook (excel)

    i want to provide an example worksheet, i zipped it and the size is 192 kb, what can i do place this on the post?

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

    Re: add unhide worksheets to workbook (excel)

    Remove everything that is not strictly needed for understanding the problem. You might select the relevant cells and copy them into a new workbook; the size of the new workbook is likely to be much smaller.

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

    Re: add unhide worksheets to workbook (excel)

    heres a workbook of the data im trying to extract.
    the extract program from the first post will extract all wanted cells from sheet 1.

    If i could some how get more data from the hidden work sheet
    Format > sheet > unhidden choose jobchartdata worksheet.

    i would like to extract 2 rows above the row where Column F has a value then 0.
    hopefully this helps.

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

    Re: add unhide worksheets to workbook (excel)

    The code I posted higher up in this thread in <post#=480523>post 480523</post#> will extract the row you want, but where exactly do you want to have it copied?

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

    Re: add unhide worksheets to workbook (excel)

    i would like it to be copied right under the data from worksheet 1 and then move on to the next folder and do the same.

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

    Re: add unhide worksheets to workbook (excel)

    Do you want the row to be copied to the worksheet referred to as wsh in your code, or to the worksheet referred as wbk.Worksheets(1) (the worksheet named "1" in the workbook you attached)?

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

    Re: add unhide worksheets to workbook (excel)

    the work sheet with my code please

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

    Re: add unhide worksheets to workbook (excel)

    Ok, that's what the code I referred to should do. Have you tried it?

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

    Re: add unhide worksheets to workbook (excel)

    I have corrected my code to match the actual format of your worksheets below:

    <pre>Option Explicit

    Sub Extract()
    Dim fso As Object
    Dim fld As Object
    Dim sfl As Object
    Dim strPath As String
    Dim strFile As String
    Dim I As Long, J As Long, JMax As Long
    Dim wbk As Workbook
    Dim wsh As Worksheet
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set wsh = ThisWorkbook.Worksheets(1)
    wsh.Range("A:B:C").Clear
    strPath = wsh.Range("K1")
    Set fld = fso.GetFolder(strPath)
    I = 1
    For Each sfl In fld.SubFolders
    wsh.Range("A" & I) = sfl.Path
    strFile = Dir(sfl.Path & "*.xls")
    Set wbk = Workbooks.Open(Filename:=sfl.Path & "" & strFile, AddToMRU:=False)
    wsh.Range("B" & I) = wbk.Worksheets(1).Range("C6")
    wsh.Range("C" & I) = wbk.Worksheets(1).Range("C8")
    wsh.Range("D" & I) = wbk.Worksheets(1).Range("C5")
    wsh.Range("E" & I) = wbk.Worksheets(1).Range("C14")
    wsh.Range("F" & I) = wbk.Worksheets(1).Range("C4")
    JMax = wbk.Worksheets("JobChartData").Range("F65536").End (xlUp).Row - 1
    If JMax < 7 Then
    MsgBox "No data in workbook " & strFile & "worksheet JobChartsData"
    Else
    For J = 7 To JMax
    If wbk.Worksheets("JobChartData").Range("F1").Offset( J, 0).Value > 0 Then
    wbk.Worksheets("JobChartData").Range("F1").Offset( J - 2, 0).EntireRow.Copy
    wsh.Paste Destination:=wsh.Range("A1").Offset(I, 0)
    Application.CutCopyMode = False
    Exit For
    End If
    Next J
    If J > JMax Then
    MsgBox "No data in workbook " & strFile & "worksheet JobChartsData"
    End If
    End If
    I = I + 2
    wbk.Close SaveChanges:=False
    Next sfl
    Set wbk = Nothing
    Set sfl = Nothing
    Set fld = Nothing
    Set fso = Nothing
    ThisWorkbook.Close SaveChanges:=True
    End Sub
    </pre>

    Legare Coleman

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
  •