Basic Copy/Paste, multiple sheets and Databases

Eagleeyes011 Posts 14 Registration date Tuesday February 8, 2022 Status Member Last seen September 29, 2022 - Sep 22, 2022 at 01:10 PM
Eagleeyes011 Posts 14 Registration date Tuesday February 8, 2022 Status Member Last seen September 29, 2022 - Sep 29, 2022 at 07:25 AM

Hello all!

I’m not good with this, so I’m am reaching out to those who are. I can record and run basic macros, but this is above my skill level. I’ve looked and tried a basic copy and paste VBA and received the error seen in the picture. I was going to try and upscale this once I got it to work, but I’m apparently not even this good. This workbook is being used to monitor nurses schedules with areas for individual daily edits like computer issues and such, columns I-J.

Given the output from the database (Kronos) listed as external DB with dates in each page, we only need the cells moved from the DB, B8 (down the column about 100’ish cells) with the coordinated cell H8 (same range column). The DB report is pulled weekly, so those pages will be refreshed weekly per date. The date is not a range when pulling from the DB, but a daily value. It looks similar to the DB here, just more refined. Didn’t copy due to privacy data being there.

We currently have column H in the DDB separated by in and out times in the dated sheets, but having it in one box is sufficient. Look at cell A7 in the dated sheets (9-17) in the WB. Above is current, below is acceptable.

Column H in the DB has multiple different formats, listed. If these can just be copied to column C in the dated sheets that would be perfect. There are too many variables I believe to break out. Some people work split shifts, leave, holiday pay, etc…

I tried to post the WB, but it looks like it did not paste. I will reply with it if it did not paste.

I hope this makes sense. Thank you in advanced for your help. Sean

11 replies

Eagleeyes011 Posts 14 Registration date Tuesday February 8, 2022 Status Member Last seen September 29, 2022
Updated on Sep 22, 2022 at 01:50 PM

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

0
Eagleeyes011 Posts 14 Registration date Tuesday February 8, 2022 Status Member Last seen September 29, 2022
Updated on Sep 22, 2022 at 01:52 PM

file... maybe?

0
Eagleeyes011 Posts 14 Registration date Tuesday February 8, 2022 Status Member Last seen September 29, 2022
Sep 22, 2022 at 01:54 PM

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. 

0
Eagleeyes011 Posts 14 Registration date Tuesday February 8, 2022 Status Member Last seen September 29, 2022
Sep 22, 2022 at 02:19 PM

Hopefully this will help it to make sense. 

0

Didn't find the answer you are looking for?

Ask a question
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Sep 22, 2022 at 09:49 PM

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.

0
Eagleeyes011 Posts 14 Registration date Tuesday February 8, 2022 Status Member Last seen September 29, 2022
Updated on Sep 25, 2022 at 07:57 AM

Dropbox Link

I tried google drive last time, we'll see if dropbox works better. Last time when I made the post, it stripped the link. The macro I supplied earlier, I do not think will work. And the sheet names were different then also, so I understand your confusion. 

Each "External DB" sheet is associated with a date of use with the corresponding dated sheet. So "External DB 9-17" corresponds with sheet 9-17. We use these sheets for daily tracking of personnel, and make edits to each line far off to the right. The personnel names change daily, and this is a 24/7 operation. 

I want to bring the nurses name and shift times from the sheet "External database 9-17" into sheet "9-17," and have it do that for each sheet with corresponding dates, and to have it organized by time in. Like dayshift, afternoon, evening. I imagine we will just need to sort after the data is brought in. It needs to be repeatable with changing dates. 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. 

Thank you again. 

0
Eagleeyes011 Posts 14 Registration date Tuesday February 8, 2022 Status Member Last seen September 29, 2022
Updated on Sep 28, 2022 at 08:34 AM

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.

0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Sep 25, 2022 at 11:31 PM

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.

0
Eagleeyes011 Posts 14 Registration date Tuesday February 8, 2022 Status Member Last seen September 29, 2022
Sep 26, 2022 at 03:44 PM

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

0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Sep 26, 2022 at 08:45 PM

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.

0
Eagleeyes011 Posts 14 Registration date Tuesday February 8, 2022 Status Member Last seen September 29, 2022
Sep 27, 2022 at 06:07 AM

There may be a drop down there now. I was not able to clear it when I copied the cells from the original workbook. It is not intentional and is welcome to be removed. 
 

There was also drop downs for the time cells, but I think I was able to clear those. 

0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Sep 27, 2022 at 08:04 AM

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.

0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Sep 29, 2022 at 12:27 AM

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.

0
Eagleeyes011 Posts 14 Registration date Tuesday February 8, 2022 Status Member Last seen September 29, 2022
Sep 29, 2022 at 07:25 AM

Not a problem. Coming from a cyber security background, I understand the logic there. I’m surprised it’s not more stringent to be honest if that is their rationale.

I have not had the opportunity to try the code yet. Had to get back to work, and I really want to focus on this when I put it into practice. 

I appreciate the very thorough replies on everything in this thread. I would highly recommend this website for anyone’s needs considering the level of professionalism and thoroughness shown by your ability and willingness to help with my multiple concerns.

Very respectfully, Sean  

0