PDA

View Full Version : Solved: help with VBA opening certain sheets



mercmannick
08-11-2006, 05:44 AM
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
08-11-2006, 05:49 AM
Hi Merc
This "shortcut" does not work

Dim IMFWs, S70Ws, IMFEXWs As Worksheets


You need to Dim each item

Dim IMFWs As Worksheet, S70Ws As Worksheet, IMFEXWs As Worksheet

mercmannick
08-11-2006, 05:52 AM
mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87)

cool thanks , thats a start

Thanks Merc

mercmannick
08-12-2006, 01:48 AM
anyone give me a start to this

Many Thanks

Merc

mdmackillop
08-12-2006, 02:24 AM
'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))
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
08-12-2006, 02:30 AM
What is MyRange?

For Each cell In MyRange.SpecialCells(xlCellTypeVisible)

mercmannick
08-12-2006, 02:34 AM
mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87)

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
08-12-2006, 02:39 AM
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
08-12-2006, 03:11 AM
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
08-12-2006, 04:03 AM
What values is Watch showing for strMyBookEXT and strMyBookInt?

mdmackillop
08-12-2006, 04:09 AM
Please ignore last posting.

mercmannick
08-12-2006, 04:11 AM
"C:\KPI 0044 internal Shortageswk32" for strmyBookINT
"C:\KPI 0015 external Shortageswk32" for strmyuBookEXT

Merc

mercmannick
08-12-2006, 04:12 AM
sorry missed your last post

Merc

mercmannick
08-12-2006, 04:14 AM
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
08-12-2006, 10:49 AM
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