Click here to Skip to main content
15,920,438 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm a portuguese engineer and I've recently started programming in Visual Basic on a button from a specific Worksheet named "Início" on a Workbook named by "Livro MQTEN". On the Worksheet "Início" I have one button with the following code:

VB
Private Sub CommandButton1_Click()
    Dim lngCount As Long
    Dim j As String
    Dim fileName As String
    Dim lngIndex As Long
    Dim strPath() As String
    Dim nome As String
    Dim folha As String
 
    ' Open the file dialog
    With Application.FileDialog(msoFileDialogOpen)
        .Title = "Selecione o ficheiro dos comboios realizados do dia"
        .InitialFileName = "Explor. *"
        .AllowMultiSelect = False
        .Show
        .Filters.Add "Excel files", "*.xlsx; *.xls", 1
 
        ' Display paths of each file selected
        For lngCount = 1 To .SelectedItems.Count
            'MsgBox .SelectedItems(lngCount)
            j = .SelectedItems(lngCount)
            'MsgBox (j)
            
            strPath() = Split(j, "\")   'Put the Parts of our path into an array
            lngIndex = UBound(strPath)
            fileName = strPath(lngIndex)    'Get the File Name from our array
            
            'MsgBox (fileName)
            
            nome = fileName
            
            'Get name of sheet
            Dim wb As Workbook
            Dim ws As Worksheet
            Dim TxtRng  As Range

            Set wb = ActiveWorkbook
            Set ws = wb.Sheets("Início")
            
            ws.Unprotect

            Set TxtRng = ws.Range("D17")
            TxtRng.Value = nome

            ws.Protect
            
            folha = Cells.Item(21, 6)
            
            'MsgBox (folha)
            
            'Copy from sheet
            
            Dim x As Workbook, y As Workbook
            Dim ws1 As Worksheet, ws2 As Worksheet
            Dim SrcRange As Range

            Application.ScreenUpdating = False
            Application.DisplayAlerts = False
 
            Set x = Workbooks.Open(j)
            Set y = ThisWorkbook
            
            Set ws1 = x.Sheets(folha)
            Set ws2 = y.Sheets("Explor. do Mês")
           
            Set CopyData = ws1.Range("A1:M8000").EntireColumn
            CopyData.Copy
            Set Addme = ws2.Range("A1:M8000")
            Addme.PasteSpecial xlPasteValues
       
            x.Close True

            Application.ScreenUpdating = True
            Application.DisplayAlerts = True
            
        Next lngCount    
    End With
    
End Sub


In the code:

VB
Set CopyData = ws1.Range("A1:M8000").EntireColumn
CopyData.Copy
Set Addme = ws2.Range("A1:M8000")
Addme.PasteSpecial xlPasteValues


I paste the entire column from column A to column M. I need to Copy and PasteSpecial only the cells from the worksheet ws1 that have values to the worksheet ws2. Then if I click again in the button and select another Workbook add the values to ws2 and not overwrite them. I want to paste the new values at the bottom of the already filled range in ws2. How can I do this in Visual Basic? What I'm missing here? Please guys, I really, really need your help! Thanks in advance.
Posted
Comments
ZurdoDev 13-Mar-15 13:50pm    
Just record a macro doing what you want and you can see the code. For example, if you want to get to the last row record a macro and press Ctrl+End to go to the bottom and you'll see what the code looks like.
Maciej Los 14-Mar-15 17:45pm    
Sorry, i do not understand your requirements: "I want to paste the new values at the bottom of the already filled range in ws2"

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900