Copy Data from multiple sheets based on criteria in a column and paste to master log.

[Jsonic5280]

I have a workbook that contains multiple sheets and Two Master sheets. I am trying to get specific data (Depending on what is in Column "G") from specific sheets to go to one Master Log, and data with a different criteria (Also in Column "G") to go to a different sheet. I have a version of the code typed out But I keep getting Run-time error 7 : Out of Memory. I duplicated the Module to do the exact same thing but changed the destination of the copied data to the other Master sheet, and still no luck.

Please review this code and see if there is any changes I can make to get this thing working.

Public Sub CombineDataFromAllSheets()
Dim wkstSrc As Worksheet, wkstDst As Worksheet
Dim i As Integer
Dim LastRow As Long, erow As Long

Application.ScreenUpdating = False
Application.EnableEvents = False

Set wkstDst = ThisWorkbook.Worksheets("Master Supplemental Requests")

For Each wkstSrc In ThisWorkbook.Worksheets
If wkstSrc.Name <> "Master Supplemental Requests" _
And wkstSrc.Name <> "State Recap" _
And wkstSrc.Name <> "Division" _
And wkstSrc.Name <> "Master Sales Projection" _
And wkstSrc.Name <> "JDA NIF" _
And wkstSrc.Name <> "Instructions" _
And wkstSrc.Name <> "items" _
And wkstSrc.Name <> "Item Forecast" _
Then

'Define Variables and run the copy Paste events
LastRow = wkstSrc.Cells(Rows.Count, 1).End(xlUp).Row
For i = 21 To LastRow

If wkstSrc.Cells(i, 8) = "Supp" _
And wkstSrc.Cells = "Both" Then

wkstSrc.Cells(i, 1).Copy
erow = wkstDst.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
wkstDst.Cells(erow, 3).PasteSpecial xlPasteValues

wkstSrc.Cells(i, 9).Copy
wkstDst.Cells(erow, 5).PasteSpecial xlPasteValues

wkstSrc.Cells(i, 10).Copy
wkstDst.Cells(erow, 6).PasteSpecial xlPasteValues

wkstSrc.Cells(i, 11).Copy
wkstDst.Cells(erow, 7).PasteSpecial xlPasteValues

wkstSrc.Cells(i, 12).Copy
wkstDst.Cells(erow, 15).PasteSpecial xlPasteValues

wkstSrc.Cells(i, 13).Copy
wkstDst.Cells(erow, 16).PasteSpecial xlPasteValues

wkstDst.Cells(erow, 4).Value = wkstSrc.Name
End If
Next i

End If

Next wkstSrc

Application.CutCopyMode = False
Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

[mikeTRON]

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


(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)