How to Copy/Paste Visible Cells Value on one line of VBA

PartTimeExcel

New Member
Joined
Dec 9, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Good morning, This line of code works, but it obviously does not copy just the visible cells.

File-Copy-icon.png
PSU.Range("a1:a" & lastrow) = PS.Range(Cells(1, ColItemID), Cells(lastrow, ColItemID)).Value

I went to modify it (like below) so that it would copy just the visible cells value (I'd like the code to be all one line) but the syntax is off. Any ideas?

File-Copy-icon.png
PSU.Range("a1:a" & lastrow) = PS.Range(Cells(1, ColItemID), Cells(lastrow, ColItemID)).SpecialCells(xlCellTypeVisible).Value
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Have you asked this question on any other sites?

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

If you have posted the question at more places, please provide links to those as well.
 
Upvote 0
Sorry, yes I posted to one other site as well, please see the link here:


I'll be sure to update both sites if the matter is resolved!
 
Upvote 0
Thanks for that.
You cannot use the .Value=.Value approach on a range of non-contiguous cells.
 
Upvote 0
Thank you!

So when I remove the .value, the syntax error goes away, but:
1.) It copied all cells, just not the visible cells
2.) It copied the header down for each row, not the contents of each visible row.

Any ideas?
 
Upvote 0
That's why I said you cannot use that approach. ;)
You could use copy destination instead, or loop through the visible areas & copy, but without knowing what your data is like, or what you are trying to do, I cannot offer much more.
 
Upvote 0
Ah, sorry I misunderstood.

So my goal is to take the "visible cells" and copy them onto another worksheet inside the same workbook. The columns are not guarenteed to be in the same order each time, and therefore, I'm trying to name the columns. so that they can be found regardless of their column location. My attempt was:

lastrow = PS.Range("f" & Rows.Count).End(xlUp).Row

ColItemID = Application.Match("Item #", Range("1:1"), 0)
ColSku = Application.Match("Sku", Range("1:1"), 0)
ColSite = Application.Match("Site", Range("1:1"), 0)

PSU.Range("a1:a" & lastrow) = PS.Range(Cells(1, ColItemID), Cells(lastrow, ColItemID)).SpecialCells(xlCellTypeVisible)
PSU.Range("b1:b" & lastrow) = PS.Range(Cells(1, ColSku), Cells(lastrow, ColSku)).SpecialCells(xlCellTypeVisible)
PSU.Range("c1:c" & lastrow) = PS.Range(Cells(1, ColSite), Cells(lastrow, ColSite)).SpecialCells(xlCellTypeVisible)

So Im finding the column with the name "Item #" (which it does find and retrieve the column # correctly) and take all the visible rows for that column and paste them into the psu worksheet in column a. The lastrow is also working properly, except that its grabbing all the rows, not just the visible.

I do see that while it is accurately identifying the column number in the application.match portion, that when I use it within the "psu.range" line, it seems to convert "PS.Range(Cells(1, ColItemID" into the value of he header.

Thank you for your advice!
 
Upvote 0
How about
VBA Code:
PS.Range(PS.Cells(1, ColItemID), PS.Cells(lastrow, ColItemID)).SpecialCells(xlCellTypeVisible).Copy PSU.Range("a1")
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top