Basic Copy/Paste, multiple sheets and Databases

The forum is not allowing me to post the workbook. So half of what I said does not make sense. Please let me know how I can post the WB. Thank you :) 

Edit, here is a link to the WB. Hopefully this works. Thank you. 

Workbook for view

file... maybe?

Well... I have tried several times with multiple links to google drive. Maybe the person who replies will be able to see the link and see the file. If not, please let me know how I can post the file. Thank you. 

Hopefully this will help it to make sense. 

Hello Sean,

I'm having some trouble reconciling your explanation with the macro that you supplied (with the error showing).

As a starting point, the error that you have received is just a compile error meaning that items or characters etc. have been omitted or placed incorrectly. So, it should look like this instead:-

Sheets("Sheet1").Range("B8").Value = Sheets("Sheet1").Range("A11").Value

However, from what I gather, you need this value (A11) copy/pasted to multiple sheets which means you will have some sheets that need to be excluded from the copy/paste. Hence, a code that looks something like this may be what you need:-
 

Option Explicit
Sub Test()

    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    
        For Each ws In Worksheets
                If ws.Name <> "External DB 9-17" And ws.Name <> "External DB 9-18" Then  '----> Add any other sheets to be excluded.
                      ws.Range("B8").Value = ws.Range("A11").Value
                End If
        Next ws
    
    Application.ScreenUpdating = True

End Sub

To upload a sample workbook, first upload it to a file sharing site such as WeTransfer or Drop Box then post the link to your file back here. Click on the link symbol at the top of the reply form if you want to name your link. You should find a box where you can add text to name the link and the box directly below it is where you add the link to your file. Please ensure that the sample is an exact replica of your actual workbook and, if your data is sensitive, then please use dummy data.

Cheerio,

vcoolio.

https://www.dropbox.com/scl/fi/jjydpsqz1ge8jz8f16me8/Testing-for-CS.xlsx?dl=0&rlkey=9d3nus3xd1fcgzwdqboev5aqk

Stripped the link again. Maybe just copy and paste this and it should work.

Hello Sean,

I've reported the 'link stripping' issues that you have been experiencing to our IT Team and await their advices.

With the sample workbook that you have provided, I refer you to your comment:-

We will need to update weekly the sheet names in the VBA to match the sheets, because the sheet names will change weekly to match the future dates

There shouldn't need to be any need to continually change the VBA code to suit new entries as this would defeat the purpose of automating the workbook. So, with some minor modifications to your workbook, we should be able to avoid this. Are you open to suggestions?

Furthermore, from your sample and using  "External DB 9-17"  as the source sheet and 9-17 as the destination sheet as our example, I can't reconcile the source data with the destination data. As far as I can tell, you need the data from the source sheet in Columns B and H only (starting from row 9) to be transferred to the destination sheet to Columns A and C. However, in our mentioned example, I do not see where the times placed in Column C of the destination sheet come from. I do not see any mention of 7:00 - 3:00 for Nurse 1 and 7:00 - 4:30 for Nurse 2 in the source sheet. I'm referring to your 'new view' BTW.

As per a proper data base set out, I'm assuming that there is only one employee name per cell in Column B of the source sheets however there are multiple time spans in Column H (the destination sheet shows only one time span per employee which I'd assume is how you need the 'new view' to be ). i don't follow how you were hoping to display these in the destination sheets.

Going back to the destination sheets, you have instructions in Row 1 (I'm assuming for other Users) which mention having drop downs with name values in them. I do not see any drop downs. Where are they supposed to be placed?

We need you to enlighten us further.

Cheerio,

vcoolio.

Vcoolio,

As always (not my first time on here) thank you for being patient with me and my mutterings and taking the time to understand. I've edited the workbook hopefully for better understanding of what I'm looking for. I've matched the data for ease of understanding also. I understood your confusion after going back and looking at it. I made edits to better coordinate the transferring data on the workbook. I updated the file on dropbox.

https://www.dropbox.com/scl/fi/jjydpsqz1ge8jz8f16me8/Testing-for-CS.xlsx?dl=0&rlkey=9d3nus3xd1fcgzwdqboev5aqk

"As per a proper data base set out, I'm assuming that there is only one employee name per cell in Column B". <---- Yes, no dropdown box here.

"however there are multiple time spans in Column H" <------ Each line is different as each nurse has a different shift to cover 24 hour operations. Some have split shifts, and some data shows time off (PTO) or MCC To Holder (is not a working shift, they are off this day). Those that are off that day ("PTO" or "MCC To Holder") do not need to transfer to the dated sheet, ex 9-17. 

"which mention having drop downs with name values in them. I do not see any drop downs. Where are they supposed to be placed?" <---- Currently there are drop downs to select names. I do not want this on the new sheet. All 150+ nurses are able to be populated by dropdown. This is awful. 

I hope I covered everything, and this post is more enlightening! I've finally had a day off to concentrate on this. Feel free to take any and every liberty concerning the database transferring of information between these sheets. Any recommendation that you may have concerning the VBA, I would be extremely grateful. Especially since my effort did not make any headway.

Respectfully, Sean

Hello Sean,

Thanks for the updated workbook and the further information. Things are clearer now. While I have a look at a solution for you, could you please clarify if there is a drop down selection in each cell of Column B (Nurse Names) of each destination sheet (e.g. 9-17).

Thank you,

vcoolio.

Hello Sean,

Well, hopefully this may help:

Option Explicit
Sub Test()
    
    SheetNames
    
    Dim shVal As String: shVal = ActiveSheet.Range("M1").Value
    Dim wsDB As Worksheet: Set wsDB = Sheets(shVal)
    Dim lr As Long: lr = wsDB.Range("H" & Rows.Count).End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    wsDB.Range("I8:I" & lr) = "=IF(OR(LEFT(H8,3)=""MCC"",LEFT(H8,3)=""PTO""),TRUE,FALSE)"
    
            With wsDB.Range("B6", wsDB.Range("I" & wsDB.Rows.Count).End(xlUp))
                    .AutoFilter 8, False
                    .Columns(7).Offset(1).Copy ActiveSheet.Range("C" & Rows.Count).End(3)(2)
                    .Columns(1).Offset(1).Copy ActiveSheet.Range("A" & Rows.Count).End(3)(2)
                    .AutoFilter
            End With
            
            wsDB.Columns("I").Clear
            
            With ActiveSheet.UsedRange
                    .WrapText = False
                    .Rows.AutoFit
                    .Columns.AutoFit
            End With
            
    Application.ScreenUpdating = True

End Sub

Sub SheetNames()

    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
     
    For Each ws In Worksheets
            If Len(ws.Name) <= 5 Then
                 ws.[M1] = "External DB" & " " & ws.Name
            End If
            ws.Columns.AutoFit
    Next ws
       
     Application.ScreenUpdating = True
     
End Sub

You'll note that there are actually two codes above. The "Test" code calls the "SheetNames" code which will keep the "SheetNames" code permanently active without User intervention. I'll explain shortly.

I've attached your sample workbook here. You should be able to then follow what I've done.

You'll note that in the "Test" code above, there is a formula which is placed into Column I each time the code is run. Column I is now a helper column.This formula defines which items to leave behind when the code is run. As per your instructions, these items are the times with "MCC" and "PTO" included with them. The formulae are cleared with each run of the code, at the end of the copy/paste procedure.

The "SheetNames" code will place the DB sheet names into cell M1 of each of the relevant destination sheets (e.g. 9-17, 9-18....). We now have reference points for each source/destination sheet to interact.

In the "Test" code, you'll note the reference to "ActiveSheet". Once you open one of the destination sheets (9-17, 9-18 etc....), it will become the "Activesheet" as you obviously need to operate from one sheet at a time. I found this method to be the most effective in your case. To run the code from an Activesheet, create a button on each sheet and simply assign the "Test" code to it. It would probably be an even better idea to simply assign the "Test" code to a short cut key which you'll be able to use on any of the source sheets eliminating the need for a button on each sheet. Please remember that the codes will only work from the "Activesheet".

Getting back to the "SheetNames" code, each time new source/destination sheets are added, the code, being permanently active by being called by the "Test" code, will continue to add the reference points for sheet interaction as mentioned earlier. You'll never have to worry about altering the code to suit the new worksheets (refer to post #8).

You'll also note in the sample that I've done a little tidying up but I only used the "External DB 9-17" and "9-17" sheets for testing. Note carefully that I've scripted the codes based on the sheet set out that you supplied so I hope that your actual workbook is exactly the same.

Excel requires order to operate correctly and this will always mean that source sheets are set out the same column/row wise. The same for destination sheets. The sheets that I have tidied up are how I'd assume your actual worksheets are like.

Anyway, play away with the sample above and let us know what you think.

I hope that this helps.

Cheerio,

vcoolio.

Hello Sean,

Here's the reply from Admin in regards to the links being stripped:-

This is by design. Links are only allowed to members who are quite active on the forum and is determined by the number of messages. If they aren't allowed to post links, they normally don't have the option in the text editor. That's why links are stripped.

Eagleeyes011 should insert the full links so it will be possible to visit them, like in post 9.

I personally don't see the point. However................

Cheerio,

vcoolio.