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!

.