PDA

View Full Version : macro assistance -



Pete
12-07-2009, 08:33 AM
hi

Need a macro to copy the following columns starting at row 5 columns A:C

1. copy S/O Item from A5 until last cell with data from worksheet "PivotTable_KE24 to column D row 3 worksheet "Reconciliation YTD".

2. copy Sum of Cost from B5 until last cell with data from worksheet ""PivotTable_KE24 to column H row 3 worksheet "Reconciliation YTD".

2. copy Sum of Cost from C5 until last cell with data from worksheet ""PivotTable_KE24 to column I row 3 worksheet "Reconciliation YTD".

Bob Phillips
12-07-2009, 08:39 AM
Off the top



With Worksheets("PivotTable_KE24")

.Range(.Range("A5"), .Range("A5").End(xlDown)).Copy Worksheets("Reconciliation YTD").Range("D3")

.Range(.Range("B5"), .Range("B5").End(xlDown)).Copy Worksheets("Reconciliation YTD").Range("H3")

.Range(.Range("C5"), .Range("C5").End(xlDown)).Copy Worksheets("Reconciliation YTD").Range("I3")
End With

Pete
12-07-2009, 08:55 AM
lets test it

Pete
12-07-2009, 08:56 AM
prefect thanks for the feedback xld

Pete
12-07-2009, 09:13 AM
Hi

Is it possible to do the following.

1. I have data in worksheet "Rec YTD" - column D4 starting point until last cell in column D and i have the exact same data in worksheet "Accrual cal" in column A6....

each time i update column D4 worksheet "Rec YTD" the corressponding data in worksheet worksheet "Accrual cal" updates too.....

and how

lucas
12-07-2009, 09:36 AM
This seems fairly straightforward using forumla's.

Have you tried anything?

Pete
12-07-2009, 09:39 AM
i have a macro that xld provided and does a lot of the work.... i am looking at additonal information that u might add to the buttom of a list already populated......in column D4 worksheet "Rec YTD"......so both columns in the two sheet stay as an exact copy of each other....

GTO
12-07-2009, 09:47 AM
Hi Pete,

Maybe I am missing something, but if you literally had data from D4 to the last cell in the column, you could not have the exact same data from A6 downwards elsewheres, as you'd run two rows short.

Anyways, I doubt you are looking for:


'Entered in A6 and drug down...
='Rec YTD'!D4

Are you wanting it to update via code?

Mark

Edit: Oops! Sorry Steve, slow-hand here...

Bob Phillips
12-07-2009, 09:49 AM
Another question, why do you need it in 2 places Pete?

Bob's Golden Rule of Data #1 - only store it once.

Pete
12-07-2009, 10:47 AM
two different worksheet and two totally diff calculations,,,,

i have managed to find an old macro that might work......
Private Sub Worksheet_Change(ByVal Target As Range)If Target.Column <> 4 Then Exit SubIf Target.Cells.Count > 1 Then Exit SubSheets("Accrual cal").Range("a" & Rows.Count).End(xlUp).Offset(1) = Target.ValueEnd Sub

Pete
12-07-2009, 10:50 AM
and i do not want to overwrite any current data in column d4 worksheet "Rec YTD"...only add new data....to the bottom of the old list.

lucas
12-07-2009, 10:51 AM
Pete, do you know how to format your code for the forum?

when posting, select the code and hit the VBA button.

Pete
12-07-2009, 10:52 AM
ok sorry........

Pete
12-08-2009, 04:19 AM
hi

Need to finish this macro so that it populates the results in columns M:N based on column A data last cell




VBA:


Sub Vlookup_KE_24() Application.ScreenUpdating = False ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-12],'KE24'!C1:C13,7,0)" ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-13],'KE24'!C1:C13,13,0)" Application.ScreenUpdating = True End Sub

Bob Phillips
12-08-2009, 04:30 AM
See other thread.

Your VBA tags have lapsed.

lucas
12-08-2009, 10:26 AM
Threads Merged

Pete, you don't seem to want to work with us here at VBA Express.

Keep your ongoing questions in one thread to keep down confusion about what you are talking about and USE THE VBA TAGS PLEASE.

You have been asked repeatedly and I tire of cleaning up after you and begging you to do what every other poster seems to be willing to do.