Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
workbook event
have following code working with two problems. (1) copys more columns than I
want only want to copy (E) thru (L) do not want (M) (2) when trigger cell in data worksheet is changed code inserts a new line. Wud like to only have code only make change in target cell that was changed. Not start a new entry. Help is greatly appreciated Thanks Public Sub CopyDonors(ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Set wksSummary = Sheets("Donors") Set rngPaste = wksSummary.Cells(65536, "A").End(xlUp).Offset(1, 0) ' disabling events to block extra passes through ' Workbook_SheetChange caused by changing Donors cells Application.EnableEvents = False Set rngPaste = rngPaste.Offset(1, 0) rngPaste = Range(target.Offset(0, -7), target.Offset(0, 2)) Range(target.Offset(0, -7), target.Offset(0, 2)).Copy _ Destination:=rngPaste rngPaste.Offset(0, 7) = target - 10 Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
workbook event
A little confused exactly what you want. Where should the paste occure? You
are passing in a target range and you want to copy some cells based on the location of that range. Where are the coppied cells supposed to be pasted... As for part 1 of your question give this a whirl... Public Sub CopyDonors(ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Set wksSummary = Sheets("Donors") Set rngPaste = wksSummary.Cells(65536, "A").End(xlUp).Offset(1, 0) ' disabling events to block extra passes through ' Workbook_SheetChange caused by changing Donors cells Application.EnableEvents = False Set rngPaste = rngPaste.Offset(1, 0) 'Why move down again? rngPaste = Range(target.Offset(0, -7), target.Offset(0, 2)) 'What is this? Range(target.Offset(0, -7), target.Offset(0, 1)).Copy _ Destination:=rngPaste 'Change 2 to 1 rngPaste.Offset(0, 7) = target - 10 Application.EnableEvents = True End Sub -- HTH... Jim Thomlinson "Curt" wrote: have following code working with two problems. (1) copys more columns than I want only want to copy (E) thru (L) do not want (M) (2) when trigger cell in data worksheet is changed code inserts a new line. Wud like to only have code only make change in target cell that was changed. Not start a new entry. Help is greatly appreciated Thanks Public Sub CopyDonors(ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Set wksSummary = Sheets("Donors") Set rngPaste = wksSummary.Cells(65536, "A").End(xlUp).Offset(1, 0) ' disabling events to block extra passes through ' Workbook_SheetChange caused by changing Donors cells Application.EnableEvents = False Set rngPaste = rngPaste.Offset(1, 0) rngPaste = Range(target.Offset(0, -7), target.Offset(0, 2)) Range(target.Offset(0, -7), target.Offset(0, 2)).Copy _ Destination:=rngPaste rngPaste.Offset(0, 7) = target - 10 Application.EnableEvents = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
workbook event
With your help all is working fine on this part now. Trying to figure out to
have multiple conditions in row to trigger event. Hope I suceed. Still learning Thanks "Jim Thomlinson" wrote: A little confused exactly what you want. Where should the paste occure? You are passing in a target range and you want to copy some cells based on the location of that range. Where are the coppied cells supposed to be pasted... As for part 1 of your question give this a whirl... Public Sub CopyDonors(ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Set wksSummary = Sheets("Donors") Set rngPaste = wksSummary.Cells(65536, "A").End(xlUp).Offset(1, 0) ' disabling events to block extra passes through ' Workbook_SheetChange caused by changing Donors cells Application.EnableEvents = False Set rngPaste = rngPaste.Offset(1, 0) 'Why move down again? rngPaste = Range(target.Offset(0, -7), target.Offset(0, 2)) 'What is this? Range(target.Offset(0, -7), target.Offset(0, 1)).Copy _ Destination:=rngPaste 'Change 2 to 1 rngPaste.Offset(0, 7) = target - 10 Application.EnableEvents = True End Sub -- HTH... Jim Thomlinson "Curt" wrote: have following code working with two problems. (1) copys more columns than I want only want to copy (E) thru (L) do not want (M) (2) when trigger cell in data worksheet is changed code inserts a new line. Wud like to only have code only make change in target cell that was changed. Not start a new entry. Help is greatly appreciated Thanks Public Sub CopyDonors(ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Set wksSummary = Sheets("Donors") Set rngPaste = wksSummary.Cells(65536, "A").End(xlUp).Offset(1, 0) ' disabling events to block extra passes through ' Workbook_SheetChange caused by changing Donors cells Application.EnableEvents = False Set rngPaste = rngPaste.Offset(1, 0) rngPaste = Range(target.Offset(0, -7), target.Offset(0, 2)) Range(target.Offset(0, -7), target.Offset(0, 2)).Copy _ Destination:=rngPaste rngPaste.Offset(0, 7) = target - 10 Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook event | Excel Programming | |||
Workbook Event | Excel Programming | |||
Workbook open event | Excel Programming | |||
Workbook Close Event | Excel Programming | |||
Workbook Open Event | Excel Programming |