Copy / Paste / Transpose Loop Between Sheets

Jun 16, 2014

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.

Sub Macro5()
'' Macro5 Macro
Application.CutCopyMode = False
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _


View 3 Replies


Copy,Paste,Transpose In Loop

Dec 30, 2009

I m new to macro and I need to copy the data from 49 cells value which are

present in Two Rows to be converted into TWO columns and 48 rows

I have run a macro for this and I got

Sub Macro4()
' Macro4 Macro

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A1:B48"), Type:=xlFillDefault
End Sub

View 9 Replies View Related

Copy Cells From Grouped Sheets - Paste Special / Transpose

Nov 17, 2008

Twelve months of Data.

Each month in Columns
like so:

The labels are on the left.
The data is in each column to the right.
From column 2 to the end of the sheet at far right.

Family Size
One: 1 3
Two: 2 1
Three: 1 2
Four: 1 1.....................

View 9 Replies View Related

Excel Macro To Copy Data From Array And Paste To Separate Sheet Paste Special Transpose

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

Macro For Copy / Paste As Transpose?

May 15, 2014

see the attached file below. I have monthly precipitation data for yrs 1950-1999. I need to have the data formatted such that the precipitation data is copy/pasted into one single column, going all the way from jan 1950 to feb 1950 (next row) to mar 1950 (next row).....dec 1999 (last row). On occasion, I will also have data for shorter/longer time periods. the macro code I'd need to take this data and line it all up into one single column?

View 7 Replies View Related

Copy And Paste Special Transpose Add Comma

Apr 8, 2014

I need to copy and paste transpose, some over 100 values, but I want to add a comma after each value, so they can be put into JD Edwards for running a report.

View 3 Replies View Related

Shortening VBA Code Of Copy / Paste Values With Transpose

Aug 11, 2014

I think I have a very straight forward problem, I'm copying about 400 values from one workbook to another (from vertical range to horizontal range) and I currently have about 400 lines of code in order to do this. Below I've pasted the code I'm using now but the macro takes an estimated 30 seconds to run. I figure if I can reduce the number of lines the macro will run a lot faster

RowCount = openWb.Sheets("Library Raw Shear Rates").Range("A3").CurrentRegion.Rows.Count
With openWb.Sheets("Library Raw Shear Rates").Range("A3")


View 3 Replies View Related

Macro To Copy / Transpose And Paste Columns To Rows

May 1, 2013

I'm trying to get a macro together that will take a set of workbooks that I've merged (using Ron de Bruin's RDBMerge add-in) and transpose all columns from B to HB into rows. Now, I know that each spreadsheet is 210 columns and 244 rows large and they are concatenated on one another. Attached is a brief example of what I am trying to go from and what I am trying to get to.

View 1 Replies View Related

Copy, Transpose & Consolidate Data From Multiple Sheets

Jan 25, 2010

writing a macro that could save me loads of time. In the attached file there are 2 separate sheets of data ( named "Elements Innovation" and "Dinttap") that I need copied, pasted, transposed and consolidated into one master sheet (named "Desired result"). Note that the fields that relevant cells are highlighted in yellow. In reality I'll have many (~100) of these sheets that I need the data extracted from, and the work looks like it can be automated using VBA to speed things up.

View 2 Replies View Related

List All Sheets Names From Multiple Workbooks & Copy Transpose Certain Columns

Oct 7, 2009

this may or may not be easy for some of you but its driving me nuts. Here's what I need to do.

1.Create a new workbook

2.Cycle through multiple workbooks and all worksheets within each workbook (all in the same folder). All workbooks will have the same structure.

3.Take the worksheet names and put them in rows

4.Take the data from the first column (column A) from the first worksheet of the first workbook and put them across the top (i.e. transpose the data). The first column will be the same in all workbooks so it doesn't matter where I get it from.

5.Copy the data from column E from each worksheet and paste them in rows in the new workbook (again transpose) corresponding to each worksheet.

6. Perform simple mathematical calculations at the end of each row.

I expect to have approximately 26 workbooks with a total of 7000 worksheets. In the target workbook (i.e. the new one), I expect there to be 7000 rows (corresponding to the 7000 worksheets) and about 260 columns.

The only reason I need to transpose stuff is because Excel 2007 doesn't have 7000 columns.

Here's an example of what it would look like.
Example worksheet (Input)-
Worksheet ABC
Col A Col E
1/1/2004 $25
1/8/2004 $30
1/15/2004 $15

Imagine another worksheet called LMN with the same ColA but different values in Col E.

Output workbook
ColA Columns B Column C Column D
Sheet 1/1/2004 1/8/ 2004 1/15/2004
ABC $25 $30 $15
LMN $xxx $yyy $zzz

View 7 Replies View Related

Copy / Paste Certain Cells Then Paste 3 Sheets Into New Workbook - VBA 1044 Error?

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

[Code] ..........

View 1 Replies View Related

Loop With Copy Paste

May 21, 2012

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.

View 2 Replies View Related

For Loop Copy And Paste

Oct 11, 2012

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?


View 1 Replies View Related

Copy From Multiple Sheets (26), PASTE To 1 Sheet From 26 Sheets

Dec 26, 2009

I have a workbook with 26 sheets, labelled A to Z. Column A in all the sheets have names from rows A6:A35.

I need a macro or a code to extract all the names from each of the 26 sheets and paste it to a new sheet 'Names' under column A, such that names starting with 'B' paste under all the names 'A' and so forth till 'Z'.

View 9 Replies View Related

Copy Paste Macro Loop

Dec 19, 2013

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.

View 6 Replies View Related

VBA Loop Through Each Worksheet Except Two And Then Copy Paste

Jun 25, 2014

I do have macro which populates sheets based on given list.

I want to paste all data in newly created sheets from "Template".

I do not want to loop 2 sheets ("DATA" & "Template").

Data must be pasted with format & validation. Validation exists on Template sheet only. No other sheet is referred for validation.

View 2 Replies View Related

Copy/Paste 30 Times Or Loop?

Nov 18, 2009

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.

View 3 Replies View Related

Copy And Paste In New Worksheet With Loop

Feb 15, 2007

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
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

View 4 Replies View Related

Vba Copy & Paste Within A Loop Down A Column

Mar 29, 2007

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 ...

View 8 Replies View Related

Double Loop To Copy And Paste

May 18, 2007

i have this code

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 ..................

View 9 Replies View Related

Loop Copy And Paste Until Final Sheet?

Jan 16, 2014

I'm currently running this macro:

Sub CopyNextTab()
WorkbookName = ActiveWorkbook.Name


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).

View 5 Replies View Related

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.

View 6 Replies View Related

Selection Copy / Paste Loop Macro

May 24, 2013

Simple loop macro.

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.

View 9 Replies View Related

Loop Through The Rows - Copy Each Row And Paste In Another Excel

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


View 1 Replies View Related

Loop Worksheets Copy & Offset Paste

Dec 1, 2006

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")

Application.DisplayAlerts = False

Do While excelfile <> "" ' loop all files

View 7 Replies View Related

Loop Through Data To Copy To Other Sheets

Apr 11, 2002

Input Workseet:

Col A: Date
Col B through M: Headings are employee names, data is how many hours of vacation per DATE.

User will enter a date in column A, and then the corresponding number of vacation hours a person took that day. There are a dozen or so employees, so we're only entering a record on the dates that someone has taken vacation time. Dates are mm/dd/yyyy format. The hours are number/two decimals.

What I would like:

An update command button (hey, I can actually do that part!) that has an on-click that:

Loops through each column B through M, and copies the information to the employee's individual sheet.

The individual sheets:

Columns are:
A = Date of vacation
B through M are months Jan through Dec.

Data starts *paste* in cell A12, where the date of the first vacation day they take should appear. If it was a half-day in February, .5 (or .50) will appear in cell A14.

If it's not clear, I'm happy to send the file! If you put your email here, I'll send it right away. If you email me at home, it'll be a few hours before I can send.

No rush on my part.

Really appreciate it!! I'm not a coder. I know small bits and pieces. When you start talking about Dim, I think of chinese food.


[ This Message was edited by: Dreamboat on 2002-04-11 10:20 ]

View 9 Replies View Related

Loop To Find A Cell, Then Copy Offset Paste

Feb 1, 2009

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.


200 Firstname Lastname
204 99999999 (Employee ID)
G38 00005000 (i.e. Pension Deduction)
H38 00007580 (i.e. Benefits Deduction)
X96 00012099 (i.e. Staff Club Deduction)
200 Firstname Lastname
204 99999998
G38 00000775
X96 00001000

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.

View 7 Replies View Related

Compare Rows In Column A And Copy/paste If They Are The Same Using A For Next Loop..

Jul 26, 2009

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.

View 4 Replies View Related

Excel 2007 :: Copy And Paste A Range Using Do Loop?

Jul 27, 2012

Here is my situation:

- 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).

- 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.


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)"

[Code] .......

View 1 Replies View Related

Run A Loop For 1000 Times And Take Value - Copy / Paste In A Range

Sep 30, 2013

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.


Sub montecarlos()
Dim MCs As Long
Dim c As Range
Dim lCount As Long
Dim lNum As Long
lCount = 0
lNum = 1


View 6 Replies View Related

Copyrights 2005-15, All rights reserved