reading data from hidden sheet

[Atiq]

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!

 

[Don Guillett]

As ALWAYS, post your code for comments.

 

[Luke M]

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.

 

[Atiq]

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]

try this idea. Not tested so probably errors but the idea is to REMOVE
selections.

Public Sub GasDistGas()
with Sheets("Temp")
.Range("A1") = "Gas"
Sheets("Nlist").Range("A7").Copy .range("A2")
end with

' Determine how many Departments are on Data sheet

with Sheets("Level")
FinalRow = .Range("A10").End(xlUp).Row
' Loop through each department on the data sheet
For x = 1 To FinalRow
LastSheet = Sheets.Count

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").Range(DName).Copy Sheets(LastSheet + 1).Range("W9")
Sheets("Nlist").Range(DName1).Copy Sheets(LastSheet + 1).Range("A8")
If x = 1 Then
Sheets(LastSheet + 1).Range("A4") = 1
Else
Sheets(LastSheet + 1).Range("A4") = 2
End If

Next x

end with
End Sub