VBA - Copy and paste filtered rows without using copy property (to optimize my code)-CodePudding

I need to reduce my code time.

I can successfully copy and paste using the below line. BS and Inputs are the sheet names.

BS.Range("A3", BS.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy
Inputs.Range("Z3").PasteSpecial xlPasteValues

I am trying to copy without using copy property, but it just copies the first cell with below code.

Inputs.Range("Z3").Value = BS.Range("A3", BS.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Value

Also, I have large numbers such as 62926080836884040921. It only pastes 62926080836880000000.

CodePudding user response：

A range is something that you can copy and paste, however, you cannot retrieve and assign the value of a range to a different range as you would with a cell.

In your code, the first option works because you copy whatever data you have on the range and then paste it, but it won't work when you try assigning the value from a range to another; a range is a group of values (the value from each cell within the range), that's why it only passes the first value.

Basically, to copy and paste, you need to use the functions as you did first; or assign the values from the cells within the range one by one.

CodePudding user response：

Note that that code works by setting the left side equivalent to the right.

Try using this format:

Sub copypaste()

'''Cell B3's value becomes A2's value
Sheets(1).Range("b3") = Sheets(1).Range("A2")
'''Cells B3-B6 become A2-A5
Sheets(1).Range("b3:B6") = Sheets(1).Range("A2:A5").Value

End Sub