Discussion Locked

Copy Data From A Hidden Worksheet

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I wrote a macro that copy's a specific range from a worksheet and pastes it into another worksheet.
The macro works fine when the worksheet I am copying from is visible. However, when I hide this worksheet, I get an error message when running thismacro, (can't locate the worksheet ).

Is there a way to copy data from a hidden worksheet with out writing more code to unhide, copy this range and then hide this worksheet?

7 replies

by glen_mcleod

I don't know it it's possible, but if it isn't, here'a a way around the user seeing the sheet pop up and disapear:

Application.ScreenUpdating = False
'unhide the sheet
'get the data
'hide the sheet
Application.ScreenUpdating = True

This will freeze the screen so nothing seems to be happening, then unfreezes it when you're done. You might want to change the mouspointer to an hourglass just before you freeze to let the user know it's still working.

Glen

by glen_mcleod

It will also speed up any processing that has to be done, for just about any procedure that operates on the sheets.

by sankulpune

Hello,
Assume that you have 2 sheets Sheet1(hidden) & Sheet2 in your workbook. Sheet1 contains :
A1 : 10
A2 : 20
A3 : =A1+A2 (displays 30)
Also assume that cell A3 is formatted as bold and red.
Case A) All sheets have similar formatting for similar cells, say A3 cell in every sheet is Bold and Red.
Unhide Sheet1.
Select all sheets simultaneously.
Appply cell formating to cells in any sheet. This will apply the formatting to cells in all sheets having same adress.
Hide Sheet1.
Topaste cell A3 from Sheet1 to cell A3 of Sheet A2, type the formula
=Sheet1!A3
in cell A3 of Sheet2.
Case B) You want to paste only value and not formatting in a cell in hidden sheet, to any cell in visible sheet.
Assume that you want to copy value in A3 of Sheet1 in A4 of Sheet2.
Type the formula
= Sheet1!A3
in cell A4 of Sheet2.
You can type any complex formula involving cells in hidden sheet (=Sheet1!A1+Sheet1!A2) in any cell of visible sheet.
But formatting will not be copied.
Case C) You want to paste both formatting and value of a cell in hidden sheet to a cell in visible sheet:
Assume that you want to paste cell A3 in Sheet1 to cell B10 in Sheet2.
Use the following macro:
Sub CopyHidden()
Sheets("Sheet1").Range("A3").Copy
With Sheets("Sheet2").Range("B10")
.FormulaR1C1 = "=Sheet1!R3C1"
.PasteSpecial Paste:=xlFormats
End With
End Sub
PS: After you run this macro once, any subsequent changes in value (but not formatting) of A3 in Sheet1 will automatically be updated in B10 of Sheet2. To update changes in formatting, run macro again.
Your feedback will be highly appreciated.

Please let know if you need any clarifications before you rate the answer,

With regards,

SanganakSakha.
Simple & Effective Solutions To Small Problems, No QuickFixes.

by sankulpune

Hello,

This macro works only for the pair of cells A3 in Sheet1 and B10 in
Sheet2. It is possible to make it more generalised so that you can
interactively paste any range from Sheet1 anywhere in Sheet2.

Please let know if you need any clarifications before you rate the answer,

With regards,

SanganakSakha.
Simple & Effective Solutions To Small Problems, No QuickFixes.

This discussion is closed to new replies.