Copying Constant Data and Pasting to Only Visible Cells

[Bkisley]

I have a set of date - lets say Cells A1:A300
I need to transfer this over to a different file/template that I can not change. This template has a row then two hidden rows then a row then two hidden rows....etc.
So if I select A1:A300 and copy then paste it over to this template, it pastes information in the hidden rows.

How can I copy the entire data section and then paste in my template to only the visible (unhidden) cells??

I hope this is possible!
Thanks

 

[CyrusTheVirus]

try this https://www.youtube.com/watch?time_continue=21&v=mrDhkClPXEM

 

[Akuini]

You can try this:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] CopyVisibleToVisible1()
[I][COLOR=seagreen]'Copy paste(value):[/COLOR][/I]
[I][COLOR=seagreen]'from filtered to filtered range[/COLOR][/I]
[I][COLOR=seagreen]'from filtered to unfiltered range[/COLOR][/I]
[I][COLOR=seagreen]'from unfiltered to filtered range[/COLOR][/I]
[I][COLOR=seagreen]'Not work on hidden column[/COLOR][/I]
[I][COLOR=seagreen]'Can't copy-paste to another workbook[/COLOR][/I]

    [COLOR=Royalblue]Dim[/COLOR] rngA [COLOR=Royalblue]As[/COLOR] Range
    [COLOR=Royalblue]Dim[/COLOR] rngB [COLOR=Royalblue]As[/COLOR] Range
    [COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range
    [COLOR=Royalblue]Dim[/COLOR] Title [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]
    [COLOR=Royalblue]Dim[/COLOR] ra [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
    [COLOR=Royalblue]Dim[/COLOR] rc [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
    
    [COLOR=Royalblue]On[/COLOR] [COLOR=Royalblue]Error[/COLOR] [COLOR=Royalblue]GoTo[/COLOR] [COLOR=Royalblue]skip[/COLOR]:
    
    Title = [COLOR=brown]"Copy Visible To Visible"[/COLOR]
    [COLOR=Royalblue]Set[/COLOR] rngA = Application.Selection
    [COLOR=Royalblue]Set[/COLOR] rngA = Application.InputBox([COLOR=brown]"Select Range to Copy :"[/COLOR], Title, rngA.Address, [COLOR=Royalblue]Type[/COLOR]:=[COLOR=crimson]8[/COLOR])
    
    [COLOR=Royalblue]Set[/COLOR] rngB = Application.InputBox([COLOR=brown]"Paste Range (select the first cell only):"[/COLOR], Title, [COLOR=Royalblue]Type[/COLOR]:=[COLOR=crimson]8[/COLOR])
    [COLOR=Royalblue]Set[/COLOR] rngB = rngB.Cells([COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR])
    Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]

    ra = rngA.Rows.count
    rc = rngA.Columns.count
    [COLOR=Royalblue]Set[/COLOR] rngA = rngA.Cells([COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR]).Resize(ra, [COLOR=crimson]1[/COLOR])
    
    [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] r [COLOR=Royalblue]In[/COLOR] rngA.SpecialCells(xlCellTypeVisible)
      rngB.Resize([COLOR=crimson]1[/COLOR], rc).Value = r.Resize([COLOR=crimson]1[/COLOR], rc).Value
        [COLOR=Royalblue]Do[/COLOR]
          [COLOR=Royalblue]Set[/COLOR] rngB = rngB.Offset([COLOR=crimson]1[/COLOR], [COLOR=crimson]0[/COLOR])
        [COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]Until[/COLOR] rngB.RowHeight <> [COLOR=crimson]0[/COLOR]
    [COLOR=Royalblue]Next[/COLOR]
    
    Application.Goto rngB
    Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
    Application.CutCopyMode = [COLOR=Royalblue]False[/COLOR]
[COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[COLOR=Royalblue]skip[/COLOR]:
    [COLOR=Royalblue]If[/COLOR] err.Number <> [COLOR=crimson]424[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        MsgBox [COLOR=brown]"Error found: "[/COLOR] & err.Description
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    
    Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
    Application.CutCopyMode = [COLOR=Royalblue]False[/COLOR]


[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]