VBA Error 1004 When Pasting to Visible Cells Only

[Ichijiku]

Hi all,

I am using Excel 2013 to build a production schedule, with the main schedule located on a worksheet called "Schedule" and a production template for each job on a worksheet called "Template." The job information is in the first few columns, then each column represents a week. When a week has passed, I hide the column - I don't want to delete it as I may need to refer back to it. However, this has caused problems with parts of the template pasting into hidden cells when I try to add new jobs. I've been working on solving the problem with code that I found in this thread: http://www.mrexcel.com/forum/excel-...applications-paste-special-visible-cells.html

Sub PASTEtoVISIBLE_Click()


Worksheets("TEMPLATE").Range("A3:BA21").Copy
Worksheets("SCHEDULE").Range("a313:ba331").SpecialCells(xlCellTypeVisible).PasteSpecial


End Sub

Unfortunately, I'm getting a 1004 error (can't paste because copy area and paste area aren't the same size) when I try to run it, even if I adjust the end column out to compensate for the hidden columns.

(FYI, I've tried doing a standard copy/paste from the template in two chunks on either side of the hidden cells, but parts of the production data link to the job information, and all the links break if I do it this way.)

Could someone please help me figure out how to fix this?

Thank you!

 

[Rijnsent]

Hi Ichijiku,
I've recorded some code and modified it, you don't need to reference to the whole range in the second bit of code. This works for me (have hidden some columns in Sheet2):
G'luck,
Koen

    Worksheets("Sheet1").Range("A2:I8").Copy
    Worksheets("Sheet2").Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    'Or:
    Worksheets("Sheet1").Range("A2:I8").Copy Destination:=Worksheets("Sheet2").Range("A10")

 

[Ichijiku]

Hello Koen,

Thank you for sending me the above code to try out; unfortunately when I tried it on my sheet, it still pastes into the hidden cells. I also tried integrating your tip about not referencing the whole range into my original code, but still got a 1004 error.

Any thoughts?

 

[Rijnsent]

Hi Ichijiku,
ah, you don't want to paste in the hidden cells, that wasn't clear to me... If the hidden columns are always the same (say alway column Y:AF), I would split the copy-paste action into blocks, so first copy-pasting A:X and then AG:BA. If that is not the case, you could write a macro like this, checking and copy-pasting every column.
Cheers,
Koen

Sub CopyPaste()

Set SrcRng = Worksheets("Sheet1").Range("A1:K12")
Set DestRng = Worksheets("Sheet2").Range("A51:K62")

For col = 1 To DestRng.Columns.Count
    'Loop through the columns of the DestinationRange and see if they are hidden, if so, don't copy from SourceRange, otherwise, do copy
    If DestRng.Cells(1, col).EntireColumn.Hidden = True Then
        'Hidden column, skip
    Else
        SrcRng.Resize(SrcRng.Rows.Count, 1).Offset(0, col - 1).Copy Destination:=DestRng.Cells(1, col)
    End If
Next col

End Sub