Thread: Get Info From Another Sheet
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
LizS[_5_] LizS[_5_] is offline
external usenet poster
  
Posts: 1
Default Get Info From Another Sheet

Thanks for all your help Bob. I changed my line from:

Worksheets("Sheet14").Activate

to

Worksheets(1).Activate

and it all works fine now.

I have pasted my complete macro below incase it can help anyone else.

Thanks again
Regards
Liz

Sub PrintSheet()
'
' PrintSheet Macro
' Macro recorded 16/11/2005 by abc
'
Sheets.Add

Range("A1").Select
ActiveCell.FormulaR1C1 = "Search No."
Range("A2").Select
ActiveCell.FormulaR1C1 = "AONB"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Advert Control"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Ancient Monument"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Article IV"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Breach of Condition"
Range("A7").Select
ActiveCell.FormulaR1C1 = "Conservation Area"
Range("A8").Select
ActiveCell.FormulaR1C1 = "Conservation Interest (SNCI)"
Range("A9").Select
ActiveCell.FormulaR1C1 = "Enforcements"
Range("A10").Select
ActiveCell.FormulaR1C1 = "Landscape Area (SLA)"
Range("A11").Select
ActiveCell.FormulaR1C1 = "Nature Reserves (NNR)"
Range("A12").Select
ActiveCell.FormulaR1C1 = "Rail Link within 0m"
Range("A13").Select
ActiveCell.FormulaR1C1 = "Rail Link within 200m"
Range("A14").Select
ActiveCell.FormulaR1C1 = "SSSI"
Range("A15").Select
ActiveCell.FormulaR1C1 = "Tree Preservation Order"
Range("A16").Select
ActiveCell.FormulaR1C1 = "Waste"
Range("A17").Select
ActiveCell.FormulaR1C1 = "Minerals"
Range("A18").Select
ActiveCell.FormulaR1C1 = "HSE (Hazardous)"
Range("A19").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "Address"
Range("C2").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "Parish"
Range("D2").Select
Range("E1").Select
ActiveCell.FormulaR1C1 = "Ward Boundaries"
Range("E2").Select

If SheetExists("Land Search") Then
Range("B1").Select
ActiveCell.FormulaR1C1 = "='Land Search'!R[1]C"
Range("B2").Select
Else
Range("B1").Select
ActiveCell.FormulaR1C1 = "None"
End If

sFormula = "&""""&Address!RC[5]&"" ""&Address!RC[6]&""
""&Address!RC[7]"
If SheetExists("Address") Then
Worksheets("Address").Activate
For i = 2 To Cells(Rows.Count, "G").End(xlUp).Row
Worksheets(1).Activate
If Worksheets("Address").Range("G" & i) 0 Then
Cells(i, "C").FormulaR1C1 = _
"=Address!RC[3]&""""&Address!RC[4]" & sFormula
Else
Cells(i, "C").FormulaR1C1 = _
"=Address!RC[3]" & sFormula
End If
Next i
Else
Range("C2").Value = "None"
End If


If SheetExists("Parish") Then
Range("D2").Select
ActiveCell.FormulaR1C1 = "='Parish'!RC[0]"
Range("D3").Select
Else
Range("D2").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Ward Boundaries") Then
Range("E2").Select
ActiveCell.FormulaR1C1 = "='Ward Boundaries'!RC[-2]"
Range("E3").Select
Else
Range("E2").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("AONB") Then
Range("B2").Select
ActiveCell.FormulaR1C1 = "='AONB'!RC[8]"
Range("B3").Select
Else
Range("B2").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Advert Control") Then
Range("B3").Select
ActiveCell.FormulaR1C1 = "='Advert Control'!R[-1]C[6]"
Range("B4").Select
Else
Range("B3").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Ancient Monument") Then
Range("B4").Select
ActiveCell.FormulaR1C1 = "='Ancient Monument'!R[-2]C[6]"
Range("B5").Select
Else
Range("B4").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Article IV") Then
Range("B5").Select
ActiveCell.FormulaR1C1 = "='Article IV'!R[-3]C[6]"
Range("B6").Select
Else
Range("B5").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Conservation Area") Then
Range("B7").Select
ActiveCell.Formula = "='Conservation Area'!R[-5]C[1]"
Range("B8").Select
Else
Range("B7").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Conservation Interest") Then
Range("B8").Select
ActiveCell.FormulaR1C1 = "='Conservation Interest'!R[-6]C[2]"
Range("B9").Select
Else
Range("B8").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("SLA") Then
Range("B10").Select
ActiveCell.FormulaR1C1 = "='SLA'!R[-8]C[2]"
Range("B11").Select
Else
Range("B10").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Nature Reserves") Then
Range("B11").Select
ActiveCell.FormulaR1C1 = "='Nature Reserves'!R[-9]C[12]"
Range("B12").Select
Else
Range("B11").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("SSSi") Then
Range("B14").Select
ActiveCell.FormulaR1C1 = "='SSSi'!R[-12]C[14]"
Range("B15").Select
Else
Range("B14").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("TPO") Then
Range("B15").Select
ActiveCell.FormulaR1C1 = "='TPO'!R[-13]C[8]&""
""&'TPO'!R[-13]C[11]"
Range("B16").Select
Else
Range("B15").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Waste") Then
Range("B16").Select
ActiveCell.FormulaR1C1 = "='Waste'!R[-14]C[5]"
Range("B17").Select
Else
Range("B16").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Mineral") Then
Range("B17").Select
ActiveCell.FormulaR1C1 = "='Mineral'!R[-15]C[12]"
Range("B18").Select
Else
Range("B17").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("HSE") Then
Range("B18").Select
ActiveCell.FormulaR1C1 = "='HSE'!R[-16]C[6]"
Range("B19").Select
Else
Range("B18").Select
ActiveCell.FormulaR1C1 = "None"
End If

If SheetExists("Notices") Then
Range("B6").Select
ActiveCell.FormulaR1C1 = _
"=LOOKUP(""Breach Of Condition"",Notices!C[10],Notices!C[2])"
ActiveCell.Offset(1, 0).Range("A1").Select
Range("B7").Select
Range("B6").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

Range("B6").Select
If ActiveCell.FormulaR1C1 = "#N/A" Then
ActiveCell.FormulaR1C1 = "None"
End If
Else
Range("B6").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Notices") Then
Range("B9").Select
ActiveCell.FormulaR1C1 = _
"=LOOKUP(""Enforcement Notice"",Notices!C[10],Notices!C[2])"
ActiveCell.Offset(1, 0).Range("A1").Select
Range("B10").Select
Range("B9").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

Range("B9").Select
If ActiveCell.FormulaR1C1 = "#N/A" Then
ActiveCell.FormulaR1C1 = "None"
End If
Else
Range("B9").Select
ActiveCell.FormulaR1C1 = "None"
End If


Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

End Sub
Function SheetExists(SheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
SheetExists = False
On Error GoTo NoSuchSheet
If Len(Sheets(SheetName).Name) 0 Then
SheetExists = True
Exit Function
End If
NoSuchSheet:
End Function


--
LizS
------------------------------------------------------------------------
LizS's Profile: http://www.excelforum.com/member.php...o&userid=28991
View this thread: http://www.excelforum.com/showthread...hreadid=487198

Reply With QuoteReply With Quote