Hello,
Admittedly I'm pretty new to macros, but I'm trying. I am wanting to combine data with the sheet names from multiple worksheets in a separate work sheet. My spreadsheets have to be locked, and we we already are using a macro to copy a master copy. Basically our users open the spreadsheet and use the first macro to make a copy of the Master each time we need a new worksheet, so I need that page to not be copied as well since it will be blank. I found a code that completed the general idea, minus having to exclude a worksheet and the sheets with data having to be locked. I altered it quite a bit and I thought I had both issues addressed but I keep getting some errors that I'm not sure how to fix. I get an unknown identifier for what I thought would work for the 2 separate names (pretty sure it reads as "not "this" or "that""). I get an error saying "Cannot jump to 'ActiveSheet' because it's hidden" when trying to do the last paste for what I came up with to address the locked pages issue. I made sure the worksheet all the data is going to is unlocked and even tried it with the other sheets unlocked to test if it was jumping to another sheet and I still get the same error (and it works great up until that point. I'm quite perplexed as I can't see where the code is going wrong.
Here is what I have (I've been testing the 2 issues separately, so I only have to change the ranges eventually, so forgive me if I miss something when combining them)
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> ("MASTER COPY") Or ("Master Control") Then
Sht.Select
Range("V8").Copy
Sheets("Master Control").Select
Range("AB2").PasteSpecial Paste:=xlPasteValues
Sht.Select
Range("A2:A14").Copy
Sheets("Master Control").Select
Range("AC2").PasteSpecial Paste:=xlPasteValues
Range("AB:AC").Copy
Range("A65536").End(xlUp).Offset(1, 0).Select
[I][B]ActiveSheet.Paste[/B][/I]
Sht.Select
Range("AB:AC").Delete
Else
End If
Next Sht
End Sub
In Bolded Italizied part is the pasting part that keeps giving me the error. What I thought to do to get around the issue of the pages the data are on being locked is to have a cell out of the way display the name (in this test spreadsheet "V8"), copy that paste to part of the Control Master then go back and copy the data and paste that next to the name. Then copy both column and past at the beginning of the sheet. And it works up until pasting the columns with the name and data. I've tried (at least I think that's what I command I used did providing I was reading it correctly) telling it to go to the right sheet again, and even the paste values only command but still no luck. I've also tried various ways to organize the name part with every way I could find on the internet, but I still get the unknown identifier issue. I am also made sure that the sheets I don't want copied were named and saved prior to putting in the macro.
Also our data is in columns instead of a row, but I am really not sure there's away to get the tab name to copy for each data entry. I think I have an idea to help users sort it but unfortunately not automatically, but it's better than what we currently have.
I'm really hoping someone can help me understand where I went wrong, and help me get it working.