Excel 2003 :: Copy Paste With VBA Using Do Until Loop?
Jan 8, 2012
I'm trying to use a VBA Macro to copy and paste a row of cells from 14 worksheets into in another worksheet in a Colum. The worksheets are entitled Data 1, Data 2, Data 3 and so on until Data 14 and the worksheet that I want to paste the data to is entitled sheet 1. The row of data that I want to copy and paste is in the same place on each worksheet C4 - IR4. I can do this using the Macro recorder but there is a lot of code and ultimately I want this to be part of a much bigger Macro once I get better at figuring this stuff out.
I have read that this can be done by using a Do Until and then using a loop. By the way I am using Excel 2003 with XP.
I have a workbook, in which from sheet1, I have to copy first row and paste it in another workbook in sheet1 This I have to do for all the rows present in first workbook. I have written code for one single row, but for all rows,
below is the code :
Option Explicit Dim wbIP As Workbook Dim wbJT As Workbook Dim wbET As Workbook Dim mypathET As String Dim mypathJT As String Dim mypathIP As String Dim vals As Variant
Copy over data from different workbooks and using paste special values to paste it into a new workbook using a macro. Here is what I have and what I am looking for:
My file path is
C:Documents and SettingsMy DocumentsProjectCostsDecember12
I also have a workbook called DecMonthlyTotal in the same folder with the same named worksheets.
I am looking for a macro to be placed in the DecMonthlyTotal that will pull the data from the Cashable12-13 worksheets from Function1, Function2 and Function3 and paste special the values into workbook DecMonthlyTotal, worksheet name Cashable12-13, it will also pull the data from the NonCashable12-13 worksheets from Function1, Function2 and Function3 and paste special the values into workbook DecMonthlyTotal, worksheet name NonCashable12-13
Both the Cashable12-13 and the NonCashable12-13 have Columns A - G The row that the macro should start the copying from is Row 3 for each of the workbooks; however I don't have an end row for the workbooks as this will vary.
Background: - Excel VBA 2007 - I have a macro that inserts a set of vlookups in range D3:D8. - When D3:D8 has been filled, I need the macro to COPY the Vlookups in D3:D8, skip down one cell, and paste in the next set of open spaces. - I have a Do statement that isn't working, and and IF...THEN that quits the loop when a blank cell is found (This means the range of vlookups is no longer needed).
Objectives: - Fix Do...Loop so it copies D3 throuh D8, skips a cell below D8, and Pastes in the next section? - There will sometimes be only one section to paste in, and sometimes 20. It is dynamic.
Code:
Sub InsertVLookup2() ' This macro inserts the VLookup into cell B2 Dim lastcolumn As Long Range("D3").Formula = "=VLOOKUP($A2,INDIRECT(""'"" & B$1 & ""'!A:I""),9,FALSE)" Range("D3").Select
I have data from (row 1, column 1) to (row 53, column 5) on 283 consecutive worksheets in a singular excel file that I would like to be presented on a singular worksheet starting from the data on worksheet 1 and descending to the data on worksheet 283.
I am looking for a copy and paste loop solution that will copy the data from each page and sequentially paste the results on a singular output page in descending order (worksheet 1 data, worksheet 2 data... etc) so that I can sort the data.
What I need is the code for a loop which copies a formula from cells E1 and E2 and then pastes it, staring at C7 and then every 32 cells down until row 20000.
The formula attached here is in cell J4. I pulled this formula down to J26. Then copy J4:J26 and paste to M2:M26, P2:P26, until CG2:CG26. I need 26 times to paste to the range. There are even 4 columns distance between two pasting range.
How can write a For Loop to copy and paste to these ranges?
Is there any way users can cut and paste while keeping the Conditional Formatting of the source cells intact? It is the same as copy-paste and then delete the source cell contents after copying.
I have a financial model (attached) which calculates the final cash flows (in a calculation sheet named 9.Operations, rows 428 to 437) for a particular plot. However,the model has a list of 17 plots in the input sheet (3.Input-Plot Details). The macro must change the plot number in the 9.Operations sheet(cell G11), re-calculate the final cash flows and paste all the final cash flows for 1 to 17 plots in a single separate sheet consecutively.
I've attached below a small part of my code. I am wondering if there is a way to do this, without copying and pasting something 30 times, but using a loop instead. As you can see there are a series of productsNumbers (30 in total, but this example is for 3). Each productNumber is a string that is actually a 5 digit number, so it can be an integer as well, if it has to.
I would like to code below to loop for as many products as there may be. The way that I have it right now does not work - I feel that I am missing something small or that there is no way to do this.
Finding the value "OK" in a range of data in Worksheet(1) out of Range("Product"). Ones the value "OK" is found, the entire row is cut and then pasted into a new worksheet 'Range("A3")'. Then the loop sets in and finds the next value "OK" in the range untill it reaches the end of the predetermined Range("Product").
The only problem I have is that the code I have written already performs the process, but when pasting the data into the new worksheet, paste's all of the found rows into the same row. So what you are left with in the new pasting sheet (Worksheet2), is only the last found row because it keeps overiding previously found data. What I need the Macro to do is find the next availible blank row in Worksheet2 and for all values cut out of Worksheet1. Now there was a simular posting to this on the forum, but when I tried it in my code it would not work...
Sub FindAndPaste1() With Worksheets(1).Range("Product") Set c = .Find("OK", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Cut Destination:=Worksheets("Sheet2").Range("A3") Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress End If End With End Sub
I also attach the Excel spreadsheet called Product Macro.xls
I want to make a vba (macro) for excel that will look at the column to the left of an active cell and if there is a formula there drag it over to the right into the active cell and if there is not a formula there copy and paste value of what is in the active cell into itself deleting any formulas that may have been there. on top of that if the cell to the left of the active cell has no boarding I need to copy it and paste format to active cell. I need it to continue a number of times to be decided in cell A1 and just go down the column doing this. Please help, I know it sounds hard but I need it for 10,000's of lines of code and really cant do it by hand ...
Dim UltFila As Long, i As Integer, k As Integer UltFila = Range("A65536").End(xlUp).Row k = 2 For i = UltFila To 2 Step -1 If Cells(i, 2) = 4102 Then Range(Cells(i, 3), Cells(i + 15, 6)).Copy Sheets("Final").Cells(k, 1) End If If Cells(i, 2) = 4104 Then Range(Cells(i, 3), Cells(i + 15, 6)).Copy Sheets("Final").Cells(k, 1) End If ..................
Spreadsheet 1 has 10 columns of data (A-J). I want to copy a variable number of rows from spreadsheet 1 to spreadsheet 2.
When I paste into spreadsheet 2, I'd like to automatically insert blank cells in three places, taking the total number of columns to 13. I'd like columns C, F and I to be blank, and the last column with data to be M.
I will perform this task regularly, and add the copied cells to the bottom of spreadsheet 2, so I'd only like to insert blank cells within the range that I'm copying, not the entire spreadsheet.
I will then populate the blank cells with a VLOOKUP function. Do I need another macro to automatically add the formula to the cells, or is there a way to include this in the cell-inserting macro?
VB: Sub CopyNextTab() WorkbookName = ActiveWorkbook.Name Cells.Select
[Code]....
I'm not entirely sure if this is correct, but what I'm looking to do is the following:
1. On the currently selected sheet, copy all data into the "Pasted Data" sheet on "Test.xlsm" 2. On the "Calculations sheet", copy all information across to the next available row on "Master Sheet" 3. Select the original Workbook and move to the next tab 4. Repeat until there are no more tabs remaining
So far as I can tell steps 1-3 are working (however I'm not entirely confident with my code to move to the next sheet as I'm not sure it will end the sub on the last sheet).
1) Select/Copy rows 1 thru 35 of column C from "Start" tab. 2) Special Paste/transpose them in row 2 of "Finish" tab (1st 35 columns in row 2 should fill up). 3) Go back to "Start" tab, move exactly three rows down from bottom of previous selection in column C and copy the next 35 rows (rows 38 thru 72 in column C). 4) Again return to "Finish" tab, and special paste/transpose in next row down (row 3 in this case). 5) Continue to repeat this action until all rows of data in column C have been copied and transposed/pasted from "Start" tab to "Finish" tab. Variable number of rows on "Start" tab.
I have a long column of data in column D (D2 to D8761) and I would like to sequentially select 24 cells at a time (D2:D25, D26:D49, D50:D73...D8738:D8761) copy them and paste them to another sheet as separate rows.
For instance, the selected data from Sheet 1, D2:D25, would be pasted/transposed to Sheet 2 B9:Y9. I would like the macro to loop so it would then select D26:D49 and paste/transpose to B10:Y10 and so on until it finishes transposing the final data group of D8738:D8761.
I'll add the macro that I recorded using the brute force method so perhaps you can have a better understanding of what I am trying to accomplish.
My code (with help from this forum) loops through all workbooks, all sheets and all columns OK as I have tested it with message boxes
I need to take the value of Range("C5") from each column of all sheets of all workbooks and paste it to Range("A4") downwards in Workbook("Loop Folder.xls") . That is, each new value is inserted in the next row of column A.
Sub test4() ' populate analysis sheet ' copies cell("C5") from each column in each sheet in each workbook in a directory
Dim Mypath As Variant Dim excelfile As Variant
Mypath = "U:September 2006" ' folder where all excel files reside excelfile = Dir(Mypath & "*.xls")
I often need to use Paste Special . .. Values, using Excel 2003. So I recorded a Macro into "Personal.xls" and assigned a shortcut key combination "CTRL-Shift-V" so that it would always be available. I first select one or more cells and hit CTRL-C to copy to clipboard, click on another cell, then hit CTRL-Shift-V to run the following macro:
It often works . . . and it often fails. When it is working it seems to keep working over and over. When it is failing it keeps failing. So it is almost as if there are two "modes" of operation, which for lack of terms, for this post I will coin the terms "fail mode" and "fixed mode". Once I am in one of the two modes, it stays there for a while. When it is in the "fail mode", I always get the dreaded:
Run-time error 1004: "Pastespecial method of Range class failed".
Since this problem is very intermittent, I believe this is why there are so many posts in the Internet about this, and so many people are replying back "it works fine for me". I have seen many solutions offered but none work so far for just a simple shortcut key to do a PasteSpecial :Values. Some solutions "work" . . . such as adding the PasteSpecial button to the Toolbar, or pressing ALT-E-S-V <Enter> - but both of these bring up the Paste Special dialog box which I want to avoid.
I spent days trying the many solutions offered but so far all I have found is a trick to convert Excel from "fail mode" to "fixed mode". The trick is to add a line to the macro to first do a "Paste Special: Format:
Then select and copy any cell/s, click on another cell, and run the macro just ONCE - it will paste the formatted value successfully and you are now in the "fixed mode". Now you can remove the PasteSpecial:Format line that you just added, to get back to the original macro with just the "Paste Special: Values" line:
Now Excel is in the "fixed mode" and the original macro will once again start working properly. BUT only for a while !! Then some time later, usually within a day or a couple hours . . . Excel goes back into that "mode" where once again the macro fails repeatedly. I have been unable to isolate what causes the mode to return to failure.
Any simple way to either change the code so that it ALWAYS is successful, why this often is not successful ? I believe that the fact that adding in the Paste Special : Format code and running it just once to fix the problem, also could be a clue as to what causes this problem to "sometimes" occur.
I have a data sheet with employee information. Only one column. It prefixes information with codes, but keeps it in the same cell. I want to move data of certain types into their own columns, but the amount of data is variable, so I cannot simply move every Nth cell, etc.
So you see some employees may have different codes altogether. But I know that I want all the cells that start with 200 to be offset (-1,1), and all the cells that start with 204 to be offset (-1,2), and so on so that basically I end up with columns of info instead of a one column list.
I have been reading and studying other peoples' macros, and am just starting to grasp the basic. When I wrote my own to accomplish this, I put this together, which doesn't work. But I don't know enough to know what I don't know.
I have one column of names in excel. The column may contain more than one row with the same name but these rows with the same name will all be grouped together. This is an exampe (each name represents a row in column A):
What I want to do is copy the rows with the same information, e,g, the rows with 'julie' above, paste them into a new spreadsheet and email this spreadsheet to specific email addresses and then do the same for 'jonathan'.
I can work out how to send an email using VBA but I am really stuck as to how to go through the rows and send the email in discrete 'chunks'. I have tried using a for next loop, looping through the rows and copying/pasting rows that are the same as the previous one into a new spreadsheet but this does it one row at a time. If I include the instruction to email the spreadsheet within the loop this would also email the new spreadsheet one row of information at a time, i.e. three emails for 'julie' each containing a spreadsheet with one row of information on it, rather than one email containing one spreadsheet with all three rows on it.
I have a rand () which provides me a different number upon calculate. I would like to run a loop for 1000 times and take the value and copy and paste in a range.
The below sorta works, but at times it stops or doesn't work at all. I have a loop for the counter and the for each to place the value into the cell and have it run over and over and placing the value in the next cell underneath. I have the counter at 10 so I dont get stuck in some endless loop before I know it works.
Code:
Sub montecarlos() Dim MCs As Long Dim c As Range Dim lCount As Long Dim lNum As Long lCount = 0 lNum = 1 Worksheets("MonteCarlo").Activate
I have an Excel 2003 file that contains hyperlinks to OneNote notebooks on a Sharepoint site. An Excel macro looks for these links and determines the full hyperlink address which is then assigned to a variable. An Outlook message is generated which includes the hyperlinks.
The hyperlinks work in the Excel file. I can also copy them from OneNote and manually paste them into an Outlook message and they work. However, when I obtain their full address and transfer that to the email through code, the links do not work. The hyperlink address from OneNote starts with "onenote:http" which is not recognized as a link.
If I can do this manually, there must be a way to do this with vba. Are there characters I need to include in the OneNote hyperlink address to make this work? Is there another way to transfer the working link from Excel to Outlook?
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).
Example:
Sheet order A B C
Date of delivery Product
1.9.2013 orange
[Code] ........
Unfortunately simple Pivot table is not able to do this simple list.
I am trying write code withVBA to save separate file in a different folder with Loop.
Write a code with "loop" till it finds empty cell in the column and for every change in number a separate file needs to be saved in specified folder with file name as "10010, 10011,10012... and so on with data copied in the file saved.
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?
