Staggered Copy/paste
Apr 23, 2009
I have a coworker who is looking for a way to copy large columnar datasets and paste them into a different worksheet while inserting spaces. In other words, he has say a set of 100 values in column A and wants to paste them into another column in worksheet 2 in blocks of three. So paste values 1-3, then skip 5 rows, then paste values 4-6, skip 5 rows, etc. Without constantly jumping back and forth between worksheets and copy/pasting 3 values at a time.
Is there a function or simple macro for this?
View 9 Replies
ADVERTISEMENT
Jul 22, 2009
Attachment 49027I have an excel data dump of the organizational hierarchy out of ERP system that I need to convert to a flat file pivot table ready format.
The catch is that my lowest level always has to be a department meaning that a dept should be a last column however in the ERP not all the departments are mapped to the lowest level layer i.e. I might have Layer 1, Layer 2 dept or Layer 1 Layer 2 Layer 3 and then dept mapped to 3. Attached is the sample of the file.
View 13 Replies
View Related
Jun 17, 2014
set a formula to auto calculate the staggered rent for the month. When I change the date, it will tell me for this month I should charge according to the rates for the year.
Rent for the month
Start Date Year 1 Year 2 Year 3 01/07/14 Explanation
01/08/13 10 20 30 10 < 1 yr = 10
01/07/13 40 50 60 50 enter 2nd yr = 50
16/07/13 70 80 90 76.29 (15/31*70)+(16/31*80)
16/07/13 10 20 30 15.16 (15/31*10)+(16/31*20)
formula or vba using Excel 2003.
View 2 Replies
View Related
Mar 29, 2009
I'm using 2003.
1. Copy cells B5 to V-First blank row in Strength Tests worksheet
2. Paste cells into Racks worksheet in cell C5
3. Change font size to 6
4. Sort by Column T descending then by Column C ascending
5. Copy one row (A5-W5 (1Rx23C)) from Racks worksheet
6. Paste row into M1 worksheet in cell D4
7. Print M1
8. Drop down one row on the Racks worksheet
9. Repeat steps 5-8 until there's a blank row.
View 9 Replies
View Related
Feb 3, 2012
This macro works fine on my machine but not with other users:
This should copy/paste certain cells then paste 3 sheets into a new work book.
ON other computers it seems to paste in a picture? works OK for me?
Sub ValidationTests()
'
' ValidationTests Macro
' Macro recorded 21/12/2011 by '
'
Sheets("Score Sheet").Select
Range("A8:M18").Select
Range("H18").Activate
Selection.Copy
[Code] ..........
View 1 Replies
View Related
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
Jun 20, 2013
Attached is my code, pay attention to the bold part. I want the sourceSheet to be copied as a sheet and pasted in the targetSheet (the Sheet2 of "NewBook") but I want it pasted asvalues. Here is the specific part which needs to be looked at...and below is the full code.
VB:
Set sourceBook = Application.Workbooks.Open(sourceFilename)
Set sourceSheet = sourceBook.Sheets("Current")
Set targetSheet = NewBook.Sheets("Sheet2")
[Code].....
View 9 Replies
View Related
Sep 6, 2012
copy/paste Every Sheet Single ( P Column ) and Paste to Notepad and take P1 As file name for note pad.
View 1 Replies
View Related
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.
.....is equal to "Herr" copy row to "Herr" spreadsheet.
....is equal to "McCormick" copy row to "McCormick" spreadsheet.
and so on.
View 2 Replies
View Related
Nov 25, 2012
HTML Code:
Range Apple
A B C D E
1 2 2 4 3
2 1 3 5
3 4 6 9
4 5 3 1 3
5 7 7 7 6
Range Pear
A B C D E
4 1 3 5
5 1 1 1
6 2
7 2 2
8 5 7
Range Apple
A B C D E
1 2 2 4 3
2 1 3 5
3 4 6 9
4 5 3 1 3
5 7 7 7 6
View 2 Replies
View Related
Feb 4, 2014
How would you prevent the copy/paste of cells that have comments?
Also, how would you allow cells with comments to be copied and pasted without pasting the comments?
I also have an aside question about the forum advanced search. When searching for multiple search words, how would you type the search to include all words, for example, "prevent" & "paste" & "comments".
View 7 Replies
View Related
Jan 22, 2007
I want to copy and paste from one sheet to another based on column a using a macro copy button.
E.g. if column a value = apple then copy that row into the apple sheet.
View 9 Replies
View Related
Jul 10, 2006
I want this macro to find in this case "406" in column A which is at the very end of the last block of 160 rows of information.
I then want it to move up 159 rows and copy 160 rows of information underneath the last block of information.
i.e.go to A5280 , then go to a5121, copy rows 5121:5280 to 5281.
It falls over on the very last line of code I can see A5281 selected but it won't paste....
View 9 Replies
View Related
Sep 25, 2009
The following sub will look in the file ("FY09 SOF"), in column "A", search for the strings that begin with "2109", "3009", or ends in "-1", and copy the entire row. It will then paste these in the file ("FY09 PR Log Blank").
I have found that in the file ("FY09 SOF"), if things are filtered in any row, it will not copy those necessary items.
The data filter is on row 13 of each sheet. Is there a way of fixing this? (i.e. having the macros select "all" on the filter before copying the sheet? There are 60 sheets so a macros will be necessary.
Sub get_data()
Dim wb As Workbook, wbDest As Workbook
Dim ws As Worksheet, wsDest As Worksheet
Dim lngCalc As Long
Dim FoundCells As Range
Dim FoundCell As Range
Set wb = Workbooks("FY09 SOF")
Set wbDest = Workbooks("FY09 PR Log Blank")
Set wsDest = wbDest.Worksheets("Paste all here, then sort")
With Application
.ScreenUpdating = False
lngCalc = .Calculation
.Calculation = xlCalculationManual
End With
For Each ws In wb.Worksheets.............................
View 9 Replies
View Related
Jan 1, 2013
I've received an Excel file with the below macro. However, I've made changes in the original file and therefore, the macro isn't working anymore except if I copy paste special the value instead of simply copying it. I would need to change the macro accordingly.
The macro is the following :
Sub CCtosheetwithoutformat()
'
' CCtosheetwithoutformat Macro
[Code]....
View 2 Replies
View Related
Jul 23, 2009
I currently have a macro that outputs data to a bulletin-board type table in my worksheet. At the moment, what happens is that the macro copies the entire table down one row using copy/paste, then prints a new line of data to the top row of the table.
This works great because it is very fast and because I only have to format each line once--the formats just get copied down every time a new line of data is added.
My problem is that while this program is running, I am unable to use copy and paste in windows, because the copy/pasting from the macro overwrites the windows clipboard.
Is there a way for me to "copy" formats from 1 range to another range of equal size without actually using copy/paste? I know this can be done with values (eg. range("B2:B4").value = range("A2:A4").value), but I can't get it to work with any sort of formatting.
View 9 Replies
View Related
Jun 26, 2009
Sheet linked from external file, new data coming daily. How to copy Values of cells from B4 till B-empty to C column? The attached file has a properly displayed data.
View 3 Replies
View Related
Oct 23, 2009
what is wrong in this code, it does not paste.
HTML Range("A65536").End(xlUp).Offset(0, 0).Select
ActiveCell.Offset(-1, 7).Select
ActiveCell.Copy Destination:=ActiveCell.FormulaR1C1 = "=(R[1])"
I am trying to copy from the selected cell and paste one cell below(next row) in same column.
View 2 Replies
View Related
Aug 19, 2008
I have a list of invoice #'s on a sheet named "Temp Sheet".
I have a VBA macro that created a new tab for each entry and named it the invoice #. So basically the vba code created a new tab ( based on the number of invoice #'s on my list ), and named each tab an invoice number. So if I had a list of 10 invoice #'s, named S1-S10, the vba code created 10 tabs, named S-1, S-2,. S-3.....
Now to my question.
I have a template sheet I want to copy from ( "Template" ), and select any sheet that starts with "S", and copy/paste this template to.
View 9 Replies
View Related
May 31, 2014
I need a macro code that when the button1 is pressed, it will ask for a range or it will capture a range, when the range is identified, the range value will be copied and pasted to the next available line/row when I click button2.
In this sample I select the range columnA and columnB
Column1
Column2
row1
Apple
Ball
[Code] .......
When I press the button2 to copy/paste the table should be:
Column1
Column2
row1
Apple
Ball
[Code] .......
It is like Duplicating a selected range.
View 1 Replies
View Related
Mar 24, 2014
I have a macro recording some production data. Everything works fine except the last action. I want to copy all data from active sheet (row 31 until last row in column M), then open another sheet (database) and paste data under the last row. Everything works, but the data always paste under the last cell in column "A" and rewrite data in other columns under this row (cells in column "A" are not always having data in them). That makes sense, because I'm using the "A1000000" as Range in my code. But I have tried everything to make it work, based on the last cell/row used with no success. Ideally, I would like to find the last used row within columns A:M and paste the data underneath. Some of the columns have date or name (not sure if that makes any difference). The copy and opening+closing works fine,
Here is the code:
Application.ScreenUpdating = False
Application.CopyObjectsWithCells = False
Dim Rowcount As Integer
Dim DataRowCount As Integer
[code]....
View 3 Replies
View Related
Nov 28, 2013
I'm trying to get a loop to copy a cell(B?) value if a cell(F?) is blank, and then paste that copied value(from B?) into a different cell(A?+) if it is not.
I know the first run of the loop will always result in the copy happening. However, when it comes to the else paste it fails.
Code:
Range("A:A").Insert Shift:=xlToRight
Rows("1:10").Delete Shift:=xlUp
Range("A1").Select
Do While IsEmpty(ActiveCell.Offset(0, 1)) = False
If ActiveCell.Offset(0, 5).Value = "" Then
ActiveCell.Offset(0, 1).Copy
ActiveCell.Offset(1, 0).Select
Else
ActiveSheets.Paste
ActiveCell.Offset(1, 0).Select
End If
Loop
View 1 Replies
View Related
Aug 15, 2007
I am not too sure if what I want to do possible with VBA.
In cell B14 I want to copy the data starting from cell B43 and downward. That is to say if cell B43, B44 contain data and if B45 is blank only B43 and B44 will be copied into cell B14 sequentially. It is like entering data in cell B14 and then press Alt Enter to allow entering data on another row for data from B44.
It will be wonderful if this can be done as I have more than 100 worksheets to go through for this repetitive action.
View 9 Replies
View Related
Dec 19, 2008
I have a workbook with 2 sheets.
in "sheet 1" I have "column A" with "part nos" and "Column B" with OLD "prices"
in "sheet 2" I have "column A" with "part nos" and "Column B" with NEW "prices"
I want a macro that would do the following:
for each cell in "column A" of "sheet 1" the macro will check "column A" of "sheet 2", if the values are equal, it should copy the value of the corresponding cell in "column B" to "column B" of "sheet 1"
I have a list of items (3000) with prices and I received a new list (14000) with newer prices. the problem is I need the prices only for the items in my initial list.
View 9 Replies
View Related
Jul 27, 2006
Im having problems copy and pasting. I've got the following code
Private Sub CmdImport_Click()
Dim ImportName
'Dim CurrentPO
'On Error Resume Next
'CommonDialog.Filter = "Excel Files|*.xls;"
CommonDialogImport.DialogTitle = "Please Select the file to import"
CommonDialogImport.InitDir = ("\CcnofficeshareddocsNORGlazeNORGLAZE.BUYINGNORGLAZE.Purchase Orders") 'CURRENTMONTHTEST")
'CommonDialog.InitDir = ("e:VBA PO FormCURRENTMONTHTEST")
CommonDialogImport.ShowOpen......................................
View 2 Replies
View Related
Nov 13, 2006
Thats the code of the button which I made (the file is atached),
Private Sub CommandButton1_Click()
Dim wb As Workbook
ChDrive "C"
ChDir "C:Documents and SettingsszmMy Documentszadanie bojowe"
FName = Application. GetOpenFilename("*,*.xls", , "Please select file to open")
If FName = False Then Exit Sub
For Each wb In Application.Workbooks
If wb.Path & "" & wb. Name = FName Then
MsgBox "File " & wb.Name & " is already open"
Exit For
End If
Next
If wb Is Nothing Then....................
and question:
I want to add a formula to this one, to import data to the next row (skip previous) if the row where the data should be imported is not empty. I can make another and another button to do that (more than 300 ?) but I think its stupid. U can see how it look like in the file which is attached.
View 2 Replies
View Related
Feb 13, 2007
I have a list of 2000 part numbers in column A, some with information (known partnumbers) on the same row under columns: D, E, F, G and H.
Some of these part numbers are repeated down the list without these informations (unknown partnumbers).
Can i write a formula: to copy informations from known partnumbers to unknown partnumbers.
Therefore steps are as follow:
1-search for first (known) p/n. these are p/n with information under D, E, F, G and H column.
2- search for the same p/n, if same p/n is known, go to next p/n, if not (so these are unknown p/n)
3- copy and paste information from column cells: D, E, F, G and H from known to unknown p/n.
4-repeat till list is complete
5- go to next known p/n on the list from top and search for same (unknown) p/n.
6- till list is complete.
View 6 Replies
View Related
Mar 23, 2007
I have created a macros to copy cells on worksheet 'Workings' and paste these onto another worksheet 'CAP'. What I need is to edit this so instead of pasting the information into cells specific cells I would like to paste these into the next available row with no data in.
Sub Cap()
Range("H8:AP8").Select
Selection.Copy
Sheets("CAP").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Workings").Select
End Sub
View 3 Replies
View Related
May 21, 2014
I currently have created a database (sheet two) with information including ID # (VH-XXXXX) in Column A and all the necessary information related to that unique Item. What I would like to do is search based on the ID number, have it filter, then copy and paste the row into another worksheet. Is this possible? I am relatively new to VBA and have written codes for copy and pasting rows, but never with an autofulter.
View 3 Replies
View Related
Jul 22, 2014
I am currently working on a bid sheet for my company; the accounting department has new software and they need me to alter my spreadsheet to fit a format that is suitable to import into their system.
I will try to explain this as simple as possible (I have attached an example spreadsheet as well)
Sheet1: Estimate
Sheet2: Cost Code Import Sheet
I need to take the information from Sheet1 and have it converted to fit sheet2 and there are a few issues I'm having:
#1 I need the line items in column B on Sheet1 (B2,B3,B4) referenced on Sheet2 but every 4 rows (E2,E6,E10)
#2 Now that I have 3 blank rows between my line items, I need to reference the cost amounts in cells F2,G2,H2 (sheet1) onto sheet 2 (D3,D4,D5)
so far, I have been changing the reference in every cell, and I have hundreds of line items... I do not think I will maintain what little sanity I have left if I continue doing it this way
View 3 Replies
View Related
