Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add record to end of list, variables table
I currently have (script below) macro which adds a record (from "CashEntry"
sheet, cells $C$2:$P$3) to the bottom of a list (on another sheet ""CashTransferRecord") . My Request: I would now like the ability to add the record to one of four different sheets (instead of solely the "CashTransferRecord") based on a Variables Table (see below). So, the macro will identify the Name located in 'CashEntry" cell G2, then add the record to bottom of list onto the appropriate Sheet Name. Variables Table: Names Sheet Name Robert RobertCash Dilbert DilbertCash Q-Bert Q-BertCash Eggbert EggbertCash __________________________________ Sub CopyPasteOntoDatabase() Sheets("CashTransferRecord").Select If Cells(2, 3).Value = Empty Then Worksheets("CashEntry").Range("$C$2:$P$3").Copy Worksheets("CashTransferRecord").Range("C2").Paste Special (xlPasteValues) Else Worksheets("CashEntry").Range("$C$2:$P$3").Copy Worksheets("CashTransferRecord").Range("C65000").E nd(xlUp).Offset(1, 0).Cells.PasteSpecial (xlPasteValues) End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add record to end of list, variables table
Dim sh As Worksheet
Set sh = Worksheets("CashEntry") With Worksheets(sh.Range("G2").Value) If .Cells(2, 3).Value = Empty Then sh.Range("C2:P3").Copy .Range("C2").PasteSpecial (xlPasteValues) Else sh.Range("C2:P3").Copy .Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues End If End With -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Brice" wrote in message ... I currently have (script below) macro which adds a record (from "CashEntry" sheet, cells $C$2:$P$3) to the bottom of a list (on another sheet ""CashTransferRecord") . My Request: I would now like the ability to add the record to one of four different sheets (instead of solely the "CashTransferRecord") based on a Variables Table (see below). So, the macro will identify the Name located in 'CashEntry" cell G2, then add the record to bottom of list onto the appropriate Sheet Name. Variables Table: Names Sheet Name Robert RobertCash Dilbert DilbertCash Q-Bert Q-BertCash Eggbert EggbertCash __________________________________ Sub CopyPasteOntoDatabase() Sheets("CashTransferRecord").Select If Cells(2, 3).Value = Empty Then Worksheets("CashEntry").Range("$C$2:$P$3").Copy Worksheets("CashTransferRecord").Range("C2").Paste Special (xlPasteValues) Else Worksheets("CashEntry").Range("$C$2:$P$3").Copy Worksheets("CashTransferRecord").Range("C65000").E nd(xlUp).Offset(1, 0).Cells.PasteSpecial (xlPasteValues) End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add record to end of list, variables table
Hello, please let me know if my question makes sense. Not sure if I explained
my problem very well. Thanks for your help! - Brice "Brice" wrote: Thanks Bob. How do I incorporate a Variables Table (see below) so that records are added based on it? Names: Sheet Name: Robert a/c 1 Robert Cash Dilbert a/c 3 Dilbert Cash Eggbert a/c 2 Other Cash Sherbert a/c 1 Other Cash Also, instead of cell G2 as reference, I would like to identify the concatenation of cells F2 and G2 together, then vlookup for this output under "Names:" on the VariablesTable and add record to the corresponding "Sheet Name:".........is this possible? Thanks for all your help! "Bob Phillips" wrote: Dim sh As Worksheet Set sh = Worksheets("CashEntry") With Worksheets(sh.Range("G2").Value) If .Cells(2, 3).Value = Empty Then sh.Range("C2:P3").Copy .Range("C2").PasteSpecial (xlPasteValues) Else sh.Range("C2:P3").Copy .Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues End If End With -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Brice" wrote in message ... I currently have (script below) macro which adds a record (from "CashEntry" sheet, cells $C$2:$P$3) to the bottom of a list (on another sheet ""CashTransferRecord") . My Request: I would now like the ability to add the record to one of four different sheets (instead of solely the "CashTransferRecord") based on a Variables Table (see below). So, the macro will identify the Name located in 'CashEntry" cell G2, then add the record to bottom of list onto the appropriate Sheet Name. Variables Table: Names Sheet Name Robert RobertCash Dilbert DilbertCash Q-Bert Q-BertCash Eggbert EggbertCash __________________________________ Sub CopyPasteOntoDatabase() Sheets("CashTransferRecord").Select If Cells(2, 3).Value = Empty Then Worksheets("CashEntry").Range("$C$2:$P$3").Copy Worksheets("CashTransferRecord").Range("C2").Paste Special (xlPasteValues) Else Worksheets("CashEntry").Range("$C$2:$P$3").Copy Worksheets("CashTransferRecord").Range("C65000").E nd(xlUp).Offset(1, 0).Cells.PasteSpecial (xlPasteValues) End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I add the new record from another list to current one? | Excel Worksheet Functions | |||
how to add a record to a pivot table | Excel Worksheet Functions | |||
Record Pivot table Macro | Excel Programming | |||
Fetch list of table names in access to a record set | Excel Programming | |||
list box record select | Excel Programming |