15

I'm trying to get an excel macro to work but I'm having an issue with copying the values from formula-containing cells.

So far this is what I have and it works fine with the non-formula cells.

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

How can I make it so it pastes the values for formulas?

If this can be changed/optimized, I'd appreciate it too.

TylerH
  • 20,799
  • 66
  • 75
  • 101
BlueSun3k1
  • 757
  • 5
  • 21
  • 39

2 Answers2

26

You can change

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

to

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

BTW, if you have xls file (excel 2003), you would get an error if your lastrow would be greater 3.

Try to use this code instead:

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

Note, above code determines last non empty row on DB sheet in column A (variable lastrowDB). If you need to find lastrow for each destination column in DB sheet, use next modification:

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

You could also use next approach instead Copy/PasteSpecial. Replace

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

with

Sheets("DB").Range(arr2(i) & lastrowDB).Resize(lastrow - 2).Value = _
      .Range(.Cells(3, arr1(i)), .Cells(lastrow, arr1(i))).Value
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • Thank you for you post. I tried the first code with the arrays and I got an error "Script out of range" and after changing With Sheets("Sheet1") to "DB", it just doesn't do anything. just as FYI, I'm using Excel 2010. I haven't tried the other options yet. Any suggestions? – BlueSun3k1 Feb 08 '14 at 18:53
  • I also corrected this part. >> arr1 = Array("B", "C", "D", "E", "F", "AH", "AIH", "AJ", "J", "P", "AF") << from AIH to AI and with this fixed, still got the same error. – BlueSun3k1 Feb 08 '14 at 19:06
  • 1
    you should change `Sheet1` in line `With Sheets("Sheet1")` to the sheet name _from wich_ you are coping data. E.g. if you copy data from sheet `mySheet` to sheet `DB`, you should change `Sheet1` (in line `With Sheets("Sheet1")`) to `mySheet`. If you want to copy from _active sheet_, change `With Sheets("Sheet1")` to `With ActiveSheet` (it will copy data from currently active sheet to `DB` sheet) – Dmitry Pavliv Feb 08 '14 at 19:23
  • You were faster than me. I was just about to comment that. I did that while testing and it worked beautifully. Thank you so much for your help. I'll continue to test and if I have any more questions, I'll let you know. – BlueSun3k1 Feb 08 '14 at 19:27
-1

How about if you're copying each column in a sheet to different sheets? Example: row B of mysheet to row B of sheet1, row C of mysheet to row B of sheet 2...

Gem
  • 1
  • 3