copy and paste by VBA based on criteria

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

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

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

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

Thnx Doc Sir for ur great help and for ur great guidance

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

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