Copy / Paste Every N Rows?
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
ADVERTISEMENT
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
Apr 14, 2014
I am new to macro and just trying to learn. I have a spreadsheet with 20000 rows and 8 columns. I am trying to identify equal rows based on the values of columns C, D, E and F. then I need to separate equal bunches with a blank row. Then I need to copy the ID number from the first cell of column B of each bunch and paste it for the rest of the rows in that bunch. I have written the following code but this does not put the ID of the first cell in a bunch for the rest of the rows in that bunch.
[Code] .........
View 10 Replies
View Related
Jan 28, 2014
I need to build a macro which copies 3 rows every day and pastes the row data into an identical sheet. The three rows will have column "D" as =today(). As the days progress the three rows will change accordingly ( tag to the today's date)
e.g. 28/1/2014
28/1/2014
28/1/2014
I need the macro to recognize the date when pressed and copy the corresponding rows of data and paste them into an identical sheet with the same date. The second sheet is an archive sheet. The date will tick over as per the calendar.
View 9 Replies
View Related
Aug 15, 2009
col1 col2 col3
row1 A 1 a1
row2 data data data
row3 data data data
row4 data data data
row5 A 2 a1
row6 A 3 a1
row7 B 1 a1
I'm trying to do is set up a VBA code that will take lines lines 2-4, copy the rows and then paste them in new rows every other row for the rest of the document, so that it appears as...
col1 col2 col3
row1 A 1 a1
row2 data data data
row3 data data data
row4 data data data
row5 A 2 a1
row6 data data data
row7 data data data..............................
View 4 Replies
View Related
Nov 18, 2009
I need to copy and paste 4 rows individually under each of the 500 rows.
View 12 Replies
View Related
Jun 30, 2008
I'm currently using the below code to add the formula in each cell. Depending on the number of rows.
It's very slow (Range("aa2").Value returns 1060) which means 1k rows. I was thinking of doing copy and paste to speed up. But I do not want to fill the whole column U (Column 13) with the formula. Is there anyway to set the max to copy to?
like r2:rX
where X is Range("aa2").Value?
Private Sub CommandButton1_Click()
Dim nDb As Integer
intRow = 2
Range("aa2").Value = "=COUNTA(A:A)-1"
nDb = Range("aa2").Value
View 9 Replies
View Related
Apr 10, 2014
I want do copy a range and paste to a specific range. So if the copy selection is larger I don't want it to paste the rows that don't fit.
With this code it copy a range and paste even outside my given paste range.
VB:
Range("F33:H60").Select
Selection.Copy
Sheets("7211").Select
Range("F33:H40").Select
ActiveSheet.Paste
View 4 Replies
View Related
Mar 16, 2012
I have some code that uses a form so you can choose the folder that contains all the spreadsheets that I need to copy. My issue is I have about 20 spreadsheets, some have the tab labled M3 and others Sheet1. Next is each spreadsheet has a different amount of rows in them. My data will always start in cells B4:N4 but may 20, 100, 1000 rows long.
My code works but only copies the first four rows of the last sheet open.
Code:
Private Sub cmdbtnProcess_Click()
Check_Path
End Sub
Sub Check_Path()
If txtboxPath.Value = Empty Or txtboxFile.Value = Empty Then
[Code]...
View 2 Replies
View Related
Apr 30, 2013
I have Data in Row 1 beginning in Cell E1 then K1 (Every 6th Cell) About 300 Records with 5 Blanks between each one of them...
How would I get that Data In A5 Down Without the Blanks?
View 9 Replies
View Related
Apr 19, 2006
I have been trying to set up this macro to copy and paste rows where a tick box is checked. The macro needs to transfer the row A:H over to sheet 2 then insent the data acording to property title either A, B, C etc. into formated tables which then feed into a bar and pie chart. I have tried posting this question up in sections hoping that i could get the bits of code i need and then work out how to do the macro but I have had no luck since my VBA skills are poor. I have attached the file
Private Sub CommandButton1_Click()
Dim lRow As Long, lRow1 As Long, lRow2 As Long
Dim Target As Range
Dim vTemp As Variant
Dim WS2 As Worksheet
Set WS2 = Sheets("Sheet2")
lRow = WS2.UsedRange.Row + WS2.UsedRange.Rows.Count
For Each Target In Range("I1", Cells(Rows.Count, "I").End(xlUp).Address).SpecialCells(xlCellTypeConstants)
vTemp = Target.Value
If VarType(vTemp) = vbBoolean Then
If vTemp = True Then
vTemp = "*"
On Error Resume Next
vTemp = WorksheetFunction.Match(" Total*", Range("A" & Target.Row, "A" & Rows.Count), 0).....................
View 2 Replies
View Related
Jan 28, 2008
In column H I have dates from 1/1/2008 to 12/31/2013. What I would like to do is transfer that data to column J, but alternate it every other row and then insert the word Date into the blank cells. For instance J1 would read "Date" and then J2 would read 1/1/2008 J3 would be Date and J4 would be 1/2/2008. I have a large database and I tried to use array formulas and they slow it down to a crawl so I was working with DSUM, but I don't feel like wasting time with adding in Date manually.
View 8 Replies
View Related
Jun 17, 2008
I am trying to save me a lot of copy and pasting. I have cells A1:A100+ with customer names listed out. I am trying to create a chart on a different page but on this particular chart, i need those customer names at the top of the columns going across the page instead of the rows.
View 3 Replies
View Related
Apr 19, 2013
I have tried to write some VBA to copy and paste rows based on the condition in cells A:A in sheet one. So far I have managed to put something together which will paste the first row and insert a new row below but keeps on pasting the same row. But not much else! Not sure I have tried to make it more difficult than what is it.
I would like it to copy a row based on a certain condition ("WRW") in A:A sheet one and paste it to sheet2.
VB:
Function DoOne(RowIndex As Integer) As Boolean
Dim Key
Dim Target
[Code].....
View 3 Replies
View Related
Jul 18, 2013
I have a code which is copy/pasting the selection specified number of times
VB:
Sub CopyNtimes()
Dim i
For i = 1 To Application.InputBox("How many times do you want to copy the selection?", "", 1, Type:=1)
With Selection
.Copy .Offset(i * .Rows.Count)
End With
Next
End Sub
How to modify this code so that it can paste the selection specified number of times leaving specified number or rows
For Example:
Selection is A1:Z10
need to copy paste 2 times...
Rows to leave: 3
So it should paste in the range A14:Z24 and then A28:Z38
View 5 Replies
View Related
Apr 8, 2014
I am trying linking it and this what I am trying to do , need a formula for it.
E.g.
Sheet 1 cell J3= Sheet 2 cell K3
Sheet 1 cell J4= Sheet 2 cell K9
Sheet 1 cell J5= Sheet 2 cell K15
and so on.
View 3 Replies
View Related
Mar 20, 2009
Basically on the file below whenever there is data in column "type" I want excel to copy the "name" and "account" next to it. The whole file has about 80,000 rows. Spacing is not always one blank row between accounts. I am using excel 2007.
View 5 Replies
View Related
Aug 4, 2014
I would like a macro which will copy range $A$1:$Z$1 and paste it as values into AA1:ZZ1.
Easy enough on the first run; but on the second, copy the same range $A$1:$Z$1 and paste it as values into AA2:ZZ2
third run into AA3:ZZ3, etc.
View 2 Replies
View Related
Aug 7, 2014
In sheet Model RC BOM I am trying to copy all the rows under Level 1 (row 3), including level 1, until it reaches the next Level 1 (row 537) (not including row 537), and paste those cells in next tab (BIW) starting at row 2. The next operation is to copy all rows under Level 1 (row 537), including level 1, until it reaches the next Level (row 827), not including row 537, and paste those cells in the next tab (Chassis) starting at row 2.
The challenge is that I cannot use the row numbering in sheet Model RC BOM as a reference for coding because the content will change every week.
View 5 Replies
View Related
Dec 6, 2013
The number of rows in my spreadsheet will change. I am creating a Macro to insert a column and enter a formula in the second cell of the new column. I need to copy that formula down through that column to the last row, but don't know how many rows there might be that day.
View 2 Replies
View Related
Mar 16, 2014
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
[Code]...
View 1 Replies
View Related
Apr 28, 2014
What I have are dates in Column B with a lot of blank rows in between. For example: cell B2 has a date in it and then the next date would be on cell B54. I need a macro to copy cell B2 until it finds a new date (which is in cell B54) then copy the date in cell B54 until the next date again.. so on..
View 9 Replies
View Related
Apr 22, 2005
How do I create a macro that takes the 3 digits values in cells Q82:AJ86 and place them is column L, set code to start placing result in L38?
example
Q82=610
R82=611
S82=612
T82=613
Results
L38=610
L39=611
L40=612
L41=613
View 9 Replies
View Related
Jul 11, 2006
I needed to find "406" which is in A5280 copy the previous 160 rows X 3 columns to A5281.
Because of other factors involved I now realise it would be better to have the macro do the following.
1. Find "04/06" (in A5123)
2. Copy from 2 rows above this cell (A5121) down to (C5280)
3. Paste into A5281
Sub ACopy3()
Const intRowsToCopy As Integer = 160
Dim rngFound As Range
Dim Ro
Dim Col
Application. ScreenUpdating = False
Set rngFound = Columns("A:A").Find(What:="406", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Range(rngFound.Offset((intRowsToCopy - 1) * (-1), 2), rngFound).Copy rngFound.Offset(1, 0)
Application.ScreenUpdating = True
End Sub
View 9 Replies
View Related
Sep 4, 2006
I have workbook "Sheet1" that has many rows and columns with values. Could I have code to find the cells value "plant2300" in column A and copy all the rows in column A that has the cell value "2300" into workbook Sheet2 (also copy with headers from Sheet1 to Sheet2 row 1).
View 3 Replies
View Related
Sep 15, 2006
I am pulling from odbc into an excel sheet
Thier is one column with our Sales Order Numbers.
I would like to have a search box or box pop up asking a user to enter the Sales order number they need.
Once they do that it will search thru the list and copy the rows with that Sales Order Number, then paste them to another sheet.
I need help on setting the searching part up and selecting the range to copy....I think I can take it from thier after that.
View 9 Replies
View Related
Sep 16, 2006
I dont know if a vb code is at all possible to copy a formula in a cell in row 2 straight down that column until the last unhidden row in which there is at least a cell populated with values from the 256 cells in that last unhidden row. Then in that column, I want the cells that are filled with formulas to be converted to values.
All these unhidden rows are autofiltered with certain criteria. The left and right columns to the formulated cells does not necessarily filled with values as they can be blank. Row 1 has header names, and a formula can be any cell in row 2 at any one time and that is exactly the problem i faced so that to record a macro seems totally useless for me because the cell in row2 where I will begin with a formula varies every time.
Currently I go a long way by taking these steps:
Hghtlighting row1
Data/Filter/ Autofilter/
Select the header cell and click on drop-down filter
Select (Custom...) with criteria
Input formula on the cell in row2
Copy the formula
Press Shift+ArrowDown keys until the last unhidden row
Press Enter
Undo Autofilter
Select the column
Copy and Paste as values
Autofilter row1 again
Select the header cell, (Custom..) equal "#N/A" and "zero"
Continue next turn of formula..copy to last unhidden row, paste as values....
Me and other staff in my department are doing these long-winding steps endless of times, day in day out, until we are drained out and called quit. If there is a code that can do this, this is going to the greatest news ever for these people for that kind of work.
View 5 Replies
View Related
Oct 27, 2013
I need to write a macro where i need to copy set of rows from few columns of an excel sheet to another set of columns in same sheet . My excel looks something like this...
Product
F1020
F1023
F1025
F1120
F1123
F1125
[code].....
Now when i filter this table for Product PR01 only rows 1,3,4 will be visible while the other rows remains hidden
I WANT TO COPY ROWS COMING UNDER COLUMNS
F1120
F1123
F1125
TO
F1020
F1023
F1025
when i use the code
Selection.SpecialCells(xlCellTypeVisible).Copy
i get to select ones those are visible but i am not sure how i can PASTE them to rows visible under column f1020 to f1025
Tried this in a frantic effort
Selection.SpecialCells(xlCellTypeVisible).PasteSpecial xlValues
But got an error for " multiple selection"
View 1 Replies
View Related
Jan 31, 2014
I'm trying to get a VBA code that will allow me to copy and paste rows based on a date range, for example
Column A = (Blanc)
Column B = Line Number
Column C = Date
[code]....
i need to have a pop up box that i can input the start date and then another pop up for the end date (mm/dd/yyyy), then using these dates copy the whole row which are within the date range to another sheet called Summary.
View 7 Replies
View Related
Mar 19, 2014
I have a workbook with 4 sheets relating to areas and a summary sheet and introduction.
On the introduction sheet I have a dropdown which lists all the available products my company make.
What I want is to be able to select the product from the dropdown and then click a button which will then copy and paste the headers and any rows which relate to the selected product from each of the area sheets and paste it all onto the summary sheet.
View 14 Replies
View Related
