Копировать диапазон и значения вставки в другом листе.

Я пытаюсь заставить макрос excel работать, но у меня проблема с копированием значений из ячеек, содержащих формулу.

До сих пор это то, что у меня есть, и отлично работает с ячейками не-формулы.

Sub Get_Data()
Dim lastrow As Long

lastrow = Sheets("DB").Range("A65536").End(xlUp).Row + 1

Range("B3:B65536").Copy Destination:=Sheets("DB").Range("B" & lastrow)
Range("C3:C65536").Copy Destination:=Sheets("DB").Range("A" & lastrow)
Range("D3:D65536").Copy Destination:=Sheets("DB").Range("C" & lastrow)
Range("E3:E65536").Copy Destination:=Sheets("DB").Range("P" & lastrow)
Range("F3:F65536").Copy Destination:=Sheets("DB").Range("D" & lastrow)
Range("AH3:AH65536").Copy Destination:=Sheets("DB").Range("E" & lastrow)
Range("AIH3:AI65536").Copy Destination:=Sheets("DB").Range("G" & lastrow)
Range("AJ3:AJ65536").Copy Destination:=Sheets("DB").Range("F" & lastrow)
Range("J3:J65536").Copy Destination:=Sheets("DB").Range("H" & lastrow)
Range("P3:P65550").Copy Destination:=Sheets("DB").Range("I" & lastrow)
Range("AF3:AF65536").Copy Destination:=Sheets("DB").Range("J" & lastrow).

End Sub

Как это сделать, чтобы оно вставляло значения?

Если это можно изменить/оптимизировать, я тоже признателен.

Ответ 1

Вы можете изменить

Range("B3:B65536").Copy Destination:=Sheets("DB").Range("B" & lastrow)

к

Range("B3:B65536").Copy 
Sheets("DB").Range("B" & lastrow).PasteSpecial xlPasteValues

Кстати, если у вас есть xls файл (excel 2003), вы получите ошибку, если ваш lastrow будет больше 3.

Попробуйте использовать этот код:

Sub Get_Data()
    Dim lastrowDB As Long, lastrow As Long
    Dim arr1, arr2, i As Integer

    With Sheets("DB")
        lastrowDB = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With

    arr1 = Array("B", "C", "D", "E", "F", "AH", "AI", "AJ", "J", "P", "AF")
    arr2 = Array("B", "A", "C", "P", "D", "E", "G", "F", "H", "I", "J")

    For i = LBound(arr1) To UBound(arr1)
        With Sheets("Sheet1")
             lastrow = Application.Max(3, .Cells(.Rows.Count, arr1(i)).End(xlUp).Row)
             .Range(.Cells(3, arr1(i)), .Cells(lastrow, arr1(i))).Copy
             Sheets("DB").Range(arr2(i) & lastrowDB).PasteSpecial xlPasteValues
        End With
    Next
    Application.CutCopyMode = False
End Sub

Примечание. выше код определяет последнюю непустую строку на DB в столбце A (переменная lastrowDB). Если вам нужно найти последнюю точку для каждого столбца назначения в листе DB, используйте следующую модификацию:

For i = LBound(arr1) To UBound(arr1)
   With Sheets("DB")
       lastrowDB = .Cells(.Rows.Count, arr2(i)).End(xlUp).Row + 1
   End With

   ' NEXT CODE

Next

Вместо этого вы можете использовать следующий подход Copy/PasteSpecial. Заменить

.Range(.Cells(3, arr1(i)), .Cells(lastrow, arr1(i))).Copy
Sheets("DB").Range(arr2(i) & lastrowDB).PasteSpecial xlPasteValues

с

Sheets("DB").Range(arr2(i) & lastrowDB).Resize(lastrow - 2).Value = _
      .Range(.Cells(3, arr1(i)), .Cells(lastrow, arr1(i))).Value

Ответ 2

Как насчет того, копируете ли вы каждый столбец листа на разные листы? Пример: строка B для mysheet для строки B листа 1, строка C в таблице для строки B листа 2...