Diary System - automation

[hmltnangel]

Ok, so I hate it when i know what I want Excel to do - but I have no idea how to tell it to do it :(

I have a workbook I created that I need some help to automate a few things.

The book has a front worksheet - Diary Sheet whereby the basics of a number of customers is retained, along with the status of their case and the expected diary date to work them.

Each customer has a corresponding individual sheet hllding much more detail about them. The individual sheet is created when a new row is completed on the DIary sheet.

I need the diary dates and status to update on the diary sheet whenever they are updated on the individual sheet.

To do this I therefore need a bit of code I can add to the 'close sheet' command I have setup - code below

Sub HideSheet()
Dim sheet As Worksheet
Set sheet = ActiveSheet
sheet.Visible = xlSheetHidden
Sheets("Diary System").Select
End Sub

The extra code would need to check the sheet 'Diary System' - range B5:B100 for the corresponding customer reference number that matches the individual sheet cell C8. It then needs to copy the info from Individual sheet cell C9 and C15 and C16 and then paste it to the correct row in column D, H & G respectively

Any ideas :D

[westconn1]

you can use excel find method or vlookup worksheet function to find the customer name on diary sheet, then just update the correct cells in that row

show us your attempt, so we can help to make it work

you could probably even use a formula in the cells on diary sheet to automatically update to the last row in the customer's sheet

[hmltnangel]

I know its not right - but hopefully going in the right direction.....


Sub findValue()
Dim xlRange As Range
Dim xlCell As Range
Dim xlCopydata As Range
Dim xlSheet As Worksheet
Dim valueToFind
valueToFind = "H13"
Set xlCopydata = ("H13:N13")
Set xlSheet = ActiveWorkbook.Worksheets("Diary System")
Set xlRange = xlSheet.Range("B5:B100")
For Each xlCell In xlRange
If xlCell.Value = valueToFind Then
With Sheets("Diary System").Select
Range(xlCopydata).Copy
Sheets("Diary System").Select
Range("H12").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
Next xlCell
End Sub

[westconn1]

try like

Sub findValue()
Dim xlRange As Range
Dim xlCell As Range
Dim xlCopydata As Range
Dim xlSheet As Worksheet
Dim valueToFind
valueToFind = range("H13") ' assumes activesheet
Set xlCopydata = range("H13:N13") ' "
Set xlSheet = ActiveWorkbook.Worksheets("Diary System")
Set xlRange = xlSheet.Range("B5:B100")
For Each xlCell In xlRange
If xlCell.Value = valueToFind Then
xlCopydata.Copy
xlsheet.Range("H" & xlcell.row).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
exit for ' if you only want to find one instance
End If
Next xlCell
End Sub specifying the sheet, rather than assuming the correct sheet is the activesheet would be better

[hmltnangel]

So close but it pastes in the data in the worng columns.....

I then changed it as follows: So that it should start pasting in column B .... but then it doesnt copy paste anything. Just having one of those days.


Sub findValue()
Dim xlRange As Range
Dim xlCell As Range
Dim xlCopydata As Range
Dim xlSheet As Worksheet
Dim valueToFind
valueToFind = ActiveSheet.Range("H13") ' assumes activesheet
Set xlCopydata = Range("H13:N13") ' "
Set xlSheet = ActiveWorkbook.Worksheets("Diary System")
Set xlRange = xlSheet.Range("B5:B100")
For Each xlCell In xlRange
If xlCell.Value = valueToFind Then
xlCopydata.Copy
xlSheet.Range("B" & xlCell.Row).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Exit For ' if you only want to find one instance
End If
Next xlCell
End Sub
Sub HideSheet()
Dim sheet As Worksheet
Set sheet = ActiveSheet
sheet.Visible = xlSheetHidden
Sheets("Diary System").Select
End Sub
Sub CallMacro()
Call findValue
Call HideSheet
End Sub

[westconn1]

it looks right to me, if you can not get it to work, maybe you can post a workbook with some sample data
it was pasting before you changed the column?

try changing
xlSheet.Range("B" & xlCell.Row).PasteSpecial Paste:=xlPasteValues
to
xlcell.PasteSpecial Paste:=xlPasteValues

[hmltnangel]

11836

Doh! I know where its gone wrong - just not sure which line of code to amend....

Its where we're telling it to copy from - the code sets the active sheet as the "Diary Sheet" to copy from when it should be the individual customer sheet.

I think the file is attached now - its got some dummy details added for playing around with.

[hmltnangel]

SORTED :D


Sub findValue()
Dim xlRange As Range
Dim xlCell As Range
Dim xlCopydata As Range
Dim xlSheet As Worksheet
Dim valueToFind
valueToFind = ActiveSheet.Range("H13") ' assumes activesheet
Set xlCopydata = ActiveSheet.Range("H13:N13") ' added activesheet to this line and it works a treat
Set xlSheet = ActiveWorkbook.Worksheets("Diary System")
Set xlRange = xlSheet.Range("B5:B100")
For Each xlCell In xlRange
If xlCell.Value = valueToFind Then
xlCopydata.Copy
xlCell.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Exit For ' if you only want to find one instance
End If
Next xlCell
End Sub
Sub HideSheet()
Dim sheet As Worksheet
Set sheet = ActiveSheet
sheet.Visible = xlSheetHidden
Sheets("Diary System").Select
End Sub
Sub CallMacro()
Call findValue
Call HideSheet
End Sub