Macro For Find, Copy, Paste, Repeat

Mar 24, 2009

I have 10 worksheets. They are labelled 'FEB', 'MAR', 'APR', 'NOV' (NB: JAN & DEC not needed). In column "G" on all these sheets I have the data as "CURRENT" or "OVERDUE". The first 3 rows on every sheet are headers, so the data starts at 'G4' on every sheet.

I then have an "OUTSTANDING" sheet which will display all of the "OVERDUE" items from the various months. I have an UPDATE_Click() event which when actioned needs to search all the column Gs in the month sheets and then copy the data from column 'B' to 'F' of the corressponding row to an "OVERDUE" result. The data from B-F then needs to be pasted in the next available row on the "OUTSTANDING" worksheet (starting at row 4, as first 3 rows are headers with merged and unmerged cells).

Nothing fancy, when the search go does the column and hits a blank cell that means its the end of the list and can move on to the next month.

View 9 Replies


Repeat Copy Paste Macro Based On Value Of Cell

Mar 4, 2014

I need to repeat the following copy past macro the number of times equal to the value in column B and repeat the process for rows 3 through 27. It would end up being a loop with in a loop. The data in C3:H3 will be pasted onto the "Fixt#" sheet as many times as the value in column B3. The idea is to create an individual entry on the "Fixt#" sheet for each row on the "Totals" sheet.

Sub Create_Fixt_SheetI()
' Create_Fixt_Sheet Macro
ActiveSheet.Range("B" & Rows.Count).End(xlUp).Offset(1).Select
Application.CutCopyMode = False
End Sub

View 3 Replies View Related

Copy, Paste...but How To Repeat

Jan 7, 2008

I am a VB novice and I cannot seem to figure out how to loop a macro. So here is what I have...

Two worksheets, Sheet1 and Sheet2. I want to copy data starting from the second row of Sheet2 and then past it into specific cells on Sheet1. Then I want excel to print the the page. I want this process to repeat itself for all the rows in Sheet2. I can easily enough record a macro to do this process (which I have), but I cannot figure out how to easily loop it. If someone can help I have to print this log daily to meet federal guidelines. Thanks a million everyone!

An example would be to copy cell A2 on Sheet2, then paste in cell D4 of Sheet1, print and repeat for the next row.

View 9 Replies View Related

Find Value, Copy And Paste Macro

Apr 27, 2007

The macro finds the value in cell D11 (which is the name of a product) in a worksheet called "Summary1". It then looks for this value in another worksheet called "Output", specifically in a column with a named range called "Products". If the value being searched is found in the "Products" named range column (in the worksheet "Output"), the code below copies the entire row for it, to another sheet called "OutputSummary1". I would like to change the code as follows:

1) Instead of copying the entire row, for which the searched for value is found, from the sheet "Output" to the sheet "SummaryOutput1", I would like to instead only copy the values in columns A, B, and E to the sheet "OutputSummary1". Also, I would like the values to be pasted pastespecial so that the formulas in the sheet "Output" are not copied over, and only the values are copied.

2) Secondly, I would like to know how to perform the find procedure (searching for cell D11 contents) on all sheets in the same workbook beginning with the word "Summary" (There will be worksheets called Summary1, Summary2, Summary3, etc. that the macro should be performed on.) As well, the pasting should be perfomed on all respective sheets called OutputSummary1, OutputSummary2, OutputSummary3, etc.

Sub CopyPaste()
With Worksheets(1).Range("Products")
Set c = .Find(Worksheets("Summary1").Range("D11").Value, LookIn:=xlValues) 'this identifies the value D11 in worksheet called Summary1
If Not c Is Nothing Then
firstAddress = c.Address
c.EntireRow.Copy Destination:=Worksheets("OutputSummary1").Range("a" & Worksheets("OutputSummary1").Range("a65536").End(xlUp).Row + 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

View 7 Replies View Related

Excel Macro To Repeat Paste Between Sheets

Apr 27, 2014

I have 2 sheets of date. Sheet 1 contains a temple range A3:T162. This set of data is repeated x number of times. Sheet 2 contains a range of data A1:A3 repeated x number of rows. The data is different on each row. So if I have 10 rows of data in Sheet 2 , there will be ready in Sheet 1 10 sets of dats, as in range A3:T162.

I would like the following action:
the contents of Sheet 2 Row 1 to be pasted into Sheet 1 cell A3,B3,C3
then contents of Sheet 2 Row 2 pasted into A163,B163,C163,
then contents of Sheet 2 Row 3 pasted into A323,B323,C323,

So basically each consecutive row on Sheet of column A,B,C is pasted back onto Sheet 1, 160 rows below until all contents of Sheet 2 has been exhausted.

I have attached an excel sheet with the relevant data.

View 6 Replies View Related

Macro To Find / Copy And Paste To Cell

Jul 26, 2013

I want to find data from a certain point and paste this to a cell. My objective is to get all the data on too one row. This row is always 1 in col M called Run#. The find locations never change, just the data being copied always changes . Heres example,

In col K find FHBASC and look across to the right 3 cells in col N and copy.
Look in col M and find 1, paste above copied data into col GI.

Then move down rows to next FHBASC and repeat process until end of sheet.

If I can get a very basic simple macro to do this, then hopefully I can just re write to suit my other data.

View 9 Replies View Related

Macro Find Text, Copy, Paste Special

Mar 4, 2009

I have a workbook with many sheets in it. Within each sheet there is a cell with the Text "March 09" there are then 3 cells to the right of this cell with relevant information. Is there a Macro that can search for this text March 09, then copy this and the 3 cells to the right of it and paste special this information 1 cell below for all 4 cells.

View 2 Replies View Related

Macro - Find Text And Copy / Paste Cell Below - Loop In Row

Nov 5, 2013

Let's say sheet - "delivery" - has a row1 that includes all possible delivery days. So A1 is 1.9.2013, B1 is 15.9.2013, C1 is 1.10.2013 and so on.

I want to make a macro, that will gradually go through whole row 3 in sheet "delivery" and look for "A1" date in sheet "Orders". Sheet "orders" have for example in column B the date of delivery, and in column C product of the delivery.

I want the macro to find all deliveries with "A1" date, and paste all products that will be delivered on this day under cell A1 (sheet delivery). then move on to the cell B1 (sheet delivery) - find all orders in sheet orders, that will be delivered on B1 date, and list all products with this delivery date under cell B1 (sheet delivery).


Sheet order

Date of delivery


[Code] ........

Unfortunately simple Pivot table is not able to do this simple list.

View 3 Replies View Related

Excel 2007 :: VBA Find Macro Executes But Copy / Paste Not Working

Mar 23, 2012

Excel 2007, Windows XP Pro

Dim strLoan As String
Dim longCat As Long
Dim rHere As Range
[Code] .....

I can see the cursor move to all the desired cells when this macro executes; it just isn't dropping any data where it should be. I have been working at this stage for the last five hours with no success. I don't know whether my copy-paste methodology is broken or if it is my selection criteria

View 1 Replies View Related

Macro To Find Required Data Than Copy/paste In Specific Cell Address

Nov 4, 2009

I want to copy some data from internet and paste it in to worksheet location Range A1:A30. the data look like following information.

LocationKuwaitP.O.Box:4819 SafatAddress:ShuwaikhTel:-22253580Fax:-24343645Category:CONSTRUCTION COMPANIES

the above information have following header ie. Location, P.O.Box, Address, Tel, Fax and Category.

every time one of the header inforamtion is missing ie. Address or P.O.Box, Or Fax, due to this problem every time cell address of information aganist header is change. supose in first copy/paste Tel inforamtion is in A15, but second time it may be on A13, and 3rd time it may be on A17 and so on.

I want to make a macro which search header ("Location", "P.O.Box", "Address", "Tel", "Fax", "Category" ) from a Range A1:A30. than down two steps to copy header information (75325412) and paste in fixed cell location C1:C5 (C1=Name, C2=Location, C3: P.O.Box, C4=Address, C5=Tel, C6=Fax, C7=Category) in same sheet. and repeat the steps to copy information aganist header from all header in Range A1:A30.

View 9 Replies View Related

Find Last Cell In Column And Paste Formula In Next Blank Cell Then Repeat On Remaining Columns

Jan 14, 2013

I need a macro to find the last cell in the column, then copy the formula to the next blank cell. Then, it goes back to the last cell (above) and paste's values. Then, go to the next column and repeat the process. I can do this but have to call each cell separatly...however, I would like to do it in a loop to simplify things. It would be great to even be able to just set the start and ending columns. Here is my current code:

Dim rng As Range, aCell As Range
Set rng = Range("C8, D8, E8, F8, G8, H8, J8, K8, L8, M8, N8, O8, P8, Q8, R8, S8, T8, U8")
For Each aCell In rng
Application.CutCopyMode = False

[Code] .......

It does not go to the next column, instead it stays in the same column and repeats the process.

View 8 Replies View Related

Macro To Find Value In Cell And Copy / Paste To Other Cell

Dec 11, 2013

I have the following code, so far:


Sub CopyPasteValue()
Dim ARow
Dim AColumn
Dim BRow
Dim BColumn
Dim Value
AColumn = 1


What I want it to do is to start in cell B1 and look down until it finds a non-blank cell; copy the contents of that cell. I then need it to go to the same row Col A and go down from until it finds the first non-blank cell then paste into the corresponding cell in Col B. It should continue this way through the rest of the worksheet. There is no set pattern to say to go down 5 rows or such. Also the number of pastes in each step is variable from 1 to ??

In my worksheet, the first non-blank cell in Col B is cell B3 and the first non-blank cell in Col A is A7. The paste should occur in cells B7:B10 because the next non-blank cell in Col A is A11.

View 2 Replies View Related

Excel Macro To Copy Data From Array And Paste To Separate Sheet Paste Special Transpose

Jan 29, 2014

I would like to implement specific cell ranges from two specific worksheets each within 33 workbooks (which all have several tabs) into a summary page in a separate workbook.

The cell ranges are going across my spreadsheet in rows and I would like for them to transpose into a columns depending on the data which I have separated by catergory on the summary page. They are all on the same location in each workbook which is separated by country. The cell ranges are E26:P37 and I would like to transpose them and have them put below eachother without overwriting for my format on the summary page, how I can put this together in a macro?

View 1 Replies View Related

Create Macro That Can Copy / Paste Or Cut / Paste Rows Into Different Spreadsheet

Oct 1, 2012

I have one workbook that needs two macros.

On the "Complete Backlog" tab of my workbook, I want users to enter in the requested information based on the column header. Then I would like a Macro attached to a button that says "Refresh" that the user would click after they have entered in all of the information. This macro should look in Column M (WIP Status) and if any of the cells say "Close", it should Cut the entire row from the spreadsheet(Ex. A2:M2) and Paste it into the speadsheet titled "Closed Jobs".

This is so that as jobs are closed/finished, they are removed and stored on a separate sheet. The items would have to be pasted so that it pastes into the next available row - not just on top of each other.

I also need another macro that i can put into a button that doesn't "delete" a row from the sheet, but just copies over to another sheet - so that there are two instances in the workbook.

If would look something like: If a cell in "Column G / Director" of the "Complete Backlog" speadsheet is equal to "Snodgress" then copy columns A-L of the same row to the spreadsheet titled "Snodgress" - of course skipping down the rows to the next blank row. equal to "Herr" copy row to "Herr" spreadsheet. equal to "McCormick" copy row to "McCormick" spreadsheet.
and so on.

View 2 Replies View Related

Find Copy And Paste

Apr 22, 2006

The following is a sample spread sheet similar to ones I use daily.

I am trying to create a macro that will help me do the following.

Look for the same account # shown in Column A to Column F. If the account # matches, copy the total Value in Column H to the correct account in Column C.

The problem here is that In column A, i've placed some headings so i'm not too sure if that'll cause a problem.

View 9 Replies View Related

Is It Possible To Repeat A Transpose Paste Special

Sep 12, 2009

I have over 300 lines of data that I need to transpose from columns to rows and I am not sure the best way to do it.

I can do it manually 125 times using paste special and transpose and get the results I need but I am thinking there must be a better way.

Example spread sheet inclosed. Column A is what I have - Column C-F is what I need.

View 8 Replies View Related

Find, Copy And Paste Between Two Sheets

Jan 27, 2010

I'm currently making a database of my DVDs and BluRays, and thought that doing it in Excel, would make it nice and easy to see.

Tho, after some testing on my own, and redoing some of the sheets, to make it easier and less work, I'm stuck ....

View 8 Replies View Related

Find Value And Copy Row And Paste In Sheet 2?

Oct 16, 2013

I have a value in sheet3 cell "C9". (the value is stewart)

I am tring to have the macro go down column "C" in sheet1 and the value is the same as "C9"s value in sheet3 then copy the 7 cells to the right and paste them in the 7 cells right of "C9" in sheet3.

Example: If the value in cell "C9" in sheet3 = "stewart" and the value in "C109" in sheet1 is "stewart" the copy C110:C116 in sheet1 and paste those values in C10:C16 in sheet3.

I guess you wouldn't have to offset, you could copy C109:C116 and paste it to C9:C16 since its the same value.

View 9 Replies View Related

Find Then Copy And Paste Into Different Workbook

May 30, 2014

I am trying to find text in one workbook and paste it into another. I've tried a Vlookup, and now I'm trying a Find, and neither is working.

This is what I have so far...

Sub FindAddress()
Dim GCell As Range
Dim Page$, Txt$, MyPath$, MyWB$, MySheet$
Txt = "N.A.V."
MyPath = "T:01862a7R228 Reports2039067"

[Code] ...........

View 9 Replies View Related

Copy- Find- Arrow To Right- And Paste

Mar 15, 2007

I have two spreadsheets, A & B. In spreadsheet A, user will input a contract number and values for the contract. The user inputs the contract number in cell D4. I have linked this cell to cell B1 of spreadsheet B.

I want Excel to

-copy values from spreadsheet A

-then go to spreadsheet B and find the value of B1(which is the contract number entered in cell D4 of spreadsheet A)

- arrow over 14 columns

- then paste

here's the code i have so far. No matter what contract I input in cell D4 of spreadsheet A (when i step through the code) excel always takes me to cell C10 of spreadsheet B!?!?!

note: the code is in a module in spreadsheet A

Sub find_contract()
' find_contract Macro
' Macro recorded 03/15/2007 by b944553

View 9 Replies View Related

VBA - Find Copy Then Paste Special

Sep 1, 2008

Im trying to get some VBA to search in my worksheet to find a match to the value of cell B5 (the value of B5 changes depending on user choice), then once a match is found I want to highlight from the matched cell to the right 5 columns and down to row 193 (so in total 6 columns would be highlighted down to row 193), then copy and paste special values over those cells.

View 9 Replies View Related

Find Match Then Copy & Paste Into Row

Aug 24, 2009

I need a Macro that will search the range A6:A19 for a match to A3. Once a match is found I want contents of B3:F3 pasted in the corresponding row that the match was found in. In this example contents of B3:F3 would be pasted in B13:F13

View 15 Replies View Related

Find Match, Then Copy And Paste

Apr 17, 2007

I basically need to copy/move the text in column E, from vertical to horizontal using VBA when column C is the same. Then delete the extra lines. eg. C1:C3 = 1, so all the text from E1:E3 needs to goto E1:G1, then Rows 2&3 can be deleted as they are no longer needed. (Note: there are not always 3 instances, this can vary from 3-10). Its a bit hard to explain so i have included the Sample-finished.xls file as this is how it needs to look once its complete.

View 3 Replies View Related

Find Copy & Paste To Another Workbook

Jul 28, 2007

Here is some sample code I found on the internet similar to mine:

Windows("Waterfall 1.xls").Activate
Cells. Find(What:="accounts", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Windows("test Schedule.xls").Activate
ActiveCell.Offset(0, 0).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.Offset(0, 1).Range("A1:G1").Select
Windows("Waterfall 1.xls").Activate
ActiveCell.Offset(0, 2).Range("A1:G1").Select
Windows("test Schedule.xls").Activate
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "4"
ActiveCell.Offset(0, -1).Range("A1").Selectp.

I now understand that there's a way to remove the activate statements, while still selecting that document to work with, and therefore greatly increasing the speed.

View 3 Replies View Related

Find, Copy & Paste Between Workbooks

Aug 21, 2007

I am trying to find out if it is possible to create a macro in an excel workbook that will open another workbook saved on the computer, perform a find, copy specific data, and paste it in the opened/active workbook.

View 8 Replies View Related

Vlookup And Row To Find / Copy / Paste Data?

Feb 22, 2007

I am needing to find a specific row of data in one workbook and, once found, copy and paste the values into another workbook. The worksheets in both workbooks are formatted exactly the same. Only the first eight fields of the row need to be copied (e.g. A7:H7, A150:H150, etc.)

Will a combination of VLOOKUP and ROW do this and how do I get them to work together? Once the correct row is found, how are the fields selected, copied and pasted into the specific worksheet of the other workbook?

View 6 Replies View Related

Find Text Copy 24 Cells Below Paste G1?

Feb 15, 2014

Finding any specified Text like "Cube" down a specified Column in this case "D" when Text has been found
Copy the 24 cells directly below and Then Paste to G1.

View 7 Replies View Related

Find Next Empty Row And Copy And Paste Information

May 19, 2009

I want to insert some data in a number of cells (e.g. A1, B4, C4 and H8) on a worksheet ('Sheet 1').

I then want to press a button and have some code that:

Looks at column B on 'Sheet 2'
Finds the next empty row Copies and pastes the information from Sheet 1 into specific cells in that empty row.

View 2 Replies View Related

Find Multiple, Copy, Then Paste To Another Sheet

Jul 9, 2009

Thank you all so much for this wonderful forum. Today has been a day of going through post after post. I am usually able to solve my problems through reading similar issues. However, just can quite get this one and I think it is rather simple, but frustrating none the less.

Attached is a spreadsheet with a list names in column B. The codes I have tried to write or have copied find one name and paste it to sheet 1. However, I need to find several names and then copy each row to sheet 1.

View 14 Replies View Related

Find Data Copy / Paste Each Value Into Different Sheet

Feb 21, 2012

I need to firstly find data in the main sheet which relates to the activecell and then I want to copy and paste that data into another worksheet.

The simplest of ways I guess would be the .xldown function however this seems to copy the blank cells as well as the cells with data, which is no good. I have therefore written code as below which finds the first cell with data, copies and pastes, then loops.

How do I set the loop to look at the next cell down from the initial copied cell and if there is data in it to paste it into the cell underneath the one I have just pasted into?

Range("D3") = ActiveCell.Offset(0, 1)
SheetName = Range("D3")
ActiveCell.Offset(0, 2).Select


View 7 Replies View Related

Copyrights 2005-15, All rights reserved