Copy/paste using VBA

Peterw_2506 · Jul 16, 2011

Hello fellow excel users and VBA guru’s,

I am trying to develop up a large database for a finishes schedule that I have to produce. I have a number of worksheets in a workbook that act as my database, these various worksheets are named “Doors; Tiles; Walls; Ceilings; Lights; Power; Bathroom; FF&E”. In column B of the database is a unique identification code for each row e.g. xyz-01, making it easy to identify the product. The are about 500 rows in each tab.

The finishes schedule portion of the workbook also has a number of worksheets of which the info from the database worksheet portion is cut and pasted/referenced into (trying to use VBA to do this). These various worksheets are named “Type A; Type A (C); Type B; Type C; Type C (C); Type D; Type E; Type F; Type G; Type H; Type I; Type J; Type J (C); Type M; Type N; Type O; Type P; Type R; and final, Type ZZ”.

All sheets have the same layout from column B through to column P. So whenever the unique ID code is assigned in column B in the finishes schedule portion of the workbook the information in the database section is automatically copied from column C to P into it respective row in the schedule.

I understand that only VBA can do this, of which I have zero experience in. I am presently doing a crash course in VBA (reading) but time will beat me if I try and do it solely myself. If anybody can help me on this issue I would be greatly indebted to you. Also I am using excel 2010 if that helps in anyway.

Regards, Peter

Ralajer · Jul 16, 2011

Peter,

Let me see if I understand your situation and requirements. You have a single workbook that has 27 sheets. 8 sheets (Doors;Tiles;etc) serve as your database of item information that you wish to have columns C-P copied from when you enter the Unique Identifier in column B of any of the remaining 19 sheets (Type **). The copy/paste operation should be automatic and triggered by a change in column B of the schedule sheet.

Is that an accurate restatement of what you have and need? Here are some follow-up questions.

• On the schedule sheets what row does the data start?
• Are all the values in Column B of the schedule sheets manually entered or are there any formula?
• Do all the schedule sheets start with 'Type'?

Ralajer · Jul 16, 2011

Assuming that I understood your post correctly and the answers to my questions were [2, all manual entry, and Yes] respectively the following should work.

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    '// Code need only execute if change occured on specific sheets
    If InStr(1, Sh.Name, "Type", vbTextCompare) <> 1 Then Exit Sub
    
    Dim changedRng As Range
    Set changedRng = Intersect(Sh.Range("B2:B" & Sh.Rows.Count), Target)
    
    '// Code need only execute if change occured  in as specfic range
    If changedRng Is Nothing Then Exit Sub
    
    Dim cell As Range
    
    Application.EnableEvents = False
    '// Loop used to handle deleting items in column B
    For Each cell In changedRng
        '// Clear Content C+P to avoid data remaining if Item wasn't found
        cell.Offset(0, 1).Resize(1, 14).ClearContents
        '// Test if blank to prevent message box when cells content was deleted
        If cell.Value <> "" Then
            If CopyAndPaste(cell) = False Then
                MsgBox cell.Value & vbCr & "    Not Found in DB"
            End If
        End If
    Next cell
    Application.EnableEvents = True
End Sub

Private Function CopyAndPaste(matchToRng As Range) As Boolean
    Dim lookInSheets As Variant
    Dim s As Variant
    Dim lookInRng As Range
    Dim found As Range
    '// Default Return
    CopyAndPaste = False
    
    lookInSheets = Array("Doors", "Tiles", "Walls", _
                        "Ceilings", "Lights", "Power", "Bathroom", "FF&E")
    On Error Resume Next
    For Each s In lookInSheets
        Set lookInRng = Sheets(s).Columns(2)
        Set found = lookInRng.Find( _
                                    What:=matchToRng.Value, _
                                    LookIn:=xlValues, _
                                    LookAt:=xlWhole)
        If Not found Is Nothing Then GoTo itemFound
    Next s
    On Error GoTo 0
    Exit Function
itemFound:
    '// Copy Values to Scedule Sheet
    matchToRng.Resize(1, 15).Value = found.Resize(1, 15).Value
    CopyAndPaste = True
End Function

The above needs to be placed in the ThisWorkbook code Module and the file has to be saved either as xlsm or xls. Let me know if you have questions.

Peterw_2506 · Jul 17, 2011

Hi Rob,

Firstly, thank you very much for trying to help me, I'm greatly appreciative.

I have cut and pasted your code into the module, but it doesn't want to seem to execute. I'm certain it is something from my end. Is it possible for me to email to you the workbook so all questions are answered and the slight modifications I have made you are aware of.

Thank you in advance.

regards, Peter

Copy/paste using VBA

Peterw_2506

Board Regular

Excel Facts

Ralajer

Active Member

Ralajer

Active Member

Peterw_2506

Board Regular

Similar threads

Forum statistics

Share this page

Copy/paste using VBA

Peterw_2506

Board Regular

Excel Facts

Ralajer

Active Member

Ralajer

Active Member

Peterw_2506

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock