Pasting into Visible cell range only - VBA?

**Rellsunn** · 08-09-2017, 09:39 AM

Hi!

Trying to figure out how to take consecutive data from one tab and paste it into visible cells only on another report. My Kutools trial is over and I used to be able to do so by using Paste to visible Range.

Basically I need consecutive data to paste into every 4th cell but right now it's taking the first row of data and repeating it until the end of my range in which the balance of the rows is then pasted consecutively.

I've tried copying and then choosing Find & Select - Go to Special - Visible Cells only then pasting and no luck as it does what I mentioned above.

Help! Thanks

**AliGW** · 08-09-2017, 09:43 AM

You can't, I'm afraid. Maybe pay for KuTools? It's not that expensive and it does have quite a few really useful short cuts. Your only other option is a small VBA routine.

**Rellsunn** · 08-09-2017, 09:45 AM

Thanks Ali!
That's what I was thinking but thought I'd take a chance and ask. I figured if Kutools can do it someone may know how?

**AliGW** · 08-09-2017, 09:46 AM

Someone will. I'll move this to the VBA section for you.

**Kaper** · 08-09-2017, 10:22 AM

Try such simple code:

Please Login or Register  to view this content.

**Rellsunn** · 08-09-2017, 10:33 AM

Hi Kaper,

Sorry I'm not so tech savvy. Where do I plug this code in?

**Rellsunn** · 08-09-2017, 10:47 AM

Actually I think I got it. I downloaded your file and it now shows in my Macros.
Thank you so much!

**Kaper** · 08-09-2017, 11:18 AM

Short instruction:
Open your file
Press Alt+F11 Visual Basic Editor will open
Press Alt+I, M New module will be created (shall be visible as open editor window at upper-right part
Paste code there
Close VBE (you will return to your workbook)
Save your file in macro-enabled format (xlsm)
Ready!

**Rellsunn** · 07-25-2018, 12:29 PM

Hi Kaper...wondering if you can alter the code you had created so that it works on my new report.

I tried to run it but it's only adding every 15th account name from my pivot table. I need it to add my account's consecutively in every 37th cell. Rows between each account cell is = to 36.

Sub CopyDataToVisibleCOMPS()
Dim rng As Range, InputRng As Range, OutRng As Range, i As Long
Set InputRng = Application.InputBox("Range with data", Type:=8)
Set OutRng = Application.InputBox("Output range", Type:=8).SpecialCells(xlCellTypeVisible)
If InputRng.Cells.Count > OutRng.Cells.Count Then
MsgBox "Sorry not enough cells to paste data from " & InputRng.Address
Else
For Each rng In OutRng
i = i + 1
rng.Value = InputRng.Cells(i).Value
Next rng
End If
End Sub

**Kaper** · 07-26-2018, 10:54 AM

Two comments -
1) I believe it's different problem than one originaly solved. If I'm wrong, and you just want to have headers from second sheet copied to dark merged cells in first sheet then the code needs some tuning to deal with merged cells. for instance this shall do:

Please Login or Register  to view this content.

of course you shall show only first level of conspect in sheet 1 - make only dark cells visible

2) since it's a year of inactivity you probably forgot one of our rules:

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

**Rellsunn** · 09-10-2018, 03:05 PM

Hi Kaper,

I tried to adjust thread but can't find EDIT for the life of me. Also, still not quite clear on adding code tags.

Finally, is there a way to make that code universal, in other words, I can use it on any report where I want to copy a pivot table list consecutively and past visible onto another tab.
See sample report attached. I tired using the macro on this one but as I mentioned above it keeps pasting the every 15th account from the pivot table. Someone had created
a formula but it causes the report to take super long to calculate making updating this report very time consuming.

Does the original code below show an amount of rows? Not a VBA expert so haven't been able to understand how this runs.

Please Login or Register  to view this content.

Thanks.

**Kaper** · 09-11-2018, 02:48 AM

You can't edit because the thread is a year+ old.

My suggestion is: if its used in your daily (yearly :-P) routine, why not pay modest price for KuTools?

Other possibility: start a new thread (may be with link to this one) with precise description and good, representative but small sample attached.

Pasting into Visible cell range only - VBA?

LinkBack

Thread Tools

Rate This Thread

Display

Pasting into Visible cell range only - VBA?

Re: Pasting into Visible cell range only

Re: Pasting into Visible cell range only

Re: Pasting into Visible cell range only

Re: Pasting into Visible cell range only - VBA?

Re: Pasting into Visible cell range only - VBA?

Re: Pasting into Visible cell range only - VBA?

Re: Pasting into Visible cell range only - VBA?

Re: Pasting into Visible cell range only - VBA?

Re: Pasting into Visible cell range only - VBA?

Re: Pasting into Visible cell range only - VBA?

Re: Pasting into Visible cell range only - VBA?

Thread Information

Users Browsing this Thread

Similar Threads

Pasting Into Visible Cells Only

Select visible range and add page break after every 20th visible cell

Pasting into only visible cells

[SOLVED] Copy/Pasting Values from visible cells (using autofilter) to visible cells

Pasting to Visible Cells

pasting to visible cells only

[SOLVED] Pasting in visible cells only

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions