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!

As ALWAYS, post your code for comments.

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Atiq" <Atiq@discussions.microsoft.com> wrote in message 
news:FCC02AA8-B4D9-4202-AF00-6384AB099FC9@microsoft.com...
>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! 

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!

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
> dguillett@gmail.com
> "Atiq" <Atiq@discussions.microsoft.com> wrote in message 
> news:FCC02AA8-B4D9-4202-AF00-6384AB099FC9@microsoft.com...
> >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! 
> 
> .
> 

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
-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Atiq" <Atiq@discussions.microsoft.com> wrote in message 
news:76E3C543-2A85-4EE0-BAD3-1084A462CB28@microsoft.com...
> 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
>> dguillett@gmail.com
>> "Atiq" <Atiq@discussions.microsoft.com> wrote in message
>> news:FCC02AA8-B4D9-4202-AF00-6384AB099FC9@microsoft.com...
>> >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!
>>
>> .
>>