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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"nc" <(E-Mail Removed)> wrote in message
news:507A87AB-9CF7-49F2-8E9B-(E-Mail Removed)...
> 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

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
On Error GoTo 0
End Function

"Don Guillett" wrote:

> 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
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "nc" <(E-Mail Removed)> wrote in message
> news:507A87AB-9CF7-49F2-8E9B-(E-Mail Removed)...
> > 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
>
>

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"nc" <(E-Mail Removed)> wrote in message
news:3A790034-5744-46DF-8C0A-(E-Mail Removed)...
> 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
> On Error GoTo 0
> End Function
>
> "Don Guillett" wrote:
>
>> 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
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "nc" <(E-Mail Removed)> wrote in message
>> news:507A87AB-9CF7-49F2-8E9B-(E-Mail Removed)...
>> > 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, 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.

"Don Guillett" wrote:

> 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
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "nc" <(E-Mail Removed)> wrote in message
> news:3A790034-5744-46DF-8C0A-(E-Mail Removed)...
> > 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
> > On Error GoTo 0
> > End Function
> >
> > "Don Guillett" wrote:
> >
> >> 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
> >>
> >> --
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> (E-Mail Removed)
> >> "nc" <(E-Mail Removed)> wrote in message
> >> news:507A87AB-9CF7-49F2-8E9B-(E-Mail Removed)...
> >> > 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
> >>
> >>
>
>