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

**fixer** · 03-02-2020, 10:25 PM

Moderator notice.
This is part of the start of duplicate cross posting chaos.
When the OP, Avinash, gets caught, he post one of his canned replies , like Sorry Sir, it won’t happen again.
Eventually he starts again a duplicate cross posting chaos.
When he gets caught , he post one of his canned replies , like Sorry Sir, it won’t happen again.
Eventually he starts again a duplicate cross posting chaos.
When he gets caught , he post one of his canned replies , like Sorry Sir, it won’t happen again.
Eventually he starts again a duplicate cross posting chaos.
When he gets caught , he post one of his canned replies , like Sorry Sir, it won’t happen again.
Eventually he starts again a duplicate cross posting chaos.
When he gets caught , he post one of his canned replies , like Sorry Sir, it won’t happen again.
_….. and so on
I am not sure if he does it on purpose or is insane or he just a total dim pig shit for brains. I expect a bit of all of those….

2 months latter he is still wasting his and lots of peoples times going around in circles making a total mess in posts everywhere because he refuses to understand anything at all about Text files.
The biggest problem is in using an Excel object to open a .csv File, which is usually not a good idea.
Sometimes you might get the impression he is understanding at least a small part of the problem, but wither it is just co incidence that what he has pasted infers that and he has no idea what he is writing, or two seconds later he forgets and we are back to the starting point and he starts again duplicating cross posting the same question… - I think there is a good chance the OP is insane, and certainly a total dim pig shit for brains. Physical violence is I think the only hope for him, and if all else fails then he should be put to death.,

all files are located same place

vba is placed in a seperate file sample1.xlsm

copy all the data from column A TO COLUMN H complete data from sample2.xls and paste it to sample3.xlsb in same columns

save and close the sample2.xls and sample3.xlsb

i need to do the same by vba so plz help me in slving this problem sir

**DocAElstein** · 03-03-2020, 02:07 AM

Hi,

To copy all columns is not usually good to do. In whole columns you maybe do not heed all rows

But this is what you asked for

vba is placed in a separate file sample1.xlsm
File is attached : FileAttatchedToPost.jpg : https://imgur.com/xFLyqLQ

copy all the data from column A TO COLUMN H complete data from sample2.xls
Columns("A:H").Copy

paste it to sample3.xlsb in same columns
I recommend using the Range PasteSpecial Method
http://eileenslounge.com/viewtopic.p...=34112#p264458
http://www.eileenslounge.com/viewtop...=25002#p193871
https://docs.microsoft.com/en-us/off...e.pastespecial

You can chose the way you want to Paste, and formats
https://docs.microsoft.com/en-us/off...el.xlpastetype
.PasteSpecial Paste:= xlPasteValuesAndNumberFormats
.PasteSpecial Paste:= xlPasteFormats
.PasteSpecial Paste:= xlPasteColumnWidths

save and close the sample2.xls and sample3.xlsb
You can
Save
Or
Save As

I did do macro recording to check syntax:
http://www.excelfox.com/forum/showth...ll=1#post12531

You can just Save That is enough in your case.
( You can also SaveAs )

Code:

Sub CopyAllColumns()
' copy all the data from column A TO COLUMN H complete data from sample2.xls
 Workbooks("sample2.xls").Worksheets.Item(1).Columns("A:H").Copy '
' paste it to sample3.xlsb in same columns
 Workbooks("sample3.xlsb").Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                                                                                             'Workbooks("sample3.xlsb").Worksheets.Item(1).Range("A1:H65536").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
 Workbooks("sample3.xlsb").Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteFormats
 Workbooks("sample3.xlsb").Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteColumnWidths
 
' save and close the sample2.xls and sample3.xlsb   '   http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=12531&viewfull=1#post12531
 Workbooks("sample2.xls").Save
' or
 Workbooks("sample2.xls").SaveAs Filename:=ThisWorkbook.Path & "\sample2.xls", FileFormat:=xlExcel8

 Workbooks("sample3.xlsb").Save
' or
 Workbooks("sample3.xlsb").SaveAs Filename:=ThisWorkbook.Path & "\sample3.xlsb", FileFormat:=xlExcel12

 Workbooks("sample2.xls").Close: Workbooks("sample3.xlsb").Close
End Sub

Alan

**fixer** · 03-03-2020, 06:58 AM

Code:

Sub STEP4()
 Dim w1 As Workbook, w2 As Workbook
 Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls") ' change the file path
 Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb") ' change the file path
w1.Worksheets.Item(1).Columns("A:H").Copy
w2.Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
w2.Save
w2.Close
w1.Close
End Sub

**DocAElstein** · 03-03-2020, 01:04 PM

Your macro is good
Your macro is working
There are no errors in it.
I did test on files enclosed , with this following macro. ( Macro is in sample1.xlsm )

Code:

Sub STEP4()
 Dim w1 As Workbook, w2 As Workbook
Set w1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls") ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls") ' change the file path
Set w2 = Workbooks.Open(ThisWorkbook.Path & "\FundsCheck.xlsb") '  Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb") ' change the file path
w1.Worksheets.Item(1).Columns("A:H").Copy
w2.Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
w2.Save
w2.Close
w1.Close
End Sub

Alan

**fixer** · 03-03-2020, 01:13 PM

**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

Alan

1.xls : https://app.box.com/s/th2xzmkh7rnfr4qf4dho1kpgudndm073

**fixer** · 03-03-2020, 03:44 PM

Thnx Doc Sir for providing the Great info

**fixer** · 03-17-2020, 05:29 PM

Doc Sir i have a similar problem like this

Code:

Sub STEP4()
 Dim w1 As Workbook, w2 As Workbook
 Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls") ' change the file path
 Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb") ' change the file path
w1.Worksheets.Item(1).Columns("A:H").Copy
w2.Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
w2.Save
w2.Close
w1.Close
End Sub

this code paste the data to sheet1 what i need is to paste the data in sheet2 so what i should do for the same sir plz help me sir in solving this problem sir

**DocAElstein** · 03-17-2020, 06:51 PM

Hello

Worksheets.Item(1) is first worksheet
Worksheets.Item(2) is second worksheet
Worksheets.Item(3) is Third worksheet
Workshe…….etc.

First4Worksheets.JPG : https://imgur.com/v0h9CaU
First4Worksheets.JPG

Try
w2.Worksheets.Item(2).Columns("A:H").Past……………

**fixer** · 03-17-2020, 07:31 PM

Thnx Alot Doc Sir i changed the same in the code but i forgot to create a second sheet
Done Doc Sir thnx alot Sir

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

Thread Tools

Search Thread

Display

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

Similar Threads

VBA Dir File Copy and Paste: Check if file exists then copy file from one directory to another. Bat File

Copy paste data based on criteria

VBA code to copy and paste row based on certain criteria

Copy data from 1 excel workbook to another based on certain criteria

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions