Generate ongoing database of results via Macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brice - 19 Dec 2007 01:14 GMT

Hello,

I will be very grateful for your help on this problem! I am very new to
macros.

I have data on "Sheet1" in cells A2:P3. These cells reference other cells on
different tabs and the data changes day-to-day. So, I need a macro which
copies & pastes (values only) the data in cells A2:P3 onto a new sheet
("CashTransferRecord").

Here is the hard part ...

Each time I run the macro I would like the data to be pasted to the first
two blank rows below the existing rows which already have data in them.

Note: the first time the macro runs i would like the two rows of data it
pastes onto "CashTransferRecord" to be pasted in rows 2 and 3.

Can this be done?!

Many Thank

Reply to this Message

JLGWhiz - 19 Dec 2007 02:00 GMT

Sub cpy2rws()
lr = Worksheets("CashTransferRecord").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet1").Range("$A$2:$P$3").Copy _
Worksheets("CashTransferRecord").Range("A" & lr + 1)
End Sub

> Hello,
>
[quoted text clipped - 17 lines]
>
> Many Thank

Reply to this Message

JLGWhiz - 19 Dec 2007 02:05 GMT

Disregard the first one. It will copy everything over. This one only does
values.

Sub cpy2rws()
lr = Worksheets("CashTransferRecord").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet1").Range("$A$2:$P$3").Copy
Worksheets("CashTransferRecord").Range("A" & lr + 1).PasteSpecial _
Paste:=xlValues
End Sub

> Hello,
>
[quoted text clipped - 17 lines]
>
> Many Thank

Reply to this Message

Brice - 19 Dec 2007 02:45 GMT

Thanks for your help.

When I run the macro for the second time, the data is overwriting the
existing data in rows two and three. I would likle to have the macro ADD the
data/record to the next available rows so that I can keep an ongoign database.

Can you help?

> Disregard the first one. It will copy everything over. This one only does
> values.
[quoted text clipped - 27 lines]
> >
> > Many Thank

Reply to this Message

Brice - 19 Dec 2007 06:26 GMT

Can someone help me with this MACRO problem?

> Thanks for your help.
>
[quoted text clipped - 35 lines]
> > >
> > > Many Thank

Reply to this Message

Jean-Yves - 19 Dec 2007 15:12 GMT

Hi Brice

Worksheets("Sheet1").Range("$A$2:$P$3").Copy
Worksheets("CashTransferRecord").Range("A65000").End(xlUp).Offset(2,
0).Cells.PasteSpecial (xlPasteValues)
Regards
JY

> Can someone help me with this MACRO problem?
>
[quoted text clipped - 48 lines]
>> > >
>> > > Many Thank

Reply to this Message

Mike H. - 19 Dec 2007 22:32 GMT

Actually you'd have to do this because of your requirement to have the data
start on row 2 if you're just starting out. And the offset # was wrong, sb 1
not 2. HTH

Sub Doit()

Sheets("CashTransferRecord").Select
If Cells(2, 1).Value = Empty Then
Worksheets("Sheet1").Range("$A$2:$P$3").Copy
Worksheets("CashTransferRecord").Range("a2").PasteSpecial (xlPasteValues)
Else
Worksheets("Sheet1").Range("$A$2:$P$3").Copy
Worksheets("CashTransferRecord").Range("A65000").End(xlUp).Offset(1,
0).Cells.PasteSpecial (xlPasteValues)
End If
End Sub

Reply to this Message

Brice - 20 Dec 2007 05:26 GMT

thanks everyone...it almost works now!

one hiccup...

I keep the cells in columns A & B on "CashTransferRecord usually blank so
the macro doesn't add a new record each time. can this be fixed so it looks
to column C to check if cells are empty or not in order to add record/data to
the bottom of sheet?

Thanks so much! - Brice

> Actually you'd have to do this because of your requirement to have the data
> start on row 2 if you're just starting out. And the offset # was wrong, sb 1
[quoted text clipped - 12 lines]
> End If
> End Sub

Reply to this Message

Mike H. - 20 Dec 2007 10:26 GMT

Alll I did was change the 2,1 to 3,1 on the 2nd line of the sub....

Sub Doit()

Sheets("CashTransferRecord").Select

If Cells(3, 1).Value = Empty Then

Worksheets("Sheet1").Range("$A$2:$P$3").Copy

Worksheets("CashTransferRecord").Range("a2").PasteSpecial
(xlPasteValues)
Else

Worksheets("Sheet1").Range("$A$2:$P$3").Copy

Worksheets("CashTransferRecord").Range("A65000").End(xlUp).Offset(1,0).Cells.PasteSpecial (xlPasteValues)
End If

End Sub

> thanks everyone...it almost works now!
>
[quoted text clipped - 23 lines]
> > End If
> > End Sub

Reply to this Message

Mike H. - 20 Dec 2007 10:28 GMT

Oops. You wanted column C-3. So it is 2,3 as shown here....

Sub Doit()

Sheets("CashTransferRecord").Select
If Cells(2, 3).Value = Empty Then
Worksheets("Sheet1").Range("$A$2:$P$3").Copy
Worksheets("CashTransferRecord").Range("a2").PasteSpecial (xlPasteValues)
Else
Worksheets("Sheet1").Range("$A$2:$P$3").Copy

Worksheets("CashTransferRecord").Range("A65000").End(xlUp).Offset(1,0).Cells.PasteSpecial (xlPasteValues)
End If
End Sub

> thanks everyone...it almost works now!
>
[quoted text clipped - 23 lines]
> > End If
> > End Sub

Reply to this Message

Mike H. - 20 Dec 2007 10:29 GMT

Oops Again, You'd also have to change the A65000 to C65000. But that SHOULD
do it. Sorry for being scatterbrained....

> thanks everyone...it almost works now!
>
[quoted text clipped - 23 lines]
> > End If
> > End Sub

Reply to this Message