Copy Paste based on comparisons calculations in 2 XL files, 1 might be .csv file .Opened in XL=Fail/Chaos

**DocAElstein** · 03-03-2020, 03:34 PM

Hi,

Some extra info:
For Copy and Paste values, is sometimes like this: , Rng1.Value = Rng2.Value better…

To explain:-
We already saw Sub Vixer9b() ' demo for rng.Value = rng.Value
http://www.excelfox.com/forum/showth...ll=1#post11479
http://www.excelfox.com/forum/showth...ll=1#post11485

That was Rng.Value = Rng.Value – We did use .Value of Rng in two ways

We can also do rng2.Value = rng1.Value
' Or
do rng1.Value = rng2.Value

So we have Alternative for:-
Rng.Copy
Rng.PasteSpecial Paste:= xlPasteValues

It works like this:-
We can use .Value Property two ways for any range, We can do this for Rng1 , Rng2 , Rngx …. Etc…

Way 1 Put values in ( for example, Rng2 ) :-
Rng2.Value = ‘ < -------------------------------- ‘ put values in Rng2

Way 2 Get values out ( for example for Rng1 ) :-
< -------------- = Rng1.Value ‘ Get values from Rng1

So we can take values out of a range and put them in another different range: -
Rng2.Value = Rng1.Value

Macro is in
Process.xlsm

Code:

Sub STEP4b() ' Rng1.Value = Rng2.Value
 Dim w1 As Workbook, w2 As Workbook
 Set w1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls")                ' w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")           ' change the file path
 Set w2 = Workbooks.Open(ThisWorkbook.Path & "\FundsCheck.xlsb") ' w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb") ' change the file path
Dim Rng1 As Range, Rng2 As Range
' If first column and first row is used , then this will work only
 Set Rng1 = w1.Worksheets.Item(1).UsedRange '  or Set Rng1 = w2.Worksheets.Item(1).Range("A1:H" & Rng1.Rows.Count & "")
 Set Rng2 = w2.Worksheets.Item(1).Range("A1:H" & Rng1.Rows.Count & "")

'w1.Worksheets.Item(1).Columns("A:H").Copy
'w2.Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
 Let Rng2.Value = Rng1.Value

w2.Save
w2.Close
w1.Close
End Sub