Getting Value off a range on 1 sheet to another sheet

[bulkodave]

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

[:) Sixthsense :)]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here