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 = strMyBookEXT & "." &
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 = strMyBookINT & "." &
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("A6553 6").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 Nick