How do I copy a range of formula values and paste them to a specific range in another sheet

Question

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   amp  lastrow      Range  C3 C65536   Copy Destination  Sheets  DB   Range  A   amp  lastrow      Range  D3 D65536   Copy Destination  Sheets  DB   Range  C   amp  lastrow      Range  E3 E65536   Copy Destination  Sheets  DB   Range  P   amp  lastrow      Range  F3 F65536   Copy Destination  Sheets  DB   Range  D   amp  lastrow      Range  AH3 AH65536   Copy Destination  Sheets  DB   Range  E   amp  lastrow      Range  AIH3 AI65536   Copy Destination  Sheets  DB   Range  G   amp  lastrow      Range  AJ3 AJ65536   Copy Destination  Sheets  DB   Range  F   amp  lastrow      Range  J3 J65536   Copy Destination  Sheets  DB   Range  H   amp  lastrow      Range  P3 P65550   Copy Destination  Sheets  DB   Range  I   amp  lastrow      Range  AF3 AF65536   Copy Destination  Sheets  DB   Range  J   amp  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

User · Answer

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

User · Answer

You can change  Range  B3 B65536   Copy Destination  Sheets  DB   Range  B   amp  lastrow    to  Range  B3 B65536   Copy  Sheets  DB   Range  B   amp  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   amp  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   amp  lastrowDB  PasteSpecial xlPasteValues   with  Sheets  DB   Range arr2 i   amp  lastrowDB  Resize lastrow - 2  Value            Range  Cells 3  arr1 i     Cells lastrow  arr1 i    Value

[excel] How do I copy a range of formula values and paste them to a specific range in another sheet?

Examples related to excel

Examples related to vba