Specify sheets for data extract

You can replace that line with something like this:

Please Login or Register  to view this content.

Or as an If statement if you're more comfortable with that:

Please Login or Register  to view this content.

Or if you have lots of sheet names you want to perform actions on then you could put them in a table somewhere in the workbook and when you're looping through the sheets just test if the sheet name appears in that table.

BSB

Hi BSB,

In a stand alone workbook that worked brilliant and rep added. However, when transferred to my live workbook, it's highlighted a different issue. The "real world" values in the source sheets are set through formula and it's that formula that is being copied. Can you think of a way to adjust my code so that it pastes values.

Also, I think that because it's formula it's copy all 500 rows per sheet. So is there a way of adding in a criteria that if the value of a cell = "" then don't copy?

Thanks

MM

You can certainly have code that will paste values and you can certainly have it only copy a "used range", i.e. ignore any rows where the formulas return a nil value.

To adjust the code it would be helpful to see what the real data looks like. Do the formulas on the unused rows leave the cells blank, or show a zero, or show an error code?
Also would the data have blank rows in the middle? i.e. would there be a student #13 then #14 is blank but #15 is populated?

BSB

Originally Posted by MagicMan

I'll try an mock it up so it presents as a formula and attach. (It's actually an aggregate formula but I'll do my best to create something).

As long as I know what the formulas in the unused rows show as a result then that's all I'd need to amend the code you have.
The process will be slightly different based on the results.

BSB

Thanks BSB, previous post amended.

Try this:

Please Login or Register  to view this content.

I've commented out some of the Dim lines that aren't required with this approach.

BSB

Hello everybody (I'm french, and not english frendly)

Accordingly with BadlySpelledBuoy "it would be helpful to see what the real data looks like."
When there is no data to control the process, it's difficult.

This Is not the solution : To control a list of sheets i use an array which list the only items allowed (IsStringInArray) rather to use "Select case" or multiple "If"
I found many constants in the original subroutine which annoying me to check the process.

BadlySpelledBuoy
I dont understand where you got these sheet names
If sht.Name = "GDPR SF Extract" Or sht.Name = "DSE Trg SF Extract" Or sht.Name = "DSE *** SF Extract" Or sht.Name = "CSA SF Extract" Then
The above function can be called like this

if IsStrInArray(sht.Name, 1, Array("GDPR SF Extract","DSE Trg SF Extract","DSE *** SF Extract","CSA SF Extract") Then

Function IsStringInArray(str, iStart, arr)
i = iStart
IsStringInArray = False
Do
If i <= UBound(arr) Then
If LCase(arr(i)) Like LCase(str) Then IsStringInArray = True: Exit Do
End If
i = i + 1
Loop Until i = UBound(arr) + 1
End Function

BSB...

That's bang on! Thank you 😊

Takes a little while in my real life work book, but there's significantly more sheets for it to process.

Thank you SOOO MUCH!

Virtual case of beer on its way.

MM

You're more than welcome! Glad I could help

I shall look forward to the virtual beers because my virtual drinks cabinet is currently bare!

Good luck with the project and give us a shout if you need more help.

BSB

Sorry, i dont saw your data and the solution of BadlySpelledBuoy is fine.