Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Moving rows of data into seperate tabs (worksheets)

Heres my situation:
Sheet1 has tons of data...."Column A" has lots of diff. types, for example CompanyA, companyB, and so on..........

How can I move rows of data for each column (all of companyA for example)
into their already created seperate tab (tab called companyA).

I already have all the differant worksheets created, but im thinking a IF statement can move the data, but im having problems coming up with the coding....yes, I can simply filter columnA by name, and then copy and paste, but this is requiring too much human work on a constant basis....please help...

Im thinking a IF (certain rows in columnA = companyA), move into worksheet CompanyA, and so on for the other companies... but i dont know how to code in excel language.

Thanks to anybody that can help!!


Post your answer or comment

comments powered by Disqus
I often have to move Rows of data from multiple worksheets on to one master sheet, and wondered if anyone had a macro that would go through all of these and do it for me?
I need something exactly like for particular rows ( i no need to copy the entire rows from the sheets)... I have very limited experience with VBA.

Thanks!
Nirmal

Does anyone know how I would create a macro that would move a row of data to a different worksheet based on the validation information of a cell?

For instance, I am working with pending cases. I’d like to have all open cases on one sheet and when they close, I would choose ‘Closed’ in my validation cell and would then like that row of data to automatically be moved to the Closed worksheet. And then I would like for that worksheet to automatically sort based on the criteria we decide on (ie Alphabetically or Chronologically, etc.)

Any help?

All,

This thread was original posted as 651452, however because I am still searching for an answer I have reposted the add.

http://www.excelforum.com/excel-programming/651452-copy-rows-of-data-into-a-different-format.html

Here is the problem:

I have a list of activities (each activity is one row of data). These will be input manually into Sheet2. (see attached file)

The activities then need to be automatically copied and pasted into Sheet1. However, the data is displayed in a different order than in sheet1. So I need some code which will run through a loop for each row of data and then copy and paste it into sheet1 until it reaches an empty row.

The cell positioning of each data set in sheet1 is equally spaced. VLOOKUP will not work for me here as I do not want any formulae or VBA script in sheet1. I cannot change the format of sheet1 as it is a company form.

Sample file is attached.

Thanks in advance for your help.

Dave32

I need to know an easy way to combine multiple rows of data into a single row
of data for a labor report that I'm working on.
My problem is that I have a report that generates 12 columns. I add an
additional 12 columns to this spreadsheet, one for each month. The first 9
columns contain specific data for each employee, the next 2 columns contain
data that is specific to the amount of labor each individual works in a given
month. The given month is the 12th column. So if the person charged to the
project 4 different months, I get 4 rows entries in the table.

I'd like to compress these 4 lines down into one, and so on for all
employees in the report. The first 9 columns contain duplicate data, since
the employee data is the same. For each of the month columns I've done a
test on the 12th column to tell what month the individual charged labor to,
and populated the labor amount from column 11 into the appropriate month
column. Now if I subtotal each month, I can see one line, by employee and
the amount of labor for each month on one line. I'd like to not have to
subtotal and see this same report with all the data from the first 9 columns
as well.

Any help would be greatly appreiciated. Thanks, JLM

In one of the columns there is data that I'd like entered into about 10
fields (columns), for each row there maybe 5 occurances of the first 9 pieces
of data. I want the the
10th column put in a new column (month)

I am trying to create a macro that will copy data from one sheet to four other sheets depending on some requirments in the data. I have a main worksheet that will store the data, called "data". The other three names of the sheets are "Esky", "Indy", "Gfld", and "Other". When copying the data, I need to copy the entire row of data to the required sheet. The requirements are below:

1. Sort the data based on the following criteria (which has been sort of modifed since last time).
- "21" or "IM" - Indy Worksheet
- "22" - Gfld Worksheet
- "51" or "MZ" - Esky Worskeet

If the first two characters are "PZ" or the first character is "Z", you need to sort by part numbers.
- 1839615C92 OR 1838871C92 - Gfld Worksheet
- Any other part - Indy Worksheet

2. I just want to copy certain columsn within the row to the new sheet. I ONLY want to copy the values and not the formats, as I already have the "templates" formatted. I want to copy the following columns:

- Columns A, C, D, F go into the templates in columns A-D.
- Column B goes into the template into column H.
- Column F goes into the template into column I.

3. For each record or row that gets copied to the sheet, I want to associate the predefined formulas that I have in my templates. So for each row of data, I want to copy the formulas to that row of data.

I have no clue how hard any of this might be, but any help would be greatly appreciated. Thanks in advance for your help.

The spreadsheet exceeds the maximum limit to post, so you can download it here:

http://www.oilmate.com/TestWorkbook.xls

I am new to programming and not sure how to write a Macro, but could use it for work. I have 2 worksheets with several hundred rows of data. I want to write a program that can check to see if both worksheets contain the same data and if possible, have anything that is different appear in a seperate tab. This would avoid me searching through thousands of lines one by one. Is this feasable? Thank you for your help.

I want some scattered rows of data that are uniquely identified by a number
(example 3), in a cell in their row. Using an IF function, I want to move
the data to another page, then have it all move up to fill the first open
row. Is this possible. I know how to get the IF part done. But not the
close up the spaces part. For example

Journal entry page
date vendor item amount code

11/30 progress elec 75 .00 3
12/1 taylors supplies 32.90 2
12/29 progress elec 44.00 3

Progress account page (this is how it brings it now, with a space)

Date Item Amt
11/30 elec 75.00
I want to eliminate this space and have the data move up automatically.
12/29 elec 44.00

Is this possible?

Hello - - I have an excel file that contains about 1000 rows of data, from column A to O. Column C contains either the letter A or the letter I, A means Active, I means Inactive.

What I'd like to do is replace my monthly manual task of moving all the I's to sheet2. When completed, the excel file should have two sheets, all of the A's on one, and the I's in the other. The original excel file is not sorted by column C. The end result should have the same row 1, being the header row.

Any suggested VBA code to get this started would be great! There are some additional steps, to save the file to a specific location but I think I could do that once the excel file is formatted the way I wanted it....

Thanks again for your help....!

Hey all - - Thanks for all your replies, I did however find a workable solution from Ron B.'s site....here it is, a bit longer then other suggestions - - I did add in the save and copy commands at the end:

Sub Copy_To_Worksheets()
'Note: This macro use the function LastRow
    Dim My_Range As Range
    Dim FieldNum As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    Dim ws2 As Worksheet
    Dim Lrow As Long
    Dim cell As Range
    Dim CCount As Long
    Dim WSNew As Worksheet
    Dim ErrNum As Long

    'Set filter range on ActiveSheet: A1 is the top left cell of your filter range
    'and the header of the first column, D is the last column in the filter range.
    'You can also add the sheet name to the code like this :
    'Worksheets("Sheet1").Range("A1:D" & LastRow(Worksheets("Sheet1")))
    'No need that the sheet is active then when you run the macro when you use this.
    Set My_Range = Range("A1:O2000") '  & LastRow(ActiveSheet))
    My_Range.Parent.Select

    If ActiveWorkbook.ProtectStructure = True Or _
       My_Range.Parent.ProtectContents = True Then
        MsgBox "Sorry, not working when the workbook or worksheet is protected", _
               vbOKOnly, "Copy to new worksheet"
        Exit Sub
    End If

    'This example filters on the first column in the range(change the field if needed)
    'In this case the range starts in A so Field:=1 is column A, 2 = column B, ......
    FieldNum = 3 ' I changed this to 3 for column C

    'Turn off AutoFilter
    My_Range.Parent.AutoFilterMode = False

    'Change ScreenUpdating, Calculation, EnableEvents, ....
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView
    ActiveSheet.DisplayPageBreaks = False

    'Add a worksheet to copy the a unique list and add the CriteriaRange
    Set ws2 = Worksheets.Add

    With ws2
        'first we copy the Unique data from the filter field to ws2
        My_Range.Columns(FieldNum).AdvancedFilter _
                Action:=xlFilterCopy, _
                CopyToRange:=.Range("A1"), Unique:=True

        'loop through the unique list in ws2 and filter/copy to a new sheet
        Lrow = .Cells(Rows.Count, "A").End(xlUp).Row
        For Each cell In .Range("A2:A" & Lrow)

            'Filter the range
            My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & _
             Replace(Replace(Replace(cell.Value, "~", "~~"), "*", "~*"),
"?", "~?")

            'Check if there are no more then 8192 areas(limit of areas)
            CCount = 0
            On Error Resume Next
            CCount = My_Range.Columns(1).SpecialCells(xlCellTypeVisible) _
                     .Areas(1).Cells.Count
            On Error GoTo 0
            If CCount = 0 Then
                MsgBox "There are more than 8192 areas for the value : " & cell.Value _
                     & vbNewLine & "It is not possible to copy the visible data." _
                     & vbNewLine & "Tip: Sort your data before you use this macro.", _
                       vbOKOnly, "Split in worksheets"
            Else
                'Add a new worksheet
                Set WSNew = Worksheets.Add(After:=Sheets(Sheets.Count))
                On Error Resume Next
                WSNew.Name = cell.Value
                If Err.Number > 0 Then
                    ErrNum = ErrNum + 1
                    WSNew.Name = "Error_" & Format(ErrNum, "0000")
                    Err.Clear
                End If
                On Error GoTo 0

                'Copy the visible data to the new worksheet
                My_Range.SpecialCells(xlCellTypeVisible).Copy
                With WSNew.Range("A1")
                    ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
                    ' Remove this line if you use Excel 97
                    .PasteSpecial Paste:=8
                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteFormats
                    Application.CutCopyMode = False
                    .Select
                End With
            End If

            'Show all data in the range
            My_Range.AutoFilter Field:=FieldNum

        Next cell

        'Delete the ws2 sheet
        On Error Resume Next
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
        On Error GoTo 0

    End With

    'Turn off AutoFilter
    My_Range.Parent.AutoFilterMode = False

    If ErrNum > 0 Then
        MsgBox "Rename every WorkSheet name that start with ""Error_"" manually" _
             & vbNewLine & "There are characters in the name that are not allowed" _
             & vbNewLine & "in a sheet name or the worksheet already exist."
    End If

    'Restore ScreenUpdating, Calculation, EnableEvents, ....
    My_Range.Parent.Select
    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With
    
    MsgBox ("Copy Complete - - Now saving file to T:Job Code folder")
    
    FileCopy "T:Job CodesActive Job Codes.xls", _
"T:Job CodesArchiveActive Job Codes " & Format(Now(), "yyyy - mm") & ".xls"
    
    ActiveWorkbook.SaveCopyAs Filename:="T:Job CodesActive Job Codes" & ".xls"
MsgBox ("Active Job Code Now Saved the T Drive")


End Sub
Thanks again...I'm closing this with Solved!!!

I have a sheet of data and when YES is entered into a particular column I want that row of data to automatically be moved to the second sheet of the same workbook - does anyone know how???

Function create_Formulas()
    
    Dim rng As Range
    Dim Last_Cell As Integer
    Dim wksName As String
    
    Activate ("Main")
    Last_Cell = Range("A" & 65536).End(xlUp).Row
    


    For x = 2 To Last_Cell
    
        wksName = Worksheets("Main").Range("A" & x).Value
    
        'Activate (wksName)
        LastRow = Range("A" & 65536).End(xlUp).Row
    
        Worksheets("Main").Range("A:H" & x).Copy
        Worksheets(wksName).Range("A" & LastRow).PasteSpecial xlPasteValues
        
        
        
    Next x
End Function
In the above code I am trying to copy a variable row of data to a variable worksheet. The variable worksheet is decided by the value in column A of the row I am copying. Hence, I am going through a list of items and moving them to their appropriate sheets.

My problem comes up when I try to copy the variable row. In the line

I am getting an Application-defined or object-defined error. Run time error '1004'.

Does anyone know what format I need to use to successfully copy a variable row?

Thanks in advance for your help.

I have got a workbook with 20 or more worksheets. Every quarter I need to paste/append new data on these worksheets (which has already got existing data from previous quarters). All worksheets have different rows of data, for eg One worksheet may have 10rows, the other may have 50. Hence when I am writing my code, I cannot specify the destination cell as for eg A11, or A51.
How do I write up a VBA code that looks for the last row of data on these various worksheets and then pastes the new information on the next row(new row).

Looking forward to someone helping me. Thanks in advance.

Hi I am trying to combine two rows of data into one.

The first three columns are the same, the last two are seperated into to rows
costcodecode nameBundleunit3000505MACHINING76.123000505MACHINING80.602
Style costcode code name Bundle unit
3000 505 MACHINING 80.602
3000 505 MACHINING 76.12

The desired result would read

Style costcode code name Bundle unit
3000 505 MACHINING 80.602 76.12

I have attached a copy of the data. The orignal spreadsheet contains 500000+ lines

10 identical worksheets sorted in 10 different waysThere are roughly 30 columns of data, one of which contains a string of characters truly unique to each row (e.g. titled "UNIQUE ID")However, since the sheets are sorted differently, it's nearly impossible to quickly find the same row of data in the other sheetsWith the cursor in any given row, would like to design a macro that searches the entire workbook and selects all rows which contain the unique character string which is identical to the unique string in the active row, and then shades all the selected rows a given color (e.g. purple).

Hello,
I have a txt file that has multiple rows of data that i want to condense into one row with multiple columns in a XLS file. For Example

TXT file:
-------------------------------------------------------------
field1: record1-field1number
field2: record1-field2text
field3: record1-field3text
field4:
field 4 text
takes up 2 lines

field1: record2-field1number
field2: record2-field2text
field3: record2-field3text
field4:
field 4 text
takes up 2 lines

DESIRED XLS FILE ( | denotes next cell)
-------------------------------------------------------------

record1-field1number | record1-field2text | record1-field3text
record2-field1number | record2-field2text | record2-field3text

I hope someone can help me here.

I have a large number of rows of data that I need to make into one long row with the data separated by commas.

Ex.
A1

1
2
3

End result = B1 1,2,3

Can someone please help?

I have a spreadsheet that has several rows of data varying in length.
Everything under the first column is a part number then everything to
the right of the part number in each row is a product code. I need
something that will convert this:

PN PROD PROD PROD PROD PROD PROD PROD

12345 rq545 gr433 or444 tz222 hq444 pl111
67892 ca544 ca899 cb355 ay111
98765 dd777 hw345 pp943 qa612 tw555 uj764 vf544
58992 gh678 lo122 uo512 mm677 xx411

INTO:

PN PROD
12345 rq545
12345 gr433
12345 or444
12345 tz222
12345 hq444
12345 pl111
67892 ca544
67892 ca899
67892 cb355
67892 ay111
98765 dd777
98765 hw345
98765 pp943
98765 qa612
98765 tw555
98765 uj764
98765 vf544
58992 gh678
58992 lo122
58992 uo512
58992 mm677
58992 xx411

except on a much larger scale of data....

any help would be appreciated.....Thanks, Steve

All,

I have a list of 33+ marketing activities (each activity is one row of data). These will be input into Sheet2.

The activities then need to be automatically displayed in Sheet1 but in a different format. Sheet1 cannot be changed and is corporate finance form.

Sample file is attached.

Anybody have any ideas?

Thanks in advance for your help.

Dave

I need help.
I am trying to utilize a macro that will be able to take the data from one worksheet and paste selected cells into another that is formatted. The problem for me comes from not knowing enough about macros to put the proper code in that will help me be able to put different amounts of rows of data into my first worksheet and have the macro stop after it hits a cell with no data in it.
I recorded the macro as I want to see it and have tested it. I only utilized two rows worth of data to show you, but my hope is to be able to automate it so it knows when there is no more data to move. Any ideas? You have all been very helpful in the past and make me look like a star. Your assistance would be greatly appreciated.

Here is my code.

Sheets("Lead Worksheet").Select
    Rows("11:11").Select
    Selection.Insert Shift:=xlDown
    Range("B12").Select
    Sheets("Paste in this sheet").Select
    Range("L4").Select
    Selection.Copy
    Sheets("Lead Worksheet").Select
    ActiveSheet.Paste
    Sheets("Paste in this sheet").Select
    Range("Q4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Lead Worksheet").Select
    Range("C12").Select
    ActiveSheet.Paste
    Sheets("Paste in this sheet").Select
    ActiveWindow.LargeScroll ToRight:=-1
    Range("H4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Lead Worksheet").Select
    Range("E12").Select
    ActiveSheet.Paste
    Sheets("Paste in this sheet").Select
    Range("N4:O4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Lead Worksheet").Select
    Range("F12").Select
    ActiveSheet.Paste
    Sheets("Paste in this sheet").Select
    ActiveWindow.LargeScroll ToRight:=1
    Range("U4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Lead Worksheet").Select
    ActiveWindow.LargeScroll ToRight:=1
    Range("I12").Select
    ActiveSheet.Paste
    ActiveWindow.LargeScroll ToRight:=-1
    Rows("11:11").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
    Range("B12").Select
    Sheets("Paste in this sheet").Select
    ActiveWindow.LargeScroll ToRight:=-1
    Range("L5").Select
    Selection.Copy
    Sheets("Lead Worksheet").Select
    ActiveSheet.Paste
    Sheets("Paste in this sheet").Select
    Range("Q5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Lead Worksheet").Select
    Range("C12").Select
    ActiveSheet.Paste
    Sheets("Paste in this sheet").Select
    ActiveWindow.LargeScroll ToRight:=-1
    Range("H5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Lead Worksheet").Select
    Range("E12").Select
    ActiveSheet.Paste
    Sheets("Paste in this sheet").Select
    Range("N5:O5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Lead Worksheet").Select
    Range("F12").Select
    ActiveSheet.Paste
    Sheets("Paste in this sheet").Select
    ActiveWindow.LargeScroll ToRight:=1
    Range("U5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Lead Worksheet").Select
    ActiveWindow.LargeScroll ToRight:=1
    Range("I12").Select
    ActiveSheet.Paste
End Sub


I created a payroll table with drop down lists and numerical data. I would like to extract data with a certain variable (by the jobsite column) and place the entire row of data into another excel sheet which will just be a totals page for that particular job.

How can I do this?
If need be i can send my table to you so you can see the layout or provide a picture.

thank you in advance! I have been stuck on this for the past few hours.

I am new to VBA and Macros and would like to request help in completing this task. I need to copy rows of data from a master worksheet to one worksheet when a "sold date" is entered and to a different worksheet when a "sit/no sale date" is entered. Can anyone help with the code required to accomplish this? Thank you.

Hi,

I'm having a problem with the code below when only 1 row of data exists in a worksheet. Basically the code runs through all worksheets and needs to find the unique values which it does if more than 1 row of data is found. D1 is the heading row.


	VB:
	
LastRow = .Cells(Rows.Count, "D").End(xlUp).Row 
Range("D2:D" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("S3"), Unique:=True 

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


Hi Forum,

I know this question has been asked ad nauseum, but I cannot find the right code for my project. I need to run the same database dump every day and find the differences. Both spreadsheets will have the same column headings, with Column A being the lookup value. Each spreadsheet will have many columns of data. I need to find the differences from Row 1 on spreadsheet A with Row 1 on spreadsheet B.

Example:
Spreadsheet 1, Column A value is 900026. Need to find 900026 in Column A on Spreadsheet 2. Compare all the data in each column to see what has changed. If anything has changed on Spreadsheet, copy the row from Spreadsheet 2 onto a new sheet and 'tag' which data has changed.

Also, again using Column A as the identifier, if there are rows on data on Spreadsheet 1 that do not appear on Spreadsheet 2, copy the entire row onto a new sheet and tag it as "Not on new datadump". And vice versa, is there are rows of data on Spreadsheet 2 that do not appear on Spreadsheet 1, copy the rows of data onta a new sheet and tag it as "Not on old datadump".

Thanks you so much,
Ceriga

I have a data file that contains transactions, and some of the transactions are for the same vendor. I would like to 1) locate all of the duplicate vendor IDs, 2) merge the multiple rows of data into a single row, using concatenate, sum, etc, 3) paste the new single row as a value, and 4) delete the multiple rows of data.

The issue is complicated by the fact that the number of rows containing data for a single vendor can run from 1 to 50, so merging the data becomes more difficult.

A brief sample is attached.

******** ******************** ************************************************************************>Microsoft Excel - Data for looping test.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA17=
ABCDEFGH4Duplicate*IDCheck*NbrCheck*Date*Vendor*NameRef*NbrInvoice*NumberInvoice*Date*Amount*Paid*5*3404/04/2007Vendor*#*10180920107AA4/1/2007**************7.20*6*3504/04/2007Vendor*#*20180951050242/28/2007**************2.89*7*3604/04/2007Vendor*#*30180962607RB3/26/2007**************2.00*813604/04/2007Vendor*#*30180972707RB3/27/2007**************3.78*923604/04/2007Vendor*#*30180982907RB3/29/2007**************3.74*10*4104/04/2007Vendor*#*80181032399433/11/2007************60.60*1114104/04/2007Vendor*#*80181042410013/18/2007*************51.90*12*4604/11/2007Vendor*#*130181082158833/2/2007************60.44*1314604/11/2007Vendor*#*130181092161493/16/2007*************10.50*14*4704/11/2007Vendor*#*14018110903-043/19/2007*************16.95*15*5904/18/2007Vendor*#*260181221915743/28/2007**************0.20*1615904/18/2007Vendor*#*260181231819893/28/2007****************1.31*17*6204/18/2007Vendor*#*29018126V271273/24/2007**************0.26*1816204/18/2007Vendor*#*290181275961273/24/2007**************0.73*1926204/18/2007Vendor*#*290180940107JV4/1/2007**************6.00*2036204/18/2007Vendor*#*290181282707RW3/27/2007****************1.15*2146204/18/2007Vendor*#*290181297721843/20/2007**************77.11*Data (2)*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.[/url]

Hello

I am looking to build a rig schedule for drilling activity. I have well types and start/end dates and I want to automate the output so it looks good for presentation purposes. Basically I would like to have one line per rig with a schedule of wells that will be drilled as colored blocks of cells (this is quite hard to explain, attached file might make it easier).
I already have a macro that displays and automerges the relevant cells for each job and rig, but unfortunately I have only been able to display it on different lines.
I need to find a way to consolidate those rows of data innto one single line, keeping the cells merged and the formatting.
Basically in my example I need to paste non empty cells from lines 11 to 12 to line 10, keeping merged and formatting properties

I have read the other posts related to this subject but have not found any reply that meets those particular requirements.

Would really appreciate some help

Many Thanks


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