Copy Value from selected cells to a new workbook sheet

**pipoliveira** · 04-10-2013, 05:55 AM

Dear all,

I hope you are all well.

I am having a issue copying contents from a selection of cells (Workbook "A") to another Workbook "B".

Both worksheets from both files are identical with the difference that the original (A) has Data Validation; Conditional Formatting and formulas, but the file "B", it's only static data from file "A".

I have managed to copy the contents of file "A" to file "B" but I can't manage to paste values only. It copies all formatting from file "A" and I just want the values.

The other thing is, after running the Macro to copy data from file "A" to "B", the file "B" looses some formulas that I have outside the range that is been paste.
E.g. I want to copy the contents of file "A" from cells F1:Z1 as paste them to the same range in file "B". If I have a formula on file "B" in cell A1, after running the macro, this cell looses the formula and shows only the value.

Here's the code I am using:

Sub Update_File()

    'Engagements Sheet (Dedicated)
    Workbooks.Open Filename:="C:\Workload Tool 2013.xlsx"
    Workbooks("Administration 2013.xlsm").Activate
    Sheets("Engagements").Select
    Range("H20:CO51").Select
    Selection.Copy
    Windows("Workload Tool 2013.xlsx").Activate
    Sheets("Engagements").Select
    Range("H20:CO51").PasteSpecial Paste:=xlPasteValues
    ActiveWindow.Close True

End Sub

Is there something wrong on the code or something missing?

As mentioned, the file "A" = "Administration 2013.xlsm"
File "B" (where data is been transported/copied) = "Workload Tool 2013.xlsx"

Your assistance on this will be highly appreciated.

Many thanks in advance and kind regards,
Filipe Oliveira

**HaHoBe** · 04-10-2013, 12:45 PM

Hi, Filipe Oliveira ,

PasteSpecial xlPasteValues turns everything in the Selection to constants.

Maybe try the following code:

Sub Update_File_2()
  Dim wbNew As Workbook
  Dim wsNew As Worksheet
  Dim wsOld As Workbook
  Dim wsOld As Worksheet
  'Engagements Sheet (Dedicated)
  Set wbold = Workbooks("Administration 2013.xlsm")
  Set wsOld = wbold.Sheets("Engagements")
  Set wbNew = Workbooks.Open(Filename:="C:\Workload Tool 2013.xlsx")
  Set wsNew = wbNew.Sheets(Engagements)
  wsNew.Range("H20:CO51").Value = wbold.Range("H20:CO51").Values
  
  wbNew.Close True
  Set wsNew = Nothing
  Set wbNew = Nothing
  Set wsOld = noithing
  Set wbold = Nothing
End Sub

Ciao,
Holger

**pipoliveira** · 04-11-2013, 05:48 AM

Hi Holger,

Thanks a million for your reply.

This formula worked as I want it to. That's brilliant and much easier and less code for what I have because I need to do the same for 3 different worksheets.

I am really appreciated with your help here

Best regards,
Filipe Oliveira.

**HaHoBe** · 04-11-2013, 11:22 AM

Hi, Filipe Oliveira,

I hope you have corrected the wrongs in the code to read it like

Sub Update_File_2_corr()
  Dim wbNew As Workbook
  Dim wsNew As Worksheet
  Dim wbOld As Workbook
  Dim wsOld As Worksheet
  'Engagements Sheet (Dedicated)
  Set wbOld = Workbooks("Administration 2013.xlsm")
  Set wsOld = wbOld.Sheets("Engagements")
  Set wbNew = Workbooks.Open(Filename:="C:\Workload Tool 2013.xlsx")
  Set wsNew = wbNew.Sheets(Engagements)
  wsNew.Range("H20:CO51").Value = wsOld.Range("H20:CO51").Values
  
  wbNew.Close True
  Set wsNew = Nothing
  Set wbNew = Nothing
  Set wsOld = noithing
  Set wbOld = Nothing
End Sub

Ciao,
Holger