ExcelBanter - reading data from hidden sheet

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - reading data from hidden sheet (https://www.excelbanter.com/excel-discussion-misc-queries/254573-reading-data-hidden-sheet.html)

reading data from hidden sheet

I have a macro to copy and past data from one sheet to another. It works fine
when I have all the sheets open, but when I hide the source sheet (where my
raw data is stored) macro gives me an error (400). I want my raw data sheet
hidden, what can I do to keep my macro working when raw data sheet is hidden?
Please advise....

Thanks!

reading data from hidden sheet

Instead of doing something like this:
Sheets("Hidden").Range("A1").Copy _
Destination:=Sheets("Destination").Range("A1")

Do this:
Sheets("Destination").Range("A1") = _
Sheets("Hidden").Range("A1").Value

This way your referencing the cell directly, and don't have to worry about
copying & pasting.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

"Atiq" wrote:

I have a macro to copy and past data from one sheet to another. It works fine
when I have all the sheets open, but when I hide the source sheet (where my
raw data is stored) macro gives me an error (400). I want my raw data sheet
hidden, what can I do to keep my macro working when raw data sheet is hidden?
Please advise....

Thanks!

reading data from hidden sheet

Public Sub GasDistGas()
Sheets("Temp").Select
Range("A1") = "Gas"

Sheets("Nlist").Select
Range("A7").Select
Selection.Copy
Sheets("Temp").Select
Range("A2").Select
ActiveSheet.Paste

' Determine how many Departments are on Data sheet

Sheets("Level").Select
FinalRow = Range("A10").End(xlUp).Row
' Loop through each department on the data sheet
For x = 1 To FinalRow
LastSheet = Sheets.Count
Sheets("Level").Select
ThisDept = Range("A" & x).Value
DName = ("GD_" & x)
DName1 = ("GD2_" & x)
' Make a copy of template sheet and move to end
Sheets("Temp").Copy After:=Sheets(LastSheet)
' rename the sheet and set A1 = to the department name
Sheets(LastSheet + 1).Name = ThisDept
Sheets("Nlist").Activate
Sheets("Nlist").Range(DName).Select
Selection.Copy
Sheets(LastSheet + 1).Select
Range("W9").Select
ActiveSheet.Paste
Sheets("Nlist").Activate
Sheets("Nlist").Range(DName1).Select
Selection.Copy
Sheets(LastSheet + 1).Select
Range("A8").Select
ActiveSheet.Paste
If x = 1 Then
Sheets(LastSheet + 1).Select
Range("A4") = 1
Else
Sheets(LastSheet + 1).Select
Range("A4") = 2
End If

Next x
End Sub

"Don Guillett" wrote:

As ALWAYS, post your code for comments.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Atiq" wrote in message
...
I have a macro to copy and past data from one sheet to another. It works
fine
when I have all the sheets open, but when I hide the source sheet (where
my
raw data is stored) macro gives me an error (400). I want my raw data
sheet
hidden, what can I do to keep my macro working when raw data sheet is
hidden?
Please advise....

Thanks!

.

reading data from hidden sheet