I’m looking for a code which copies information from one worksheet to another and duplicates each row 3 times.
So for example on sheet 1 I’ve got data in C12-H12, C13-H13, C14-H14 etc.
The macro should copy the information C12-H12 to row 1,2 and 3 on the second sheet and than information from C13-H13 to row 4, 5 and 6 etc. Hope you understand what I’m trying to do.
I started with the following ...
Normally if you want to create a copy of a sheet what we do is :
Right click on the tab sheet > click on Move or copy > select (move to end) > check box Create a copy > click ok.
Now its very tedious if i have to copy the sheet lets say 50 times using this method. Perhaps there is another shorter way that will instantly create a copy of the sheet in the same workbook?
I need a macro that will copy a row to "n" number of identical rows below it, depending on user input. I am not skilled at VBA but I cobbled together some code I found online (see below). Unfortunately, it does not work properly. The input box pops up, but it only copies one new row regardless of what number you enter.
Sub InsertCopyRow2()
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'Default for 1 row, type 1 is number
If vRows = False Then Exit Sub
End If
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow
End Sub
I'm trying to modify this line:
Range("A2").AutoFill Destination:=Range("A2:A10"), Type:=xlFillSeries
I trying to copy down a formula a set number of times based on a cell value or a declared value.
Is there a way in excel to have it automatically copy a cell 4 times and append _01, _02, etc..
ABCD0001
ABCD0002
and automatically make it look like this:
ABCD0001_01
ABCD0001_02
ABCD0001_03
ABCD0001_04
ABCD0002_01
ABCD0002_02
ABCD0002_03
ABCD0002_04
I'm trying to create an "export" feature where certain data can be copied out to a new workbook. I want to copy out the following from the current workbook to a new one: Sheets("1TR").Range("C33:M999") to range("A1") in the new workbook
and Sheets("1PL").Range("K33:K999") to range ("L1") in the new workbook.
The problem I'm having is that it is not just one copy/paste exercise, but two, and I don't know how to reference the new workbook as it doesn't have a filename.
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.
I have some groups of data. Each group are 5 cells: ........
What I want is to make a formula to sum the five numbers of each group, then: =sum(a1:a5). but, how could I copy the formula to make Excel understand that I want the numbers from a6 to a 10, and from a11 to a16? I try to make it with left click in the square down at right, but it just add one value to each cell:
It makes this: ..................
I have a range A3:E70 where A3:E69 have the same formatting and the text in A70:E70 is bold. I want to copy-paste the entire range a number of times with a for ... next-loop. Actually, it's kind of a template system. I make the template A3:E70 and uses it a number of times.
View 2 Replies View RelatedI would like to copy the existing sheet "Template" 17 times and each of the copied sheet should be names according to the order in the array:
"XX","TT","YY","WE","TG","KJ","IO","RT","EF","VU","GF","DW","QA","EZ","QU","OF","BB"
how this is done in VBA?
I have created a userform with 4 textboxes and "ok" button. whenever I press OK I want it to copy TB1(text),TB2(number) and TB3(number) in the first empty row a number of times based on what is in TB4(number). however, each time it has to add 1 to TB2 and whenever (TB2+1) exceeds 5 then 1 is added to TB3 and TB2 start over from 1 again.
View 3 Replies View RelatedI have an excel sheet, Data's range is B2:B5.
The data is listed as follows: Tom, Pete, Steve, Lisa.
I need a VB code to copy and paste the names (range) to J2:J17. By having the names pasted four times each.
The result would look like this:
Tom
Tom
Tom
Tom
Pete
Pete
Pete
Pete
Etc.
I have a task which I am sure can be done quite simply. I have a list of names in column A and a number in column B. I want a Macro to copy the name in column c, d, e etc for the number in column b.
View 9 Replies View RelatedI am not good at writing codes and so would really appreciate if someone could help me.
I have a range suppose A1:D50 which i want to copy it certain no.of times, say around 50 times below the original range or in another sheet.
Can a code be created where I have a useform , where I will be entering the number for eg.50, which will copy the range 50 times below the original range or in new sheet.
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 RelatedI have the price of S&P 500 in the L2 until L145 column.
And I would like to past each cell (L2, then L3, then L4....until L145) 220 times in column F.
As follow
L :
1780
1715
1680
.........(144 value)
TO column F
F:
1780
1780
1780
.........(220times)
1715
1715
1715
.........(220times)
1680
1680
1680
........(220times)
I have the following data in one "Project_list" tab:
Project
AAAA
BBBB
CCCC
DDDD
I want to copy this data into another tab "Transpose" in this form:
AAAA
AAAA
AAAA
AAAA
BBBB
BBBB
BBBB
BBBB
[code].....
So basically copying 4 times every row... as simple as that The constraint: I have about 1500+ projects in the project list, but this list can change so i need a statement that copy values 4 times till source tab has empty values. I would like to have in the second column the following serie for each project.
AAAA Q12014
AAAA Q22014
AAAA Q32014
AAAA Q42014
Note- i want to do this in VB not though formulas as i am doing other derivations in macro.
I'm trying to transpose multiple values in Excel, but I'd also like to repeat row values for columns A through E. The attached file "Raw Data" worksheet shows what I start with, and the "End Result" worksheet shows what I'd like the end result to be.
View 3 Replies View RelatedI have a range of cells that is 10 rows high. I want to copy and paste this range 11 times while identifying each of the 11 copies of the range, such as; 02,03,04...12.
Please see attachment for an example of what I am trying to do.
Does anyone have an idea of how this might be done in VB?
Is it possible to have the user inputing a value in cell $G$8 and have the range B11 to G11 to be copied x time depending the value of Cell $G$8 down. ie: Value is 5, it will be copied on 5 rows under B11 to G11??
View 3 Replies View RelatedJust curios if this is the most efficient way to copy a workbook x number of times.
I tried copying 77 workbooks and not sure exactly how long it took, but about 2 mintues. The original workbook is 300 KB.
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
[code].....
I enter a part number in "D1". The number of components required for that part number is returned in "G1" based on a VLookup. I would like a macro that will copy and paste the part number I enter in "D1" in the first empty cell in column "D" and will paste it once for each component (G1) required. For example, if the number of components required is 4, and the first empty cell in column "D" is "D10", I would like to paste the part number in cells "D10", "D11", "D12", and "D13".
View 2 Replies View RelatedCode copies the first two columns of a many column table and pastes them at a certain interval (14 columns) to make transfer to a report easy. The problem is that the worksheets each have a different number of columns, but none more than 56 columns. The macro works wonders on the first sheet, but thereafter does not work at all. The first sheet has 27 columns, the second sheet only has 4 columns and the one after has 38 or something.
Option Explicit
Public iMaleGroup As Integer
Public iFemaleGroup As Integer
Public iMaleAnimal As Integer
Public iFemaleAnimal As Integer
Public iMaleGroup1 As Integer
Public iFemaleGroup1 As Integer
Public StudyTitle As String
Public SmallAnimal As Boolean
Sub CommandModule()
Dim Wrkst As Worksheet
Dim wsName As String
Dim wsSubject As String
Dim wsNumber As String
Dim rSummaryHeader As Range
Dim MergedHeader
Dim NextMergedHeader
Dim HeaderRange
Dim AddHeader
Dim TableHeader
Dim SumTableHeader
Dim PasteRange
Dim x As Integer
Dim n As Integer
Dim z As Integer
Dim i As Integer
Application.Calculation = xlCalculationManual
Application. ScreenUpdating = False ............................
Need Excel Macro or Formula for finding duplicated cells more than 5 times and copy them into new sheet.
For Example;
apple
orange
apple
banana
apricot
[Code]...
OUTPUT in new Sheet will be
apple 5
orange 5
In my spreadsheet, I have approx. 300 'blocks' of data, one for each 'vendor'. I am tracking 5 stats for each vendor for 12 months. Each 'block' has 5 formulas WITH conditional formatting (only one condition used) for each month, so each 'block' has 60 individual cells with conditional formatting. Excel throws an error when I try to copy the block of formulas and formats down the sheet a few hundred times. I have figured out it isn't the formulas that is the problem. It must be some kind of limit Excel 2003 has for how many cells in a workbook can have conditional formatting.
View 9 Replies View RelatedI am encountering a problem with worksheets.copy that occurs after the copy method is called a certain amount of times. My issue is similar (exactly the same?) to what is described in these threads:
- Worksheet Copy: Method 'Copy' of Object 'Worksheet' Failed
- Copy Method of Worksheet Class Failed in Loop Code
In my case, after running this code 40 times, it fails.
If loc_after Is Nothing Then
.COPY after:=Worksheets(sht_name_orig) 'throws error here after being called 40 times
Else
.COPY after:=loc_after
End If
I would like this code to be able to run more than 40 times. I have read Dave's suggested solution regarding creating a worksheet template:
http://www.ozgrid.com/Excel/excel-wo...-templates.htm
However, in my situation the client has specifically asked that the report come as a single stand alone excel file with no add ins. In addition, I am copying several different worksheets, not just one single template that is being used over and over.
Say I have (cell A1) 10 salespeople and they each sell a (A2) $100/month membership, so I have (A3) $1,000 monthly revenue.
(A4) is an assumption about how many months the average person will stay a member.
So if A4 = 6, (6 month membership assumption) how would I get Excel to output $1,000 (the result from A3) into cells B3 through B8
but then for instance if A4 = 3 (3 month membership assumption) it would only output the the $1,000 result from A3 into cells B3 through B5
So like A1 through A4 of...
10
$100
$1,000
6
would output $1,000 into B3 through B8
but
10
$100
$1,000
3
would output $1,000 only to B3 to B5
In my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.
Here’s a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible
1:45:
If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
I'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:
E3 provides the start time of 4:00
H3 provides an end time of 15:30
If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.