Move data in several worksheets in same workbook

[Mick S]

I have a file that we use to show daily sales and orders. The original file had one tab. There is data in columns k through z. Columns M and S contain month to date (MTD) values. Columns L & R show current day sales and is calculated as the delta between today's MTD sales and yesterday's MTD sales.

The file is linked to another file that contains the current day's MTD sales. Before I update the MTD sales file (and then update the link to said file) I run a macro I built that would move data from columns M and S (current data) to columns B and C (yesterday data). Then I update the link and the "current day" sales are calculated as simple subtraction in columns L & R.

This worked fine but now we are putting in more tabs to show detail. How do I make reference to each of the new tabs? The column references are the same, I just need to reference each of the individual sheets. My original code was:

Please Login or Register  to view this content.

For example we now have tabs called "NorthEast", "West", "SouthEast", etc.

[AlvaroSiza]

You need a loop to iterate through the worksheets and then perform the preferred task.

Please Login or Register  to view this content.

If there are one or more worksheets which you want excluded, you will need to add some If/Then logic below the For Each line to test for the worksheet name prior to performing the actions (e.g. a summary sheet is often excluded).

Please Login or Register  to view this content.

[AlvaroSiza]

Something like this perhaps (cleaned up some others). Please test on a copy and report back results. I included an excluded worksheet ("Summary"). If you would like to exclude one, you will need to change to the actual name of the worksheet to be excluded. Otherwise, just comment out that line.

Please Login or Register  to view this content.

Without the workbook, it's hard to know if the .Copy / .PasteSpecial is necessary. There is potentially room to skinny the code further via the '.Value = .Value' or '.Copy Destination:=' methods

[Mick S]

I would attach the file but it lists too much sensitive data like customer names etc.

I tried the code but it says there is a compile error: named argument not found. It highlights the sub in yellow and the Format:= under 'copy and move MTD Orders

Please Login or Register  to view this content.

[Mick S]

Playing around with it a little bit it appears to not like the format specification. I commented it out and the columns moved. Also I guess the .goto range needs to be specific to a particular worksheet?

[AlvaroSiza]

Correct. You will need to specify the sheet name and range that you would like to land when it is finished.

Example

Please Login or Register  to view this content.

[Mick S]

Ok got it. One last question. What do I put if there are two tabs that I want to exclude from the loop? I tried the following but it does not seem to work.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[AlvaroSiza]

Try

Please Login or Register  to view this content.

[Mick S]

"And" worked. Thank you very much for your help. I definitely need to brush up on looping.


For Each ws In wb.Sheets
If ws.Name <> "Email Cover" And ws.Name <> "Lookups" Then
With ws