Macro - VBA

sitelbanat

Board Regular
Joined
Oct 17, 2005
Messages
152
how would I write a macro that says

IF column A sheet 1 = "Bank Account Number"
then copy the data in that same row from column C and H to paste them in sheet 2 column A and B
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
How's this:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> foo()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range([A1], Cells(Rows.Count, "A").End(xlUp))
            <SPAN style="color:#00007F">If</SPAN> c = "Bank Account Number" <SPAN style="color:#00007F">Then</SPAN>
                <SPAN style="color:#00007F">Set</SPAN> LastRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)
                <SPAN style="color:#00007F">With</SPAN> LastRow
                    .Offset(1) = c.Offset(, 2)
                    .Offset(1, 1) = c.Offset(, 7)
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Hello sitelbanat,
You didn't specify if you want these values to be placed in sheet2 on the same
row(s) they come from on sheet1 (which could be done using formulas instead of vba)
or if you want them just listed on sheet2 from row 2 on down.
Below is a way to do either.
First one will place them in the same row(s) they are found in on sheet1.
Code:
Sub Demo()
Dim LstRw As Long, ThisRw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row

For ThisRw = 1 To LstRw
  If Cells(ThisRw, "A").Value = "Bank Account Number" Then
    Sheets("Sheet2").Cells(ThisRw, "A").Value = Cells(ThisRw, "C").Value
    Sheets("Sheet2").Cells(ThisRw, "B").Value = Cells(ThisRw, "H").Value
  End If
Next ThisRw

End Sub

Second one will just list them out in sheet2 starting in row 2.
Code:
Sub Demo2()
Dim LstRw As Long, ThisRw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row

For ThisRw = 1 To LstRw
  If Cells(ThisRw, "A").Value = "Bank Account Number" Then
    Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)(2).Value = Cells(ThisRw, "C").Value
    Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp)(2).Value = Cells(ThisRw, "H").Value
  End If
Next ThisRw

End Sub

These are both intended to be run from sheet1.


[EDIT:]
Hey Smitty, how's things?
Been away for a while. (On vacation.)
Happy to report the moose is in the freezer! :cool:
Looks like we get to eat again this winter. :p
 

sitelbanat

Board Regular
Joined
Oct 17, 2005
Messages
152
Hello HalFace

The first macro did work but it only covers the first set of data on the sheet. How would i change it to go through all of the data in the sheet.

every set of data has the account number, amount and the activity. I need the macro to capture the account number and amount then paste them in sheet 2. I have 80 Accounts.
 

sitelbanat

Board Regular
Joined
Oct 17, 2005
Messages
152

ADVERTISEMENT

How's this:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> foo()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range([A1], Cells(Rows.Count, "A").End(xlUp))
            <SPAN style="color:#00007F">If</SPAN> c = "Bank Account Number" <SPAN style="color:#00007F">Then</SPAN>
                <SPAN style="color:#00007F">Set</SPAN> LastRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)
                <SPAN style="color:#00007F">With</SPAN> LastRow
                    .Offset(1) = c.Offset(, 2)
                    .Offset(1, 1) = c.Offset(, 7)
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty

This didnt work
 

sitelbanat

Board Regular
Joined
Oct 17, 2005
Messages
152
i run the macro and nothing happend, let me explain again

In sheet1 it contains account number, account balance, and activity of 80 different accounts. i need the macro to go through all that data and copy the date, account number, and account balance then paste it in sheet2. placing the date in column A, account number in column B, and amount in column C. listing all 80 accounts.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
The first macro did work but it only covers the first set of data on the sheet. How would i change it to go through all of the data in the sheet.
The code in the first routine I posted is being told to go through each cell in column A (from row 1 down to the last row with data in column A) and check to see if the value in each cell is the text "Bank Account Number".
If that condition is true then it will copy (just the value) of column C from that row to column A in the same row number on sheet 2, and copy the value from column H from that row to column B in the same row number on sheet 2. This is working for me as intended.
What results are you getting exactly?
Is the text "Bank Account Number" found in column A of all rows it's found in?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,126
Messages
5,857,523
Members
431,883
Latest member
Hien

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top