Hi all
can any one help me please.
i have a reporting spreadsheet which has 4 worksheets on : Daily input, daily Graphs,Weekly Input, Weekly graphs. I have put an array formula on daily graphs from daily input which changes the graphs to the week number i input. also the weekly input works out automaticly from the daily input.
What i now need is when i input to weekly range on the weekly graph b5 & e5. That it looks for the week number on the weekly input worksheeet and copies all the data off that spreadsheet onto weekly graphs spreadsheet on line 97 I was given a formula off here to do this on just a spreadsheet for the weekly figures but when a added the daily spreadsheets it would not work the formula was
Option Explicit
Sub PullData()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LR2 As Long, sWk As Long, eWk As Long
Set ws1 = Sheet1
Set ws2 = Sheet2
With ws1
.UsedRange.Offset(2, 0).Cells.Clear
sWk = .Range("B4").Value
eWk = .Range("E4").Value
End With
With ws2
LR2 = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
.AutoFilterMode = False
.Range("A4:G" & LR2).AutoFilter Field:=1, Criteria1:=">=" & sWk, _
Operator:=xlAnd, Criteria2:="<=" & eWk
.Range("A4:G" & LR2).SpecialCells(xlCellTypeVisible).Copy
ws1.Range("A6").PasteSpecial (xlPasteValues)
.AutoFilterMode = False
Application.CutCopyMode = False
End With
End Sub
log test Question.xlsm
Any help very much appreicated
Bookmarks