Free Microsoft Excel 2013 Quick Reference

Macro to copy paste columns to specific rows


This is a bit of a strange one - but probably relatively simple, I'll be honest I'm an amateur when it comes to macros.

Basically what I would like to do is copy and paste 3 columns to specific rows. See attached for a sample of dummy data.

What I would like to do is copy and paste
C1 to A2, D2 to A3, E1 to A4
C5 to A6, D5 to A7, E5 to A8
C9 to A10, D9 to A11, E9 to A12
C13 to A14, D13 to A15, E13 to A16
I have a full sheet of data like this, so would like a macro which would convert it all for me.

Thanks in advance.

Post your answer or comment

comments powered by Disqus
Hi. I need a macro to copy certain columns from one sheet to another. I already have code that sort of works that I have taken from elsewhere but I need little bit help to correctly finish it off. I have sheets called "Hyperlink" (the source sheet) and ME22 (the destination sheet).

The code that I have so far does the following:
It takes info from column 4 from source sheet and places in column 1 on destination sheet
It takes info from column 5 from source sheet and places in column 2 on destination sheet
It places letter "X" in column 3 on destination sheet
It takes info from column 14 from source sheet and places in column 4 on destination sheet
It places the following to col 5 on destination sheet - concatenation of text "Supp Recoll" + column 21 and column 14 from source sheet

Now the code that I so far have is the following:
Sub test()
Dim a, i As Long, maxval As Double: Application.ScreenUpdating = False
With Sheets("Hyperlink"): a = .Range(.[a2], .Cells(Rows.Count, "b").End(xlUp).Offset(, 22)): End With
With Sheets("ME22"): maxval = Application.Max(.Range(.[a2], .Cells(Rows.Count, "a").End(xlUp)))
For i = 1 To UBound(a)
    a(i, 1) = a(i, 4): a(i, 2) = a(i, 5): a(i, 3) = "X": a(i, 4) = a(i, 14): a(i, 5) = "Supp Recoll "
& a(i, 21) & a(i, 14)
Next: .Cells(Rows.Count, "b").End(xlUp).Offset(1, -1).Resize(UBound(a), UBound(a, 2)) = a: End With
Application.ScreenUpdating = True: End Sub
1) Now the problem with this current code is that it carries on copying information from source sheet to destination sheet as far as it column 24. I want it to stop at column 5 (column E) as the last column being filled in. Rest of the columns should stay clear. How can I adjust my code to make this happen?
2) Line : With Sheets("ME22"): maxval = Application.Max(.Range(.[a2], .Cells(Rows.Count, "a").End(xlUp))). How could I change this line as the "maxval" was defined in the original code that I used to learn and to get this far. This feature is not required in my macro but I have no idea how to remove it without rendering the rest of the code useless.

All ideas are ideas are welcome. I have attached a test spreadsheet.

I have recorded a macro to copy paste columns from one wokrbook to another wokrbook.

Here is what i have done:
I have alot of workbooks open (15). I recorded the macro in book 1 and clicked on the other book 2, selected two columns and went back to the orgininal workbook and selected paste in cell a1. When i cleared the columns, and ran the macro...some times the macro will copy paste and othertimes the cells are still empty..if i run the macro twice in arrow it works every time...

Any thoughts on why it isglichy?


Hi ,

Before you look into the spreadsheet, below is the simple illustration. How i can wirte macro to copy paste value from column E in Table 2 to the respective month in Table 1. Month is dependent, and it is always change. There will be a selection for month in the spreadsheet. Please help.

Table 1: By Month View
April May June Type A Type B Type C Total

Table 2: Summary of April Data
A B C D E Group A Group B Group C Group D Total TYpe A 1 2 3 3 9 Type B 2 3 3 1 9 Type C 1 2 1 2 6 Total 24

I have a sheet with data in several columns and in the first 1000 or so rows is there a macro to copy just these filled rows and then paste to another sheet at the end of the filled rows in that sheet. Thanks in advance for any help


I have one excel document with 8 sheets(ie sheet1, sheet2.......). Each sheet has 6 columns and each column has over 100 rows. I am looking for some help for macro as I want to copy 3 columns(ie column 4 , column 5 and column 6) from sheet 1 and insert a new sheet after sheet 1 and paste 2 columns(ie column4 and column 5) and rename that sheet. After that I want to insert another sheet after sheet 8 and paste the 3rd column (ie column 6) from sheet 1 and rename that new inserted sheet. So I will have 10 sheets.

hope its not that much confusing...



Hey guys...I've run into a snag that I think has an easy fix. I believe that i know how I want this process to run, I'm having trouble executing it.

Here's the details: I have a spreadsheet (see attached image) that contains a record of time (in months) running across the sheet (the columns) and down the side (the rows) is a list of products. Within these cell blocks (divided by month and product) are some dates pertaining to order shipments.

I am trying to create a macro that will loop through each row and determine if the cells cantain data, if they do contain data I would like that copied and pasted to a new sheet. After copying/pasting the shipment data the macro will have to copy/paste the product name from column A of the applicable row.

Essentially this will build a user firendly database that I can easily organize and work from.

Please feel free to inquire if I did not explain this well.

Thank you in andvance for any help.


Nice to meet you all here. I am a newbie in vb and also a new member to Excel Forum.
I have encountered some difficulties in creating macro in excel where I need to copy data from Sheet1 row A1:C3 into Sheet2 column A1:C3 as well. Which means Sheet1 is a table whereas Sheet2 is a form. The idea now is to copy the data horizontally(row) become vertically(column) in each wordsheet respectively. Attached is the sample showing the simple requirement. Appreciate helps from anyone. Thanks.

I have to copy & paste information from on worksheet to another for 10
different people & there could be like 25 rows each to copy for each of them.
Is there any way I could create a marco that would automatically carry the
information over to the other sheet without me copying & pasting. They each
have there own Codes.
Example: Everything with Code 15. I need it & everything in it's row pasted
to the tab 15 worksheet in the workbook. Could someone help. Oh yea, I am
totaly clueless when it comes to writing macros.

I have a list of parts that begin with certain letters (B,N,V,etc) with
additional information (price, qty, size). Is there a Macro to copy only the
rows that start with N (example of parts are N11, N12, NP16, NX10) and paste
them into another workbook?

i'm trying to write a macro to copy a row from sheet 1 to sheet 2, but the row can be varied by the user. in other words they can select row 15 or row 35 or any other. i can do the copy and paste bit but cant figure out how to get the macro to copy only the selected row. can anyone help please

Was looking into this, but basically i have a repetitive task of copying contents from various columns, and pasting them into 1 column.

1 right after the other. I figured there could be a way for a macro to copy contents from multiple columns and paste them into 1 column.

I have including 2 files, 1 from the start of the file, and 1 how it should be so it will have clarify what i am trying to do.

So basically if you open book1 , i have factory install tabs,

I need the columns contents from the first tab the (5k-20k) starting with Column B, then E, then H, and so on( Every 3rd column starting with B ) to be copied and pasted in Column A of the (Factory Install Tab) ( last tab ).

If you open the 2nd file this will be clearly understood. In the second file in the factory install tab you will notice i did the first 2. I highlighted each starting part in yellow just so its easier to see. SO in column A you should have all the contents in the 5k-20k tab ( starting with b every 3rd ), pasted in column A 1 after the other.

The next tab is the 30k tab, i need all of the columns contents copied starting with B ( every 3rd again ), copied in Column C of the Factory Install tab. Basically every tab's contents needs to have its own column in the Factory Install tab.

If anyone could help that would be great, this is a very time consuming process manually, and it would seem like a macro could be made to easily copy contents.


Hey there-

I've been searching around and found bits and pieces of what I need to do but can't quite put it together the way I want it to work. Here's what I'm trying to accomplish:

At work I manage a base of accounts that fluctuates every month. I’d like to have Sheet1 contain a master list of accounts I manage and have subsequent worksheets contain the monthly updates of my accounts (paste an exported report into the worksheet each month). Then I’d like to ID the accounts that are in the monthly update but not in Sheet1 then copy/paste the whole row of the new account into the first blank row in Sheet1.

The columns I'm working with are as follows:

A: Parent
B: Child
C: Customer Name

We would identify the new account by looking at Column B: "Child" in Sheet1 and Sheet2. If the number in Column B in Sheet2 (the monthly update) does not appear in Column B of Sheet1, I need to copy the whole row from Sheet2 and paste to the first empty row in Sheet1.

I've attached a workbook that's a much simplified version. Thanks in advance!

The post cited here is close to what I need for my project but I have not been able to work it out myself: http://="". Instead of putting the information to new sheets I would like the macro to copy to one sheet instead. However, I also need it to be done three times; each range in the Database sheet would copy to a range on the Coverage sheet. (I have put the code from this reference in module 12.)

Attached is a mocked file exact to what I am trying to do including the existing macros and formulas running. I have labeled everything and these are some parameters that may help:

1) This is my exact layout and macros
2) The Database sheet must pull information from the Forms List sheet. There are hidden rows providing Indirect data validation dropdowns so the Forms List sheet itself cannot be the database.
3) The Coverage tab currently has the C column merged to H. If this is problematic it does not need to stay that way.
4) The Database, Forms List and DropDown Menus sheets all need to be hidden.
5) On the Coverage sheet I have labeled my choice A, B, and C for preferences on where to run the macro from. Obviously it cannot be from the Database sheet like the example because it needs to be hidden.
6) The Type pull down menu matches the columns on the Database. When a choice is made there are three corresponding columns - one for each range I am trying to copy.

Thanks so much for the help in advance! Again and Again!

Can somebody tell me how to write a macro to copy n columns from row x to row y. The "n" is to be asked for to the user. From which rows will I sort out later. I'm an absolute n00b if it comes to VBA.....

I am trying to write a very basic macro to copy and paste, but when I record the keystrokes, step 4 (see below) seems to be recording absolute cells and not relative (?). At any rate, I can't seem to figure out how to get it to select the text the way I want it to. These are the keystrokes I want:
1. Ctrl-c in the current cell
2. DownArrow
3. Shift(hold)-End-Down
4. Shift(hold)-Up
5. Ctrl-v
6. End-Down

I have set the record tool to record "relative" references.

Below is the code that was recorded. It's line 5 that's not working - how do I fix it? What is the command to tell the program to press the shift key and an up arrow?? Such a simple thing, and yet I can't find the answer anywhere! Things sure have gotten complicated since the old Lotus 1-2-3 macro language...

    ActiveCell.Offset(1, 0).Range("A1").Select 
    Range(Selection, Selection.End(xlDown)).Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have 4 sheets called Data, xxx, www and yyy. The sheet names xxx, www and yyy are also words that are existing in the sheet Data in Row C. Am I able to copy paste all the lines existing in the sheet Data including i.e. xxx in Row C into the sheet xxx? This should also work for the words www and yyy.

I'm using a macro to copy from one workbook to another in the same instance
of Excel. The macro successfully copies the data to the clipboard but does
not proceed to paste it in the new workbook. No error screen pops up it just
doesn't paste. Your help is appreciated. Here is my current macro code:

Sub CopyPasteToNCTthree()
' CopyPasteToNCTthree Macro
' Macro recorded 4/20/2009 by _
' Keyboard Shortcut: Ctrl+Shift+M
Workbooks.Open Filename:="C:Zip DocumentsCustomersNew Customer
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 1
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
David P.

Hi there,

I have a column of text (more than 500) that need to copy & paste a specific content from it.
Here is how it looks like:


Here is what i need it to be:

I cannot keep highlight what i need and then copy & paste. it is more that 500.
Is there any formula or Marco can be use to make it automatic.


Hi all,
I have a range of numbers AL5:AL22 (there is data below AL22) with no blanks but with zeros sometimes, appreciate providing a macro to copy this range (AL5:AL22), skip the cells with zero values and paste special (value) to the range A12.


I have a spreadsheet with about 800 rows. In column D, there is a variety of numbers from 013 to 080. There could be anywhere from 2 to 100 rows to each number. Everytime the number changes in column D, I would like to copy that group of rows to either another tab or to another worksheet. So, Rows 1-5 have 013 and rows 6-20 have 018 in column D and rows 21-81, have 025 in column D. Is is possible to have it moves rows 6-20 to one worksheet and rows 21-81 to another worksheet and keep going until there is no more data?

I want to copy & paste the cell data when any cell in the worksheet
gets selected. I would like to have the data stored in a list either on the
same worksheet or in another worksheet. I have the selected cells background
color change when selected but having trouble putting the cells contents into
another cell.

Doing a favour for a friend and I am stuck...there is usually something I can find online to help me but I have looked for several hours and come up with zip...

In the macro below I would like to copy and paste columns A though H from one worksheet to another...not the entire row. Am I furnishing enough information? I would appreciate any help out there!

    Set i = Sheets("Tracker") 
    Set e = Sheets("Carpenter") 
    Dim d 
    Dim j 
    d = 3 
    j = 4 
    Do Until IsEmpty(i.Range("B" & j)) 
        If i.Range("B" & j) = "Carpenter" Then 
            d = d + 1 
            e.Rows(d).Value = i.Rows(j).Value 
        End If 
        j = j + 1 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

If someone could assist me in developing this macro I would be extremely appreciate.

The macro would start by Selecting Sheet “Paste Data Export Here”

Starting in Row 2 – Copy All Values in column to Sheet “Formatted Data” into columns specified below:

Copy From Sheet "Paste Data Export Here" in Column A and Copy to Sheet"Formatted Data" into Column A
Copy From Sheet "Paste Data Export Here" in Column F and Copy to Sheet"Formatted Data" into Column B
Copy From Sheet "Paste Data Export Here" in Column O and Copy to Sheet"Formatted Data" into Column C
Copy From Sheet "Paste Data Export Here" in Column N and Copy to Sheet"Formatted Data" into Column D
Copy From Sheet "Paste Data Export Here" in Column I and Copy to Sheet"Formatted Data" into Column I
Copy From Sheet "Paste Data Export Here" in Column J and Copy to Sheet"Formatted Data" into Column J
Copy From Sheet "Paste Data Export Here" in Column G and Copy to Sheet"Formatted Data" into Column K
Copy From Sheet "Paste Data Export Here" in Column K and Copy to Sheet"Formatted Data" into Column R
Copy From Sheet "Paste Data Export Here" in Column L and Copy to Sheet"Formatted Data" into Column S

There are header rows on each sheet, so when the columns are copied they need to be pasted into the 2nd row as well.

Thanks so much in advance!

My first post. I want to copy and paste rows to new sheet based on conditions on colmn E.
I do not want whole row but selected columns only. Can u adv a macro for this.

No luck finding an answer? You could always try Google.