Problem with Combine

[olympiac]

Sheets("File A").Activate
Range("A1:F65530").Select
'***************************select all cells in this sheets
Selection.Select
'***************************select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

'***************************copy cells selected in the new sheet on last line
Selection.Copy
Sheets("combined").Range("A65536").End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
Sheets("File B").Activate
Range("A1:F65530").Select
'***************************select all cells in this sheets
Selection.Select
'***************************select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

'***************************copy cells selected in the new sheet on last line
Selection.Copy
Sheets("combined").Range("A65536").End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
Sheets("File C").Activate
Range("A1:F65530").Select
'***************************select all cells in this sheets
Selection.Select
'***************************select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

'***************************copy cells selected in the new sheet on last line

Selection.Copy
Sheets("combined").Range("A65536").End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

The combine does not work properly.
What's the meaning of the number two between bracket in End(xlUp)(2)?
I need to paste the data from the next empty cell in column A. is the code correct? I cannot read any reference to column A

 

[Richard Schollar]

The (2) effectively offsets the copy destination to be the next empty cell in column A - it is the same as using .Offset(1,0).

What does the code do if not what you want?

 

[olympiac]

I cannot understand what's wrong with the code! The data is pasted in sheet "combined" overwriting the data pasted previously.

 

[Richard Schollar]

Do you always have a value in column A for each row of data? How many sheets are you copying from? The code can be cleaned up a lot - and I can do that for you.

 

[olympiac]

I have got 4 Sheets that contain different columns but I need to combine only the following columns in a sheet called "combined":
Column A:Standard ID
Column B:First Name
Column C:Last Name
Column D:Platform
Column E:System
Column F:Date
The value in the columns are formula and for this reason I need to paste only value in the combined sheet.
Column A can then contain a value or a "0" that is the result of a formula. I am not interested to the rows where the standard ID is "0"
Hope this can help.
many thanks for your support

 

[Richard Schollar]

Hi

Try this:

Sub getData()
Dim arr
Dim wsDestination As Worksheet, wsSource As Worksheet
Dim i As Long
Dim rLast As Range
Dim rData As Range


arr = VBA.Array("File A", "File B", "File C", "File D") 'amend as appropriate

On Error Resume Next
Set wsDestination = Worksheets("Combined")
On Error GoTo 0

If wsDestination Is Nothing Then Set wsDestination = Worksheets.Add: wsDestination.Name = "Combined"

wsDestination.Range("A:F").Clear

For i = 0 To UBound(arr)
    Set wsSource = Worksheets(arr(i))
    With wsSource
        .AutoFilterMode = False
        .Range("A1").AutoFilter
        .Range("A1").AutoFilter field:=1, Criteria1:="<>0"
        If i = 0 Then
            Set rData = Intersect(.UsedRange, .Range("A:F")).SpecialCells(xlCellTypeVisible)
        Else
            Set rData = Intersect(.UsedRange, .Range("A:F")).Offset(1).SpecialCells(xlCellTypeVisible)
        End If
        Set rLast = wsDestination.Range("A:F").Find(What:="*", after:=wsDestination.Cells(1, 1), LookIn:=xlValues, searchdirection:=xlPrevious, searchorder:=xlByRows)
        rData.Copy
        If Not rLast Is Nothing Then
            Intersect(rLast.EntireRow, wsDestination.Range("A:A")).Offset(1).PasteSpecial xlPasteValues
        Else
            wsDestination.Range("A1").PasteSpecial xlPasteValues
        End If
        .AutoFilterMode = False
    End With
Next i



End Sub

 

[olympiac]

<P>Your code is short and it's working greatly. I really thank you for your support.</P>