Paste data on new sheet and all cells are hidden

Question

By some reason every time I run this code it hides all the data on the new sheet.
The row heights are set to 0.

I have to use the mouse to pull on the row height to make the last cell visible then from there I can click on a cell and "get up" to the data.

How can I fix this? It's very annoying.
Is it something with my code or do I need to set the row height after pasting data like this?

NumMax = NumMax + 1 'there is more code above that sets NumMax

ThisWorkbook.Activate
ThisWorkbook.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = "XCFIL_" & NumMax
ThisWorkbook.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = "Resultat_" & NumMax

Sheets("XCFIL").Activate
Cells.Copy
Sheets("XCFIL_" & NumMax).Activate
Range("A1").PasteSpecial xlPasteAll
Range("A1").Select ' a try to get up in the sheet, but it does not work

EDIT: Code panes:

Try adding `Sheets("XCFIL_" & NumMax).Cells.RowHeight = 10` to the end of your code. — nightcrawler23, Dec 14 '16 at 06:19
Is that all of the code? What happens after `Range("A1").Select`? — BruceWayne, Dec 14 '16 at 06:44
I tried this `Worksheets("XCFIL_1").Range("A1:A" & Rows.Count).Rows.AutoFit` at the same time as I posted this question, and the computer is still running the code. It's about 25% done now. — Andreas, Dec 14 '16 at 07:04
@BruceWayne The code copies sheet Resultat to Resultat_1 and updates the pivot tables. So in short, yes that is all. — Andreas, Dec 14 '16 at 07:06
either your source sheet ("XCFIL") have all rows hidden or there's some Workbook event handler hiding rows in every new sheet. — user3598756, Dec 14 '16 at 07:06
@user3598756 On XCFIL there is a table of 200.000 rows with header row freezed pane. Nothing is hidden, no filter is active. There is a filter, but nothing is filtered. — Andreas, Dec 14 '16 at 07:08
And the strange thing is that I use the same code to copy the Resultat sheet and it works great. — Andreas, Dec 14 '16 at 07:10
then check for workbook event handler in `ThisWorkbook` code pane. Finally you may want to step through your code and see what's actually happening — user3598756, Dec 14 '16 at 07:10
@user3598756 all sheet vba code is empty. The only code that is not in module is on sheet Resultat: `Private Sub CommandButton1_Click() PivotSourceChangeAll End Sub` and this runs the code above in question — Andreas, Dec 14 '16 at 07:13
@user3598756 yes. It's empty. I steped through the code and all worked great until `Range("A1").PasteSpecial xlPasteAll` that's when all rows got hidden — Andreas, Dec 14 '16 at 07:16
Quick test - just copy *one* value and paste *one* value - do the rows still hide? If you step through the code, are the rows hidden before you paste? When stepping through, *when* do the rows get hidden, at the `... xlPasteAll` command only? As an alternative, why not just copy the worksheet itself, then if you need the formatting, copy the formatting and apply it? Thus avoiding `Cells` which can take some time to copy/paste, depending on the size of your data. — BruceWayne, Dec 14 '16 at 07:21
@BruceWayne Yes the hiding happens on xlPasteAll. Nothing is odd before and as the yellow marker in debugger passes this row all the cells are hidden. How would I copy the worksheet? Formatting is not really an issue. It's just data — Andreas, Dec 14 '16 at 07:32
@Andreas - Turn on the macro recorder, then right click the sheet --> Copy --> Then you should be able to choose to "create a copy" and put it after the current sheet. Also, if you just need values, perhaps instead try `Sheets("XCFIL_" & NumMax).Cells.Value = Sheets("XCFIL").Cells.Value` instead of the paste special block (it'd be put right after the `....Name = "Resultat_" & NumMax` line, no need for anything else.) But do note, using `Cells` can take time, but basically instead of copy/paste, just set the two ranges' values equal:`Range([destination Range]).Value = Range([copy from]).Value` — BruceWayne, Dec 14 '16 at 07:34
@BruceWayne When I right click on the sheet, I do not have a option to copy — Andreas, Dec 14 '16 at 07:38
First, try the `Sheets("XCFIL_" & NumMax).Cells.Value = Sheets("XCFIL").Cells.Value` option. ...but when you right click the worksheet *tab* in Excel, you don't see "Move or Copy"? That should show up (along with like, Hide, Insert, Delete, Rename, etc.) — BruceWayne, Dec 14 '16 at 07:40
@BruceWayne The first option you gave me resulted in err 7 ' out of memory' — Andreas, Dec 14 '16 at 07:50
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/130581/discussion-between-brucewayne-and-andreas). — BruceWayne, Dec 14 '16 at 07:51

score 1 · Accepted Answer · answered Dec 14 '16 at 08:05

Per the chat, a quick solution is:

'Other option is to simply copy/paste the sheet
NumMax = NumMax + 1 'there is more code above that sets NumMax
With ThisWorkbook
.Sheets("XCFIL").Copy After:=.Sheets(.Sheets.Count)
ActiveSheet.Name = "XCFIL_" & NumMax
End With

But, I was working on the below, which may work as well.

Sub t()
Dim originWS As Worksheet, destWS As Worksheet
Dim NumMax As Long

NumMax = NumMax + 1 'there is more code above that sets NumMax

With ThisWorkbook
Set originWS = .Sheets("XCFIL")
Set destWS = .Sheets.Add(After:=.Sheets(.Sheets.Count))
destWS.Name = "XCFIL_" & NumMax
End With

Dim copyRng As Range
Dim lastCol As Long, lastRow As Long

With originWS
    lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    Set copyRng = .Range(.Cells(1, 1), .Cells(lastRow, lastCol)) 'assuming your data starts in A1
End With

destWS.Range(destWS.Cells(1, 1), destWS.Cells(lastRow, lastCol)).Value = _
    copyRng.Value
End Sub

And as a note, it's always a good idea to avoid using .Select/.Activate.

Also, this doesn't get to the very quirky issue of the rows hiding on PasteSpecial. ...but oh well, if it works, it works.

@Andreas you're welcome! Out of curiosity does the second macro work? I'm curious to see if so, and if the rows remain visible. No need to spend much more time on it, but if you have an extra few minutes I'd be interested to know. — BruceWayne, Dec 14 '16 at 08:37
@Andreas - Yay! Glad both versions work. ...I'm still just really curious why your original code hid all the rows. I know you've got a working solution, so perhaps when you're bored, do you mind trying something for me and letting me know how it goes? In the second macro I posted, change that last line (`destWS.Range(...).Value = copyRng.Value` to this: `copyRng.Copy` then next line `destWS.Range("A1").PasteSpecial xlPasteAll` and see if it hides the rows. I'm going to guess it *won't*, but who knows! — BruceWayne, Dec 15 '16 at 16:22

Paste data on new sheet and all cells are hidden

1 Answers1