Copying data from cells onto multiple workbooks VBA error

dreen

dreen

Board Regular
Joined
Nov 20, 2019
Messages
52
Hello, I can't seem to get my module 8 to paste the values of Sheet1 (workbook where the VBA code is) onto another workbook's cells, I will briefly explain my code:

I have three workbooks, one workbook (with Sheet1 being the "Operator" sheet) where the VBA code is written in, and another two external workbooks with different file paths called "Changes" (file path is the: Database_IRR 20-2S New.xlsm in my code, this is the CHANGES Database) and "HE171" (file path is the: Technology_Changes\Changes_Database_IRR_20-2S_New.xlsm in my code, this is the MAIN Database).

1) IF the operator hits "YES" on Commandbutton1, I want the code to check if the value in cell "H4" from Sheet1 is present in Column A of the "HE 171" sheet from the MAIN Database then,

2) IF the value in "H4" is PRESENT in the MAIN Database, I want the code to check if the value in cell "H4" from Sheet1 is present in Column A of the "Changes" sheet from the CHANGES Database and IF the value of "H4" is PRESENT in the "CHANGES" sheet I want the code to use Module 13 (I have not posted it) to set the date and time stamp in two columns of the "CHANGES" sheet, and Module 8 to send over the values from certain Column "K" to cells inside of the "CHANGES" sheet (For example, I want module 8 to filter the value of "H4" in Column A and have it in row 2 as row 1 has my headings, and place the value of "K30" from Sheet1 to cell (1,6) in the "CHANGES" sheet)

2.1) IF the value in "H4" is PRESENT in the MAIN Database, and IF the value of "H4" is NOT Present in the "CHANGES" sheet I want the code to use Module 14 (have not posted it) to add the value of "H4" into Column A of a NEW row in the "CHANGES" sheet in the CHANGES Database, Module 13 (I have not posted it) to set the date and time stamp in two columns of the "CHANGES" sheet, and Module 8 to send over the values from certain Column "K" to cells inside of the "CHANGES" sheet

3) IF the value in "H4" is NOT Present in the MAIN Database, I want the code to use Module 7 (have not posted it) to add the value of "H4" into Column A of a NEW row in the "HE 171" sheet in the MAIN Database, Module 14 (have not posted it) to add a the value of "H4" into Column A of a NEW row in the "CHANGES" sheet, Module 13 (I have not posted it) to set the date and time stamp in two columns of the "CHANGES" sheet, and Module 8 to send over the values from certain Column "K" to cells inside of the "CHANGES" sheet

5) IF the operator hits "NO" or "x" on Commandbutton1, I want the code to save and close both external workbooks (The MAIN Databse & CHANGES Database) with a password, and then just protect Sheet1 and keep it open with nothing cleared

VBA Code: 
Option Explicit


Dim Cd As Workbook
Dim Md As Workbook

Dim Changes As Worksheet
Dim HE171 As Worksheet

Dim nConfirmation As Integer

'Actions for when the "Confirm Changes" button is clicked
Private Sub CommandButton1_Click()


    Set Cd = Workbooks.Open("\FILEPATH\Technology_Changes\Changes_Database_IRR_20-2S_New.xlsm")
    Set Md = Workbooks.Open("\FILEPATH\Database_IRR 20-2S New.xlsm")


    Set Changes = Cd.Sheets("Changes")
      
    On Error Resume Next
    
    Set HE171 = Md.Sheets("HE 171")


    'Creating the "Yes or No" message box displayed when operators click the "Confirm Changes" button on the Operator Sheet
    nConfirmation = MsgBox("Do you want to send a notification about the sheet update?", vbInformation + vbYesNo, "Sheet Updates")

    'Declares the variable for the string that we will be finding, which is the key in this case (for the With statement)
    Dim FindString As String

    'Declares the variable for the range in which we will be locating the string (for the With statement)
    Dim RNG As Range

    'Sets the string we need to find as the key value which is in cell "H4" of the Operator sheet (for the With Statement)
    FindString = Sheet1.Range("H4").Value

    'Actions if "YES" is clicked when the "Confirm Changes" button is clicked on the Operator Sheet
    If nConfirmation = vbYes Then

        'Opens and activates the Main Database workbook, with "HE 171" as the active sheet
        HE171.Activate

        'Temporarily unprotects the Main Database Workbook and Operator sheet (this is the sheet the code is in)
        ActiveSheet.Unprotect "Swrf"
        Sheet1.Unprotect "Swrf"

        'Searches all of column A in the Main Database in sheet "HE 171" for the string(key)
        With ActiveSheet.Range("A:A")            'searches all of column A
            Set RNG = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        'End With
            '////////////////////////////////////////////////////////////////////////////

            'Actions if the key is present in column A of the MAIN database
            If Not RNG Is Nothing Then

                'Since Key is present in main database, now opens and sets the Changes_Database "Changes" Sheet as active contents
                Changes.Activate

                'Temporarily unprotects the Changes_Database
                ActiveSheet.Unprotect "Swrf"
                
                'Declares the variable for the string that we will be finding, which is the key in this case (for the With statement)
                Dim FindString2 As String
    
                'Declares the variable for the range in which we will be locating the string (for the With statement)
                Dim RNG2 As Range

                'Sets the string we need to find as the key value which is in cell "H4" of the Operator sheet (for the With Statement)
                FindString2 = Sheet1.Range("H4").Value

                'Searches all of column A in the Changes_Database "Changes" sheet for the string(key)
                With ActiveSheet.Range("A:A")    'searches all of column A
                    Set RNG2 = .Find(What:=FindString, _
                                    After:=.Cells(.Cells.Count), _
                                    LookIn:=xlValues, _
                                    LookAt:=xlWhole, _
                                    SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, _
                                    MatchCase:=False)
                

                    'Actions if the key is present in column A of the Changes_Database (So a change request was previously made for the key and it already has a row in the "Changes" sheet)
                    If Not RNG2 Is Nothing Then

                        'Calls module 13 to set the date and time of the requested change in the "Changes" sheet
                        Call TimeStamp

                        'Calls module 8 to send over the requested changes to the "Changes" sheet
                        Call SendChanges

                        'On Error Resume Next

                        'Protects the Changes_Database
                        ActiveSheet.Protect "Swrf"

                        '////////////////////////////////////////////////////////////////////////////

                        'Actions if the key DOES NOT exist in column A of the Changes_Database


                    Else

                        'Module 14: Adds a new row with the key to the Changes_Database
                        Call NewPart2

                        'Calls module 13 to set the date and time of the requested change in the "Changes" sheet
                        Call TimeStamp

                        'On Error Resume Next

                        'Calls module 8 to send over the requested changes to the "Changes" sheet
                        Call SendChanges

                    End If

               End With
            
            Else

                        'Module 7:  Adds a new row with the key to the MAIN Database
                        Call NewPart
    
                        'Module 14: Adds a new row with the key to the Changes_Database
                        Call NewPart2
    
                        'Module 13: to set the date and time of the requested change in the "Changes" sheet
                        Call TimeStamp
    
                        'Module 10: Fills in the date and time the key was created for the "HE 171" sheet
                        Call TimeStamp2
    
                        'On Error Resume Next
    
                        'Calls module 8 to send over the requested changes to the "Changes" sheet
                        Call SendChanges

            End If
        
        End With

      
            'Actions if "No" is clicked when the "Confirm Changes" button is clicked on the Operator Sheet
    Else

            '''''''If nConfirmation = vbNo Then


            'Protects Changes_Database (as it was activated after the Main Database and is therefore the active contents and saves/closes it
            Changes.Activate
            ActiveSheet.Protect "Swrf"
            ActiveWorkbook.Save
            ActiveWorkbook.Close SaveChanges:=True

            'Sets Main Database as active contents to protect it, save it and close it
            HE171.Activate
            ActiveSheet.Protect "Swrf"
            ActiveWorkbook.Save
            ActiveWorkbook.Close SaveChanges:=True

            'Protects Operator Sheet and saves it
            Sheet1.Protect "Swrf"
            'Workbook.Close SaveChanges:=True

    End If

End Sub


'**************************************************************************
'module 8, currently my code is not pasting an values from column k in my current workbook
'**************************************************************************

'Module 8: Sends the requested changes over to the "Changes" sheet

Sub SendChanges()

Set Cd = Workbooks.Open("\FILEPATH\Technology_Changes\Changes_Database_IRR_20-2S_New.xlsm")
Set Changes = Cd.Sheets("Changes")

Changes.Activate
ActiveSheet.Unprotect "Swarf"

'////////////////////////////////////////////////////////////////////////////'

'Only executes this macro if the the new/change requested value in column "K" of the Operator sheet has a numerical value present
If Sheet1.Range("K30").Value <> "" Then


'Filters the Changes_Database for the part name & process (the key) which is in cell "H4" of the Operator sheet
ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=Sheet1.Range("H4")
 
 
'Copies the changed content in cell "K30" from the Operator Sheet
Sheet1.Range("K30").Copy

'Finds the row in the Changes_Database that has matched all filters and;
'Pastes the value of cell "K30" into the matching parameter cell in the Changes_Database,which is in column 6 in this case
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 6).PasteSpecial xlPasteValues


'Removes all filters and shows all data'
ActiveSheet.ShowAllData


End If
'////////////////////////////////////////////////////////////////////////////'


'Repeats the If and Else code bordered with slashes "////", for all parameter changes in the K column ("KXX")'
If Sheet1.Range("K31").Value <> "" Then
ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=Sheet1.Range("H4")
Sheet1.Range("K31").Copy
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 7).PasteSpecial xlPasteValues

ActiveSheet.ShowAllData
End If


If Sheet1.Range("K32").Value <> "" Then
ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=Sheet1.Range("H4")
Sheet1.Range("K32").Copy
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 8).PasteSpecial xlPasteValues

ActiveSheet.ShowAllData
End If

'On Error Resume Next

Sheet1.Range("K30:K115").ClearContents

'On Error Resume Next

ActiveSheet.Protect "Swrf"
            ActiveWorkbook.Save
            ActiveWorkbook.Close SaveChanges:=True

End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Hi dreen. Seems like your "Sheet1" in module 8 should be either "Changes" or ActiveSheet. HTH. Dave
 
Upvote 0
NdNoviceHlp said:
Hi dreen. Seems like your "Sheet1" in module 8 should be either "Changes" or ActiveSheet. HTH. Dave
Click to expand...
Hi Dave,

In Module 8, the values in column "K" are first being checked (that they have a value inputted), and then being copied and then pasted into the respective cell in my "Changes" sheet
 
Upvote 0
U set the active sheet to "Changes", then go to Sheet1 for information which I believe then becomes the active sheet so following that instead of active sheet referring to "Changes" (as U want it seems) it is referring to Sheet1. If U get rid of the active sheet and replace it with the actual sheet name that U are referring to (ie. Changes or Sheet1), I believe that your difficulty can be resolved. Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
300
mumps
mumps
G
  • Question Question
Copying and pasting between two workbooks
Replies
1
Views
695
rlv01
R
L
  • Solved
Macro to open multiple workbooks and copy a range of data based on todays date. Then paste the data in master workbook in one sheet under each other.
Replies
8
Views
613
Luke1690
L
F
  • Solved
VBA to open all files in a folder, put the file's name in a cell and copy it to another opened workbook
Replies
5
Views
1K
feni1388
F
D
  • Question Question
VBA to check 2 cells of data and then copy and paste from multiple sheets to the data sheet
2
Replies
11
Views
421
danbates77
D

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top