Forum Discussion

Michael Simpler's avatar
Michael Simpler
Copper Contributor
Mar 29, 2017

Macro enabled workbooks wont open in Excel 2016

I have two sheets in a workbook. One is protected accept about two dozen cells.  The other is not protected.  When I run a macro to copy and paste 19 cells from the unprotected sheet to 19 cells in t...
Macros & VBA
Need Help
Wyn Hopkins's avatar
Wyn Hopkins
MVP
Apr 03, 2017

Hi Michael

 

Can you clarify what your issue is and post the code for your copy/paste action

 

Thanks

 

Wyn

  • Michael Simpler's avatar
    Michael Simpler
    Copper Contributor
    Apr 03, 2017

    Hi Wyn, and thanks for the reply.  I just resolved the issue with a paste special. 

     

    BUT, I have another issue you may be able to help me with.  I have a user form to search previous records in a table on another sheet named "data base" and load them in to a worksheet.  The form has a list box displaying the first 6 columns from the data base, a textbox to enter what to search for, and control buttons.  The Find button has a find next code, and the load button copies info from the selected row of data in the data base to various cells in a work sheet.

     

    The codes work great, but they work in the "data base" sheet, and totally ignor the list box on the user form. When I step through the code in split screen I see it go right to the sheet.  How do I get the command buttons to work with the list box in the same user form?

     

    Thanks, Mike

     

    Option Explicit

    __________________________________________________________
    Private Sub CommandButton3_Click()
    ' laods record into work sheet
    '
    ' Clearing user worksheet before loading
        Sheets("user").Select
        Range("D4").Select
        Selection.ClearContents
        Range("D6:E14").Select
        Selection.ClearContents
        Range("F4").Select
        Selection.ClearContents
        Range("E6:E14").Select
        Selection.ClearContents
        Range("H4").Select
        Selection.ClearContents
        Range("G6:G14").Select
        Selection.ClearContents
        Range("J4").Select
        Selection.ClearContents
        Range("I6:I14").Select
        Selection.ClearContents
        Range("L4").Select
        Selection.ClearContents
        Range("K6:K14").Select
        Selection.ClearContents
        Range("N4").Select
        Selection.ClearContents
        Range("M6:M14").Select
        Selection.ClearContents
        Range("P4").Select
        Selection.ClearContents
        Range("O6:O14").Select
        Selection.ClearContents
        Range("R4").Select
        Selection.ClearContents
        Range("Q6:Q14").Select
        Selection.ClearContents
        Range("T4").Select
        Selection.ClearContents
        Range("S6:S14").Select
        Selection.ClearContents
        Range("F18").Select
        Selection.ClearContents
        Range("E20:E28").Select
        Selection.ClearContents
        Range("H18").Select
        Selection.ClearContents
        Range("G20:G28").Select
        Selection.ClearContents
        Range("J18").Select
        Selection.ClearContents
        Range("I20:I28").Select
        Selection.ClearContents
        Range("L18").Select
        Selection.ClearContents
        Range("K20:K28").Select
        Selection.ClearContents
        Range("N18").Select
        Selection.ClearContents
        Range("M20:M28").Select
        Selection.ClearContents
        Range("P18").Select
        Selection.ClearContents
        Range("O20:O28").Select
        Selection.ClearContents
        Range("R18").Select
        Selection.ClearContents
        Range("Q20:Q28").Select
        Selection.ClearContents
        Range("T18:T29").Select
        Selection.ClearContents
        Range("C_Comments") = "Verylak base, Hentzen R, B, W, L, Verylak N, Y"
        Range("C_Sales_Order_No.").Select
        Selection.ClearContents
        Range("C_Customer_Name").Select
        Selection.ClearContents
        Range("C_Color_Name") = "none"
        Range("C_Manufacturer") = "none"
        Range("C_Date") = Date
        Range("C_Specie").Select
        Selection.ClearContents
        Range("C_New_Batch_Base").Select
        Selection.ClearContents
        
    ' Loading
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 1).Select
        Selection.Copy
        Sheets("user").Select
        Range("C_Sales_Order_No.").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 2).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("user").Select
        Range("C_Customer_Name").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 3).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("user").Select
        Range("C_Specie").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 4).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("user").Select
        Range("C_Color_Name").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 5).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("user").Select
        Range("C_Manufacturer").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 6).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("user").Select
        Range("C_Date").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 7).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("user").Select
        Range("C_Comments").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 8).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("user").Select
        Range("C_Base").Select
        ActiveSheet.Paste
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 9).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("user").Select
        Range("C_Red").Select
        ActiveSheet.Paste
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 10).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("user").Select
        Range("C_Brown").Select
        ActiveSheet.Paste
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 11).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("user").Select
        Range("C_Black").Select
        ActiveSheet.Paste
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 12).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("user").Select
        Range("C_Yellow").Select
        ActiveSheet.Paste
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 13).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("user").Select
        Range("C_White").Select
        ActiveSheet.Paste
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 14).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("user").Select
        Range("C_Blue").Select
        ActiveSheet.Paste
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 15).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("user").Select
        Range("C_Misc1").Select
        ActiveSheet.Paste
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 16).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("user").Select
        Range("C_Misc2").Select
        ActiveSheet.Paste
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 17).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("user").Select
        Range("C_TotalVolume").Select
        ActiveSheet.Paste
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 18).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("user").Select
        Range("C_Can").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Sheets("data base").Select
        Cells(Application.ActiveCell.Row, 19).Select
        Application.CutCopyMode = False
        Selection.Copy
        Range("A2").Select
        Sheets("user").Select
        Range("C_CanStain").Select
        ActiveSheet.Paste
        Range("T20:T28").Select
        Application.CutCopyMode = False
        Selection.Copy
        Range("D6:D14").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        Range("C_New_Batch_Base").Select
        frmSearch.Hide
    End Sub

    ____________________________________________________________________

    Private Sub CommandButton4_Click()
    ' search with FIND NEXT loop

        Dim SearchRange As Range
        Dim WantCell As Range
        Dim Wanted As String
        Dim FirstWantCell As String



        Wanted = tbxSenter.Value

        Set SearchRange = Range("A2", Range("F2").End(xlDown))

        Set WantCell = SearchRange.Find(What:=Wanted, MatchCase:=False, LookAt:=xlPart)

        If WantCell Is Nothing Then
            MsgBox "No records found."
        Else
            FirstWantCell = WantCell.Address

            Do
                WantCell.Select

                Set WantCell = SearchRange.FindNext(WantCell)
            Loop While WantCell.Address <> FirstWantCell
        End If
    End Sub
    _______________________________________________________________________

    Private Sub lbxSearchResult_Click()

    End Sub
    ____________________________________________________________________
    Private Sub UserForm_Click()

    End Sub

Resources