Copy & paste range

[nc]

Can you please help write this macro to achieve the following,

I would like to copy the data from sheet "Bank Statement" to "Transactions".

The macro should check if the cell A2 on the "Transaction" sheet is empty.
If it is just insert 1 in the cell A2 on the "Bank statement" sheet. Then
copy the data from that sheet to A2 on the "Transaction" sheet.

If cell A2 on the "Transaction" sheet is not empty. Look at the maximum
value in column A of the "Transaction", add 1 to this value and insert it in
cell A2 on the "Bank statement" sheet. Then copy the data from that sheet to
the "Transaction" sheet, but this time find the last row being used, and
paste the data in column A, in the row below the last row. In my example
this would be row 6.

I hope it is clear what I am trying to achieve.



Sheet named "Bank Statement"

A B C D
1 Row Date Text Amount
2 02/07/2009 ddd 20
3 =B10+1 02/07/2009 eee 50
4 =B11+1 02/07/2009 fff 29
5 =B12+1 02/07/2009 ggg 30
6 =B13+1 02/07/2009 hhh 33


Sheet named "Transactions"

A B C D E
F G
1 Row Date Text Amount ID Name Amount
2 1 01/07/2009 aaa 100
3 2 01/07/2009 bbb 200
4 3 01/07/2009 ccc 300 1000 John 2000
5
1001 Jack 1000
6

 

[Don Guillett]

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

And your macro so far

 

[nc]

Don

The following code does what I am trying to achieve,

Can you help me ammend this line,

Set SourceRange = Sheets("Sheet1").Range("A26")

to select only populated range i.e. A2?. Please note that column A may
have blank formula value which needs to be excluded.


I would like to acknowlege the code below is from Ron De Bruin website and
help from yourself.

Thanks.

Sub test()

Range("A2").Value = Application.Max(Sheets("sheet2").Columns(1)) + 1

Call Copy_1_Value_PasteSpecial

End Sub



Sub Copy_1_Value_PasteSpecial()
Dim SourceRange As Range, DestRange As Range
Dim DestSheet As Worksheet, Lr As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'fill in the Source Sheet and range
Set SourceRange = Sheets("Sheet1").Range("A26")

'Fill in the destination sheet and call the LastRow
'function to find the last row
Set DestSheet = Sheets("Sheet2")
Lr = LastRow(DestSheet)

'With the information from the LastRow function we can
'create a destination cell
Set DestRange = DestSheet.Range("A" & Lr + 1)

'Copy the source range and use PasteSpecial to paste in
'the destination cell
SourceRange.Copy
DestRange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

 

[Don Guillett]

If your col a has numbers try to match any number larger than possible in
your column.

with sheets("sheet1")
lr = Application.Match(99999, .Columns(1))
Set SourceRange = .Range("A2" & lr)
end with

 

[John P]

Don, could you please explain how Application.Match(99999, .Columns(1))
works. I thought this expression should give an intermediate row if it has
the largest value that is less than 99999 but yet it still gives the last row
even with blanks in between. Thanks. Sorry if I had to start a new thread.