Generate ongoing database of results via Macro

[Brice]

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 A23. 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 A23 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

 

[JLGWhiz]

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

 

[JLGWhiz]

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

 

[Brice]

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?

 

[Brice]

Can someone help me with this MACRO problem?

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 like to have the macro ADD the
data/record to the next available rows so that I can keep an ongoign database.

Can you help?

 

[Jean-Yves]

Hi Brice

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

 

[Mike H.]

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

 

[Brice]

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

 

[Mike H.]

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

 

[Mike H.]

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

 

[Mike H.]

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