Macro to paste values to a new sheet based on a condition

Hello All,

I'm looking for a way to create a macro I can run across various workbooks that are used for inventory tracking and management. Each workbook has numerous tabs with various names that are based on dates of inventory purchase. First, I'm not sure if there is a way to make the code read "do this for all sheets in this workbook, regardless of the tab name". Usually codes reference a specific sheet, but my programming skills are at a very beginner level and there may be a way to do it. Possibly this, which I found on a Microsoft website,

Sub WorksheetLoop()

Dim WS_Count As Integer
Dim I As Integer

' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count

But I'm not sure if that will achieve what I am looking for.

So basically all I want it to do it to look in a column for a specific value, in my case I'm looking for the year the inventory has sold. If any cell in the column is the current year, I want it to paste the entire row into a new sheet. Currently when I need to create a YTD sales spreadsheet I just go through each tab of each workbook by hand, filter the year sold column to the current year, and copy/paste the values over to the new sheet.

So in plain English:
For all sheets in this workbook, if the value in any cell in the year sold column equals 2019, then copy and paste the entire row into a new sheet, and at the end of all of the pasting of these value, sum each column of sales price, cost, net profit.

Any ideas on how I can create a macro to handle this for me?

Thanks in advance

Mumps, the destination workbook would just be the same file for each year. I have files from 2016 through 2019. I just want to have a macro embedded in my personal.xlsx that I can run on any file that I open. I currently don't have the files on the computer I'm working on now.

dangelor, the files do indeed have headers on each worksheet that are matching across all files. I'll try that code and see if I can get it to work.

Thanks

Originally Posted by dangelor

Assuming the data in the worksheets has a header in row 1, possibly...

Please Login or Register  to view this content.

dangelor,

this is running into an error. .AutoFilter field:=YrSoldCol.Column, Criteria1:="=2019" says object variable or With block variable not set.

How do I upload a copy of a spreadsheet here? That way you can have something to work off of.

Thanks

this is running into an error

Is there a column heading of "Year Sold"?

How do I upload a copy of a spreadsheet here?

Click on the "Go Advanced" button and then Manage Attachments.

You have worksheets that do not have a 'Year Sold' column. Remove those worksheets and run the code.

Hi !

No need to delete anything but just check if expected column exists …

I meant to upload just one tab dumbed down so it is less to look at. I just added a clean file of just that sheet with only 5 columns. I'm still getting the same error

It worked for me....

Does it matter where this Macro is stored? I have it in my PERSONAL.XLSX workbook.

The code is written for the workbook containing the code. As it is, if you run it from your personal.xlsb workbook, it will attempt to manipulate the data in the personal.xlsb workbook. To run it from you personal.xlsb workbook, you will need to change any reference to ThisWorkbook to either ActiveWorkbook or the the name of the workbook you want to manipulate; e.g.,Workbooks("Report.xlsx").

Thanks, that is why it isn't working properly. I'll add the macro to each workbook and run it.