Macro To Keep Copying Cells Until There Is No More Data?
May 26, 2014
I have a spread sheet where you fill in an order, so customer name, item being purchased, quantity and price. I now need a macro that will copy that info over into an invoice.
If the customer is only buying one item, that would be straight forward, as it could just copy that info across, but if the customer buys two items, the macro needs to see that there is more data to copy, then insert a new row on the invoice and copy the details of the other item.
View 2 Replies
ADVERTISEMENT
Oct 10, 2009
I would like to copy a range of cells with a marcro, for example copying range C3:C15 over to D3:D15 and when I run the macro again I want range D3:D15 to copy to E3:E15 and from there to copy E3:E15 to F3:F15. I want to keep that going to the next and the next with the same macro. But it just keeps copying the the original range which I recorded in the macro, but I want it to keep going. If somebody can help me with that, I would sure appreciate hearing from you.
View 8 Replies
View Related
Jun 9, 2006
I browsed through the other posts concerning copying and couldn't find (or maybe I didn't just understand) how to do what I want. I looking for a macro for a comand button that when pressed copies certain cells in the active row to another sheet. I recorded a macro for what I wanted to do ( to make the explenation easier) for one row but even the recorded macro refuses to work.
I'll put the recorded macro here if it helps someone, but as I said even that doen't work
Private Sub Kopiering_Click()
Range("E3").Select
Selection.Copy
Sheets("TOTAL").Select
Range("K3").Select
ActiveSheet.Paste
Sheets("BRL Newbuilding").Select
Range("D3").Select
Application.CutCopyMode = False
Selection.Copy
View 8 Replies
View Related
May 3, 2014
I've got a problem with copying cells from table A to B.
A short insight of the situation:
Table A shows: ITEM_1 = 3, consequently ITEM_1 from table A is copied to table B for 3 times.
I've got a few huge information blocks to handle (up to 5000 items) obviously it's unreasonable to do this manually.
I've added an xlsx file with more detailed description.
Example_WHS.xlsx
View 8 Replies
View Related
Sep 22, 2012
Why does my macro mess up on second line C23 and not places Play Equipment or the amount in the right column it stays on row C22
[URL]....
Code:
Private Sub CommandButton1_Click()
Dim SheetName As String
SheetName = "Estimate1"
SheetName = InputBox("enter the name of a sheet to use", "sheet name", SheetName)
[Code]....
View 2 Replies
View Related
Feb 20, 2013
I've attached a sample workbook in which there are 3 macro-buttons.
The buttons will paste a shape in the active cell. So this means the buttons themselves could be deleted and replaced with a shape.
Since locking and then protecting the cells disables the macros, how can I amend the code to make sure the buttons' cells are protected from the copying and pasting macros? Or, how do I ensure that the macros only work in A1 - E5?
View 4 Replies
View Related
Jun 25, 2009
I need to search column C for the text "X". If an "X" is found, I need to copy the text from the cell directly left of it and paste it into a seperate worksheet into cell B2, then go back to the original worksheet and delete the cell with "X" on it and the cell to the left.
View 9 Replies
View Related
Nov 20, 2008
I have the following macro which copies data from one spreadsheet to another spreadsheet. The 2 files are specifically named in the database so they must have those specific file names in order for the macro to work.
Is there a way to set up this macro so that it automatically copies the data in the file that is active at the time (File1 in the attached code) to File2? File2 will always be the same file name, so that part of the macro is fine as it is. The active spreadsheet will always be the same format (so the Source Cells will work) but it may have a different file name each time.
View 2 Replies
View Related
Jun 11, 2009
What I want to do is for a macro to run and for that macro to copy data and place it in a specific place. For example i want it to take the the 20 from "Village Headquarters (Level 20)". Another time when I run the script the 20 might be a 30. I there a way to do this? To define a place to take the data from within a cell.
My other question is, is there a way to have a macro automatically paste data one row down from the time it was run last?
View 9 Replies
View Related
Jun 17, 2009
I wrote the following code for sorting data in a column. but can't choose first 5% of the whole column and paste it in a separate sheet.
View 9 Replies
View Related
Feb 21, 2009
I am creating an order form and after a user makes an order I want the data the be copied accross to another table (on the same sheet) - like an archive. I would like to create a macro that does the function. However, what I have thought of it that if I record a macro that cuts and pastes e.g. cells A2 and B2 to cells H2 and I2, the first time an order comes through it will be fine however, if the macro is run again it will keep over-writing the existing data in the archive section.
So, is it possible to construct a macro that will copy accross the data but each time it is run won't over-write what is in the cells. Like using an IF statement in the macro (if cells H2 contains data, then move to next avalible cell).
View 9 Replies
View Related
Jan 21, 2013
I have a worksheet which contains certain sections. I want to create a macro which will run if data is input into those sections. This macro should copy whatever was entered into another worksheet automatically as data is being entered. Is there a way for that?
View 8 Replies
View Related
Oct 30, 2012
I have a spreadsheet that allows users to paste set data from a PDF Image (using OCR) straight into Excel and then use the MID function to split the data accordingly.
Unfortunately, the OCR isn’t too intuitive and gets it wrong sometimes.
So to counter this, in another sheet (in the same workbook) I have a manual input section, and a simple macro button that pastes this data into the same fields where the OCR text would be, so that the main sheet works exactly the same way as before.
The problem is, and most likely due to the simplicity of the sheet, if a combination of OCR pasting and manual inputting is used, when I hit the paste button, it over rides the OCR data with blank cells
In the link below I have shown what is currently happening (1, 2, 3), and an example of what I would actually like it to do (4, 5, 6).
Example - Online Spreadsheets - EditGrid
So, in the 2nd scenario, I would like “5” to recognise that the respective cells in “4” already contain data and fill them ‘Grey’. This I have already achieved with basic conditional formatting.
However, I need to take it 1 step further and say that if the parent sheets cell (Auto OCR) contains data, as well as filling cells (in sheet Manual) lock these cells off and prevent the end user from adding data and/ or being copied over to the parent sheet.
Is this possible?
The result then being the parent sheet with both OCR text and copied text from the manual input sheet.
Both sheets are protected anyway and only allow for user input in certain areas, so is it even possible to apply further protection once the sheet is locked already?
View 1 Replies
View Related
Oct 7, 2013
I have line of code that should look bat a given range and copy all visible cells to a new sheet, to start at G8. I get the message that this code doesn't perform this for more than one selection and I'm not sure my code is correct for a rnage that covers two lines either.
VB:
Sheets("Error recommendations").Select
' On Error GoTo Errorhandler ...
View 3 Replies
View Related
Jan 25, 2010
I have a table with over 12,000 rows in it. In one column I have activity and the next a name.
A B
Walk John
Run Harry
Sleep John
*blank* Harry
Eat Percy
*blank* John
*blank* Harry
Reading Tom
So *blank is completey blank and that means Harry also put time to sleeping, and again John and Harry both put time to eating. How can I make the blank cells auto populate with the data from the entry above it?
View 3 Replies
View Related
Jan 8, 2009
12/23/20081/2/20091/5/20091/6/20091/7/2009Stores TotalTMX TotalTotal TMX Expedite
I'm trying to create some code that will do this:
On Day1- Copy info from a different worksheet in the 3 cells for 12/23 (column B)
On Day2 - Copy info from the worksheet in the 3 cells for 1/2 (col C) without deleting the info in Column B from the previous day, etc....
I can't figure out how to get it to paste into a different column each day. It will be continuously moving one column to the right each day.
View 9 Replies
View Related
Jan 27, 2010
I am trying to do is copy two ranges of data from one sheet and past the values to one other sheet. The trick is not having the data overwrite each time the macro is run (I need the data to be pasted to the next blank row). Also, as there are two ranges of data to be copied, I need to define which rows the paste is to start at e.g. copy data from sheet 1, Range("B8:M8") and paste to the next blank row on sheet 2 starting a cell "A3" AND copy data from sheet 1, Range("B9:M9") and paste to the next blank row on sheet 2 starting a cell "A20".
I've managed to get this far (see below) by pasting the data into two separate sheets but cannot work out how to modify the macro so the data can be pasted into a single sheet with conditions outlined above.
-----------------------------------------------------------------------
Sub Stats()
Application.ScreenUpdating = False
Dim NextRow As Range
Set NextRow = Sheets("Past Stats Mar to Aug 2010").Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
Sheets("Current Stats Mar 10 to Aug 10").Range("B8:M8").Copy
NextRow.PasteSpecial (xlValues)
Dim NextRow2 As Range
Set NextRow2 = Sheets("Past Stats Mar to Aug 2010").Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
Sheets("Current Stats Mar 10 to Aug 10").Range("B9:M9").Copy
NextRow2.PasteSpecial (xlValues)
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
-----------------------------------------------------------------------
View 9 Replies
View Related
Jun 4, 2007
I want to write an excel macro for copying the data from one excel sheet to another. The data should be copied as:
- It should sort the data on column E.
- Then it should sort the data on the column N.
- Copy the resulting rows in the Result worksheet.
View 2 Replies
View Related
Jul 31, 2008
http://www.excelforum.com/excel-programming/651452-copy-rows-of-data-into-a-different-format.html
Here is the problem:
I have a list of activities (each activity is one row of data). These will be input manually into Sheet2. (see attached file)
The activities then need to be automatically copied and pasted into Sheet1. However, the data is displayed in a different order than in sheet1. So I need some code which will run through a loop for each row of data and then copy and paste it into sheet1 until it reaches an empty row.
The cell positioning of each data set in sheet1 is equally spaced. VLOOKUP will not work for me here as I do not want any formulae or VBA script in sheet1. I cannot change the format of sheet1 as it is a company form.
View 9 Replies
View Related
Sep 28, 2013
i have selected cells from workbookA (b2:b8) , i want those selected cells gets pasted in workbookB in a transpose way.
like B1 cell in workbookA pasted into A2 cell in workbookB , B3 cell pasted into D2 , B4 cell pasted into B2 and B5 cell pasted into G2......
i have code but not working
Code:
Private Sub CommandButton1_Click()
Dim STRFILENAME As String
Dim O As Workbook
Windows("Copy of Bill Schedule Form1.xlsm").Activate
Sheets("Sheet1").Activate
ActiveSheet.Range("b2:b8").Select
[Code]....
View 3 Replies
View Related
May 26, 2006
In column A, I have unit numbers. Column B is blank. I need help copying the unit number in Column A down in Column B until a new unit number appears in column A. For example, in A6 is unit LS2, A12 is unit LS24, A17 is unit LS34. I would like to be able to copy A6 into B6:B11, A12 into B12:B16, and A17 into B17:B22, etc.
I have the code to copy A6 in to B6 and down, but I don't know how to make it stop at A12. Attached is also an example.
View 4 Replies
View Related
Feb 26, 2009
I'm trying to do is record a macro that will copy data from cells A1/A2 of Test.xls and paste it to cell A1/A2 of Text2.xls. Then when I run the macro again, it will copy B1/B2 of Test.xls to B1/B2 of Test2.xls, and then do that for the remaining cells.
View 9 Replies
View Related
Feb 12, 2013
i am trying to import an excel document and then copy over some columns to another worksheet from the imported worksheet, using a macro. Everything imports and copies over fine except for one column that has cells that comprise of a list of number separated by commas. When these cells get copied over some of the values retain the commas but some of them (specially when there are a lot values separated by comma) gets changed to this format
From: 1,229,124,012,441,230
To: 1.22912E+15
From: 1044,1048,1052,1053
To: 1.0441E+15
From: 1279,1282,1286,1295,1299
To: 1.27913E+19
From:926,929,938
To: 926929938
I am trying to figure out whats happening here! Is there a max number of letters this column can take and if it exceeds that value, excel converts them to this format? Or is the formatting gets changed to something else while copying and pasting?
View 3 Replies
View Related
Apr 12, 2006
I have about a thousand Excel timesheets that all contain 'hours worked' data in a column. Each row contains the area of the project they have worked on and therefore the amount of time they have spent on it. The timesheets also contain the person's name and a w/e date.
I want to sequentially work through each timesheet held locally in a single folder and copy the person's name, w/e date and the hours held in the column into a single new spreadsheet. I need to transpose the data so each amount of time spent on an activity ends up in a column.
I have attempted to record a macro for this but each time I try and run it after the intitial run, it moves to a completely different cell or set of cells to the ones I have directed it and consequently there is no data copied to the new sheet.
I believe this is the first problem..! The second is working through a high number of spreadsheets held in a single locattion but whilst browsing this site I saw the "Excel VBA Loop Through a Folder of Excel Workbooks" page and think this should work fine.
View 9 Replies
View Related
Mar 7, 2007
I have created one-dimensional array and now I try to copy the data from array to some specific Cells in worksheet. But it seems impossible all the time!
Sheet2.Cells(56, 3 + m) = LossLocationInt(m)
NB: LossLocationInt(m) is an array containing value in it. But, this doesn't copy to the Sheet2.Cells(56,3+m). For clearness: the array has type variant
View 4 Replies
View Related
Mar 19, 2013
I am currently working in an excel file for which i want to create a macro. I am using colomn F as the base from which it should be determined whether a row should be copied. In colomn F the user can select three options through data validation. If 'completed' is selected, the whole row should be copied to the second sheet, and each copied row should be pasted to the next empty row. The data on the source sheet starts at row 11 and should be copied to sheet 2, starting from row 11 as well.
View 5 Replies
View Related
Dec 2, 2009
I have a spreadsheet that has been given to me by someone else, and unfortunately it has merged cells in it that are causing some trouble. I need to have a way to unmerge the merged cells, but for the data in the merged cell to then be copied into each cell that it splits into.
I've attached two spreadsheets - unfortunately I can't really attach the real one since it has research data in it, so I've reduced the size of the file and replaced some of the text. The first file shows how the file starts off, and the file named "result" shows how I would like it to be.
My real file contains some 10,000+ lines of data, and the merged cells are of varying sizes, with no consistency to the size, so I'd like a way to automate this instead of copy and pasting into every empty cell after I unmerge them.
View 2 Replies
View Related
Jul 7, 2014
I have productivity data sheet of employees for a month and want to update in a tracker sheet.Every productivity sheet has 5 columns containing numbers.Since its monthly it would be contain 30-31 rows and.I want to copy this data then go to tracker apply filter with respective employee name and paste it there.Is there a way to do this using vba code?
I have prepared vba code to copy paste individual rows from productivity sheet to tracker.But preparing row by row code makes it way too big.Hence i am looking for another solution.
View 3 Replies
View Related
Aug 22, 2009
I am trying to copy two cells from one worksheet to another in the same work book based on the value in Column B of one of the sheets. This is just a building block to a larger script I am going to create.
Below is my attempt but I keep getting an error at Range(Cells(x, 3)).Select.
View 7 Replies
View Related
Feb 11, 2010
I've been trying to use the below code,
Sub combine()
'This will copy data from all sheets of the selected workbooks
'To a sheet named 'Data' in the sheet in which the macro is run from
Dim pasterow As String
mainsheetname = ActiveWorkbook.Name
MsgBox ("Please select spreadsheets to combine")
filestoopen = Application.GetOpenFilename(MultiSelect:=True)...
View 9 Replies
View Related
