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:
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:
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. How can I do this in Visual Basic? What I'm missing here? Please guys, I really, really need your help! Thanks in advance.
SOLVED!
Just changed the code above to:
With ws2
'Presuming the column "A" in ws2 will always contain the last row.
intLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
'Presuming we will ALWAYS copy the "A1:M8000" range, and that the column "A" is filled.
'Because we determine the last used row based on this column in ws2 (intLastRow)
ws1.Range("A1:M8000").Copy
.Cells(intLastRow + 1, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
And added in the variables declaration this:
Dim intLastRow As Integer
Change the copy code with this :
Dim intLastRow As Integer 'put it where you declare variables.
'Maybe use long, if data on ws2 can exceed 32K rows or something like that.
With ws2
'Presuming the column "A" in ws2 will always contain the last row.
intLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
'Presuming we will ALWAYS copy the "A1:M8000" range, and that the column "A" is filled.
'Because we determine the last used row based on this column in ws2 (intLastRow)
.Range(.Cells(intLastRow + 1, 1), .Cells(intLastRow + 1, 13)) = ws1.Range("A1:M8000").Value
End With
Ammended the code based on comment from OP. Now with the correct Range("A1:M8000") and Cells(intLastRow + 1, 13)
With ws2
'Presuming the column "A" in ws2 will always contain the last row.
intLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
'Presuming we will ALWAYS copy the "A1:M8000" range, and that the column "A" is filled.
'Because we determine the last used row based on this column in ws2 (intLastRow)
ws1.Range("A1:M8000").Copy
.Cells(intLastRow + 1, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With