Application.ScreenUpdating

[KCLA1785]

Hello.
I have the following formula which searches another sheet gathers the relevant info then pastes it to Sheet 1 !
Problem is it overwrites all cells in all columns and I would like it to only fill column A to Column H ?
can you help me please ??

Sub Test2()
Dim myWord$
myWord = InputBox("Type Purchase Order Number Below...", "Purchase Order Number")
If myWord = "" Then Exit Sub

Application.ScreenUpdating = False
Dim xRow&, NextRow&, LastRow&
NextRow = 11
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For xRow = 1 To LastRow
If WorksheetFunction.CountIf(Rows(xRow), "*" & myWord & "*") > 0 Then
Rows(xRow).Copy Sheets("Sheet1").Rows(NextRow)
NextRow = NextRow + 1
End If
Next xRow
Application.ScreenUpdating = True


End Sub

 

[Darren Bartrup]

At the moment your code copies the whole row Rows(xRow).Copy
Change it to copy just columns 1 to 8 (A:H).

Range(Cells(xRow, 1), Cells(xRow, 8)).Copy Sheets("Sheet1").Cells(NextRow, 1)

You should also qualify your cells location with the sheet as well (all cell references, so the LastRow definition & the Worksheetfunction).

Sheets("Sheet2").Range(Sheets("Sheet2").Cells(xRow, 1), Sheets("Sheet2").Cells(xRow, 8)).Copy Sheets("Sheet1").Cells(NextRow, 1)

You could shorten the referencing by using a With... End With code block.

The Application.ScreenUpdating isn't needed... not sure why you've called the question that.

 

[KCLA1785]

Darren... Your an absolute legend and its not my code, I'm just making repairs to the worksheet but i'm unfamiliar with this kind of copy ?
Anyway thank you very much for your help mate its working

 

[MARK858]

The Application.ScreenUpdating isn't needed...

@KCLA1785, I would leave the screenupdating = false in there as with the way that you are interacting with the sheet multiple times you are going to get screen flicker and slow the code if you remove it.

Btw, another way to to write the reference to the range is

Range(Sheets("Sheet2").Cells(xRow, "A"), Sheets("Sheet2").Cells(xRow, "H")).Copy Sheets("Sheet1").Cells(NextRow, 1)

which you might find more intuitive.

Or another option is

Intersect(Sheets("Sheet2").Rows(xRow), Sheets("Sheet2").Range("A:H")).Copy Sheets("Sheet1").Cells(NextRow, 1)

 

[KCLA1785]

Thank You very much for your help, Much appreciated