-
copy and paste by VBA based on criteria
-
1 Attachment(s)
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
-
-
2 Attachment(s)
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
-
Thnx Doc Sir for ur great help and for ur great guidance
-
2 Attachment(s)
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
-
Thnx Doc Sir for providing the Great info
-
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
-
1 Attachment(s)
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……………
-
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
