Solved: help with VBA opening certain sheets

[mercmannick]

Sub GetFiles_For_Charts() 
    Dim strMyBookEXT As String, strMyBookINT As String 
    Dim cell As Range, MyRange As Range 
    Dim StageStartBook As Workbook 
    Dim IMFWs, S70Ws, IMFEXWs As Worksheets 
 
    On Error GoTo ErrorHandler 
 
    Application.ScreenUpdating = False 
    Set StageStartBook = ThisWorkbook    'set this workbook as active 
    Set IMFWs = StageStartBook.Sheets(2)    'set imf pivot data sheet:destination sheet for kpi 44 imf pivot data 
    Set IMFEXWs = StageStartBook.Sheets(4)    'set imf ex  sheet:destination sheet for kpi 15 imf ex 
    Set S70Ws = StageStartBook.Sheets(3)    'set s70 pivot data sheet:destination sheet for kpi 44 s70 pivot data 
 
    'open most recent book by weeknumber 
    strMyBookEXT = "KPI 0015 external Shortageswk" & CStr(VBAWeekNum(Now(), 1)) 
    strMyBookEXT = strMyBook & "." & CStr(Application.WorksheetFunction.Weekday(Now())) & ".xls" 
    strMyBookEXT = "C:\KPI 0015 external Shortageswk" & CStr(VBAWeekNum(Now(), 1)) 
 
    'open most recent book by weeknumber 
    strMyBookINT = "KPI 0044 internal Shortageswk" & CStr(VBAWeekNum(Now(), 1)) 
    strMyBookINT = strMyBook & "." & CStr(Application.WorksheetFunction.Weekday(Now())) & ".xls" 
    strMyBookINT = "C:\KPI 0044 internal Shortageswk" & CStr(VBAWeekNum(Now(), 1)) 
 
    Workbooks.Open Filename:=strMyBookINT    'open kpi 0044 
    Workbooks.Open Filename:=strMyBookEXT    'open kpi 0015 
 
    'this is where i am struggling, Refer to Thread 
    Range([A1], [IV1].End(xlToLeft)).Copy Destination:=StageStartBook.IMFEXWs.Range("A1") 
 
    For Each cell In MyRange.SpecialCells(xlCellTypeVisible) 
        If cell >= "" Then _ 
           cell.EntireRow.Copy Destination:=StageStartBook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0) 
    Next cell 
 
 
 
    On Error GoTo 0 
    Application.ScreenUpdating = True 
 
    Exit Sub 
 
ErrorHandler: 
 
    MsgBox "Error: " & Err.Number & " (" & Err.Description & ")" 
End Sub 
 
Function VBAWeekNum(D As Date, FW As Integer) As Integer 
     VBAWeekNum = CInt(Format(D, "ww", FW)) 
End Function

hi

Not sure if i am going in right direction, need to open both workbooks above kpi 0044 and kpi 0015

kpi 0044 sheets copy S70 pivot data all used data to destination thisworkbook S70 pivot data sheet (but clearcontents before hand)

kpi 0044 sheets copy imf pivot data all used data to destination thisworkbook imf pivot data sheet (but clearcontents before hand)

kpi 0015 sheets copy imf ex data all used data to destination thisworkbook imf ex sheet (but clearcontents before hand)

close both kpi books

Any help would be appreciated

Thanks Merc

[mdmackillop]

Hi Merc
This "shortcut" does not work
[VBA]
Dim IMFWs, S70Ws, IMFEXWs As Worksheets

[/VBA]
You need to Dim each item
[VBA]
Dim IMFWs As Worksheet, S70Ws As Worksheet, IMFEXWs As Worksheet

[/VBA]

[mercmannick]

mdmackillop

cool thanks , thats a start

Thanks Merc

[mercmannick]

anyone give me a start to this

Many Thanks

Merc

[mdmackillop]

[VBA] 'open most recent book by weeknumber
strMyBookEXT = "KPI 0015 external Shortageswk" & CStr(VBAWeekNum(Now(), 1))
strMyBookEXT = strMyBook & "." & CStr(Application.WorksheetFunction.Weekday(Now())) & ".xls"
strMyBookEXT = "C:\KPI 0015 external Shortageswk" & CStr(VBAWeekNum(Now(), 1))

'open most recent book by weeknumber
strMyBookINT = "KPI 0044 internal Shortageswk" & CStr(VBAWeekNum(Now(), 1))
strMyBookINT = strMyBook & "." & CStr(Application.WorksheetFunction.Weekday(Now())) & ".xls"
strMyBookINT = "C:\KPI 0044 internal Shortageswk" & CStr(VBAWeekNum(Now(), 1))[/VBA]
The third line in each of the above steps is overwriting the previous two and you are producing a string which is not a file name.

[mdmackillop]

What is MyRange?
[VBA]
For Each cell In MyRange.SpecialCells(xlCellTypeVisible)
[/VBA]

[mercmannick]

mdmackillop

okay i think might code might be a bit of a mess, i took it from a snippet of other code, and tryed to work from there

So " The third line in each of the above steps is overwriting the previous two and you are producing a string which is not a file name. "

i can delete the first two lines

Thanks
Merc

[mdmackillop]

Open a "watch" window, add strMyBookEXT and strMyBookInt and keep an eye on the values as you step through the code. This will show you what is happening so that you can correct the code accordingly.

[mercmannick]

Sub GetFiles_For_Charts()
    Dim strMyBookEXT As String, strMyBookINT As String
    Dim cell As Range, MyRange As Range
    Dim StageStartBook As Workbook
    Dim IMFWs As Worksheets, S70Ws As Worksheets, IMFEXWs As Worksheets

    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False
    Set StageStartBook = ThisWorkbook    'set this workbook as active
    
    'open most recent book by weeknumber
    strMyBookEXT = "C:\KPI 0015 external Shortageswk" & CStr(VBAWeekNum(Now(), 1))
    'open most recent book by weeknumber
    strMyBookINT = "C:\KPI 0044 internal Shortageswk" & CStr(VBAWeekNum(Now(), 1))

    Workbooks.Open Filename:=strMyBookINT    'open kpi 0044
    Workbooks.Open Filename:=strMyBookEXT    'open kpi 0015
    
    
    Set IMFWs = StageStartBook.Sheets(2)    'set imf pivot datasheet:destination sheet for kpi 44 imf pivot data
    Set IMFEXWs = StageStartBook.Sheets(4)    'set imf ex  sheet:destination sheet for kpi 15 imf ex
    Set S70Ws = StageStartBook.Sheets(3)    'set s70 pivot data sheet:destination sheet for kpi 44 s70 pivot data

okay done that and this works upto this point, how can i progress from here

Thanks Merc

[mdmackillop]

What values is Watch showing for strMyBookEXT and strMyBookInt?

[mdmackillop]

Please ignore last posting.

[mercmannick]

"C:\KPI 0044 internal Shortageswk32" for strmyBookINT
"C:\KPI 0015 external Shortageswk32" for strmyuBookEXT

Merc

[mercmannick]

sorry missed your last post

Merc

[mercmannick]

With Wb.strMyBookINT
        Set S70Wscopy = Sheets("s70 pivot data")    'set s70 pivot data sheet for kpi 44 s70 pivot data
        Set IMFWscopy = Sheets("imf pivot data")    'set imf pivot datasheet for kpi 44 imf pivot data
    End With
    
    With Wb.strMyBookEXT
        Set IMFEXWscopy = Sheets("imf ex")    'set imf ex  sheet for kpi 15 imf ex
   End With

is this the right way to go to set the wsheets

Merc

[mercmannick]

Sub Update_Sheets_With_New_Data()
    Dim wbEXT As String, wbINT As String
    Dim StStrtBk As Workbook
    Dim IMFWs As Worksheet, S70Ws As Worksheet, IMFEXWs As Worksheet
    Dim shts70copy As Worksheet
    Dim shtIMFcopy As Worksheet
    Dim shtIMFEXcopy As Worksheet
    Dim wbkint As Workbook
    Dim wbkext As Workbook
    On Error GoTo ErrorHandler

    'open most recent book by weeknumber
    wbEXT = "C:\KPI 0015 external Shortageswk" & CStr(VBAWeekNum(Now(), 1))
    'open most recent book by weeknumber
    wbINT = "C:\KPI 0044 internal Shortageswk" & CStr(VBAWeekNum(Now(), 1))

    Workbooks.Open Filename:=wbINT    'open kpi 0044
    Workbooks.Open Filename:=wbEXT    'open kpi 0015

    Set wbkint = Workbooks("KPI 0044 internal Shortageswk" & CStr(VBAWeekNum(Now(), 1)) & ".xls")    'set workbook
    Set shts70copy = wbkint.Sheets("s70 pivot data")    'set wsheet
    Set shtIMFcopy = wbkint.Sheets("imf pivot data")    'set wsheet

    Set wbkext = Workbooks("KPI 0015 external Shortageswk" & CStr(VBAWeekNum(Now(), 1)) & ".xls")    'set workbook
    Set shtIMFEXcopy = wbkext.Sheets("IMF EX")    'set wsheet

    Set StStrtBk = Workbooks("IMF Charts S Start1.xls")    'set workbook
    Set S70Ws = StStrtBk.Sheets("s70 pivot")    'set wsheet
    Set IMFWs = StStrtBk.Sheets("imf pivot")    'set wsheet
    Set IMFEXWs = StStrtBk.Sheets("IMF EXT")    'set wsheet
    S70Ws.Cells.ClearContents 'empty sheets
    IMFWs.Cells.ClearContents 'empty sheets
    IMFEXWs.Cells.ClearContents 'empty sheets

    shts70copy.Cells.Copy Destination:=S70Ws.Range("A1") 'update new data
    shtIMFcopy.Cells.Copy Destination:=IMFWs.Range("A1") 'update new data
    shtIMFEXcopy.Cells.Copy Destination:=IMFEXWs.Range("A1") 'update new data

    On Error GoTo 0
    Application.ScreenUpdating = True

    Exit Sub

ErrorHandler:

    MsgBox "Error: " & Err.Number & " (" & Err.Description & ")"

End Sub

Thanks Guys an gals Solved now Solution above

Merc