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

Free Microsoft Excel 2013 Quick Reference

Macro to move row to another worksheet

Hi! I have a workbook with two worksheets in it. The first "TO DO" and the second "Completed". In "TO DO", I have rows of tasks starting in row 4 (row 3 is my header), going to 200. What I'd like to have happen is when I put a "C" in column C, it moves the entire row to the "Completed" worksheet, greys it out and removes it from the "TO DO" worksheet. Then if I remove the "C" from the "Completed" worksheet, it moves it back to the bottom of the list. I already have a macro to resort it based on "priority" in column E.

Is this even possible???

As always, thanks! You all are so awesome!!!


Post your answer or comment

comments powered by Disqus
I need help with a macro to move rows to different worksheets. For example, If I put in Delivered, I want the row moved to a "Delivered" worksheet. If I put in Declined, I want the row moved to a "Declined" worksheet. I have been able to successfully get one of these to work at a time, but I need both of these to work in a single Macro. Help anyone? Here's the code I used to move one of them.

Option Explicit
Dim Flag As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row

If Flag = True Then Exit Sub
If Not Intersect(Target, Range("B2:B100" & LR)) Is Nothing Then
If Target.Value = "Delivered" Then

LR = Sheets("DELIVERED").Range("A" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy
Sheets("DELIVERED").Range("A" & LR).PasteSpecial
Flag = True
Target.EntireRow.Delete
End If
End If
Application.CutCopyMode = False
Flag = False
End Sub

/close

Macro to move rows, row by row, from one worksheet to another worksheet based upon specific criteria.

I need a macro that I can apply (to a rectangle click) to match and move rows from one work sheet to the corresponding row in another worksheet.

Conditions:
-Column A "Location" = Values are Unique
-All other Columns = Values are NOT unique
-A list of Contents is added to the worksheet "PullSheet"
-The "Get Pull Sheet" rectangle is clicked

1. The first, only the first, matching contents row in the worksheet "MasterLog" is moved (copied and then deleted, so it cannot be pulled again, from "MasterLog") to the corresponding row in worksheet "PullSheet".
2. If the MasterLog's "On Hold" column's value is "True", it does not recognize this row as an option to be moved to PullSheet.
3. Then goes row by row (Item by Item) doing the same thing.

The top 3 criteria are very important to me. If it is possible the next 2 would be very helpful as well, but not necessary.

4. If the Contents row in "PullSheet" does not have a match in "MasterLog" it should be highlighted Red.
5. If the Contents Row in "PullSheet" matches a (Location) that is a "Z?????" it should be highlighted Green after being moved to "PullSheet"

I have attached a sample workbook to see more clearly what I am referring to. Thank You All.

macro to move data between worksheets, between Sheet 1 - 10

I'm in need of a macro that moves rows based on which salesman's invoice it is to a seperate sheet.

I want the first sheet to be sorted by salesman and have the results of each salesman on their own seperate sheet.

I attached a test workbook to this.

Thanks!

Please help me,
I have a problem with this macro to move value to other specify sheet

please see attached file..

Thanks.

Help!
I'm trying to create a macro to cut a row from one worksheet and move it to an archive work sheet.
I've used macro record to cut and past but when I cut and past another row, it wipes out the first one I archived. This is what I have so far

Sub archive()
'
' archive Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Rows("2:2").Select
Selection.Cut
Sheets("Archive").Select
Rows("2:2").Select
ActiveSheet.Paste
Rows("3:3").Select
Sheets("work orders").Select
Selection.Delete Shift:=xlUp
End Sub

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

I am having difficulty recording a macro that will move rows of completed work to the bottom of my job tracker spreedsheet. I have a list in column I that has a complete option. Once I complete a task I'd like it's entire row A:O to move to the bottom, but stay in order of date. Even if i have to have a specific start point for my data that then gets organized in accending date order. That would help too.

THANKS!

I want to create a macro to move files from one server to another.
Basically, I'd want to move anything that has a .XXX at the end. I want to
move the files as listed in the active workbook and determine the number of
files to move based on the info stored in column A.
For i = 2 To Cells(Rows.Count, "A").End(xlUp).row

Next i

The directory location is stored in column B of my workbook.
(Y:abcfile.xxx)

Can someone assist?

Thanks,
Barb Reinhardt

I have a worksheet (Open) with thousand of rows.

Column A – Submission Date
Column B – Overdue Date
Column C – Customer Name
Column …….
Column Z – Status (the status code to be input is 01, 02, 04, 05… to 12 and may be a combination e.g. 02,12 or 02, 03 ,12.

My action is to move rows with status code 04, 05,11, 12 or the combination (incl. 04, 05, 11, 12) input to another workbook (Closed). So I would like to know if it is possible to write a vb to do following

1) Move the rows with status code, 04, 05, 11, 12 or the combination (incl. 04, 05, 11, 12) to the workbook (Closed) - second row.
2) When I run the vb second time or so on, it will do the same movement to the workbook (Closed) but appended to a blank row.

Many thanks

Hi everyone,

This is my first post, so bear with me.

I currently work with a 4,000 line excel sheet daily that pulls error codes out of a processing system. Everyday I need to remove columns, create tabs, rename tabs, delete items out of column C, delete items out of column F and move rows from column G to the corresponding tab. Examples: I would delete anything in Column C with a prefix of CR next I will delete anything in column F that has ICAR in the name and lastly I will move certain descriptions from column G to a new tab. I've tried recording a macro to do this, but since the information changes everyday, I need a better way.

Thanks for any help you can provide.

Ill try to explain best I can.

Recently found (finally..) a macro to auto create worksheets named after whats in Column A (5 digit number). I have one master worksheet and now 47 sheets titled with each ID from column A.

Now I have 47 rows with information on the master worksheet and 47 empty worksheets.

How do I put each row into each worksheet? ... Worksheet one will contain row 2, worksheet 2, row 3 etc etc.

I should also mention that row 1 is the header(?) says the titles ...that should be copied in addition to each row.

make sense?

I am trying to figure out how to use a macro to move the data that is stacked up under the company name to the same row that the name is in.

So, move data from C1R2,R3,R4,R5 into C1R1,C2R1,C3R1,C4R1 and C5,R1.

The attachment has a few lines of the data for an example.

I have tried it on my own, done some searches, but macros don't like to work for people that don't know what they are doing.

Thanks for any help.

Hi,

I need a macro to update chart ranges every month, the concept is simple, I need to show in my chart always the last 3 months, but I can't figure how to do that kind of macro to move the range down on my table.

I tryied to record something with the macro recorder, but all it does is move the range down from rows 2, 3 and 4 to rows 3, 4 and 5.
Next month I'll need to move from 3, 4 and 5 to 4, 5 and 6.

HTML Code: 
Sub Macro1()
'
' Macro1 Macro
' Macro gravada em 2/3/2008 por !
'

'
    ActiveSheet.ChartObjects("Gráfico 1").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).XValues = "=Plan1!R2C1:R4C1"
    ActiveChart.SeriesCollection(1).Values = "=Plan1!R2C2:R4C2"
End Sub


I am using the following procedure to move rows in a large text file from one sheet to another if it meets two criteria. However, this procedure is very slow. Can anyone suggest a way to improve speed by using something other than a loop? Filtering and copying maybe?
Sub MSP3()

' Filters to show only IP claims on first sheet and OP and Subprovider
' claims on second sheet

    Dim j As Long, n As Long, Counter As Long
    Dim PctDone As Single
    Dim SaveAsFname
    
    j = Range("A65536").End(xlUp).Row
    
    n = 2
    
    Counter = 0
    
    Do
    
        If Val(Left(Cells(n, 4), 2)) = 11 And CStr(Mid(Cells(n, 1), 3, 1)) = "0" Then
        Else
            Rows(n).Cut Sheets(Left(ActiveSheet.Name, 7) & "OP and Subs") _
                .Range("A65536").End(xlUp).Offset(1, 0)
            Rows(n).EntireRow.Delete
            n = n - 1
        End If
        n = n + 1
        
        Counter = Counter + 1
        PctDone = Counter / (j - 1)
        With frmProgress2
            .FrameProgress.Caption = Format(PctDone, "0%")
            .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        End With
        DoEvents  ' the DoEvents statement is responsible for the form updating
    
    Loop Until IsEmpty(Cells(n, 1))
    
    Unload frmProgress2
    
    Range("A1").Select
    
    Application.ScreenUpdating = True
    

' Saves as an Excel file

    SaveAsFname = Application.GetSaveAsFilename(Left(ActiveWorkbook.Name, _
        Len(ActiveWorkbook.Name) - 4) & ".xls", _
        "Microsoft Excel Workbook (*.xls),*.xls", , "Save As")
        
    ActiveWorkbook.SaveAs SaveAsFname, xlNormal, , , False, False

End Sub
TIA

Jason

is there a keyboard shortcut that enables the user to move from one worksheet
to another (within Excel) without using the mouse?

This report spreadsheet is exported from SAP. Since SAP has a limit number of displaying the columns, there are 40 headings (= 40 columns) in the report has been broken down to 4 rows of headings. How to use macros to move 3 those extra rows to one row and the number still matches each column? I also attatch the file for reference. Very appriciate any help.

Hello Experts,

I need a macro to convert rows to columns with reverence to a cell value in column A. For example, I have a huge data like below

549 Peter
549 Peter
549 Bob
54B sam
54B sam
54D Mike
54D Mike
54D Mike
54F tony
54F tony
551 tony
551 tony
553 tony
553 tony
555 mike
555 mike
555 tom
555 tom
557 john
557 John
557 john
557 Peter
557 Peter

I want to move the data of B2 to C1 if A2=A1 and B3 to D1 if A3=A1 and so on.. till next cell value of A is not equal to A1 and start with new cell again. Finally it should be like below.

549 Peter Peter Bob
54B sam sam
54D Mike Mike Mike
54F tony tony
551 tony Tony
553 tony Tony
555 mike Mike tom tom
557 john John John Peter Peter

Thanks in Advance
Lavan

I need help designing a macro that can take data that comes in looking like this...

A - B - C - D - E - F - G
123 - 5 e. main st. - Anytown - IL - 60635 - emp1 - john smith1
123 - 5 e. main st. - Anytown - IL - 60635 - emp2 - john smith2
123 - 5 e. main st. - Anytown - IL - 60635 - emp3 - john smith3
123 - 5 e. main st. - Anytown - IL - 60635 - emp4 - john smith4
123 - 5 e. main st. - Anytown - IL - 60635 - emp5 - john smith5
321 - 1 e. main st. - Anytown - IL - 60635 - emp1 - john smith6
321 - 1 e. main st. - Anytown - IL - 60635 - emp2 - john smith7
321 - 1 e. main st. - Anytown - IL - 60635 - emp3 - john smith8
321 - 1 e. main st. - Anytown - IL - 60635 - emp4 - john smith9
321 - 1 e. main st. - Anytown - IL - 60635 - emp5 - john smith0

and moves and filters it so that it looks like this...

H - I - J - K - L - M - N - O - P - Q
- - - - - emp1 - emp2 - emp3 - emp4 -emp5
123 - 5 e. main - town - IL - zip - smith1 - smith2 - smith3 - smith4 - smith5
321 - 1 e main - town - IL - zip - smith6 - smith7 - smith8 - smith9 - smith0

So in the initial column F, the employee #s 1-5 will be constant throughout every set that comes in. Each set is 5 rows, and the number of sets varies from day to day. So I need the macro to move the data from the first 5 rows, and then the next 5 rows after that, and the 5 after that one, etc... basically until there is no more data (upto the blank row)

I have a basic "Copy this, paste it transposed here and advanced filter this and paste it here" kind of macro, but as it stands now, it repeats the code 60 times so it can handle 60 sets of data. As you can imagine, this is very slow.

If you need to see that code, i can post it, but i'd rather start from scratch on this one.

Thank yoU!

Good Morning

With the help of others I have managed to complete a Working Macro to move Rows from One Workbook to Another and place it in the first open Row of the secord workbook. The Macro then leaves the inserted row with Cell L copied to the clipboard.

Here is code that it working for me adequatly to get that job done

	VB:
	
 OpentoSold() 
     '
     ' OpentoSold Macro
     ' Macro recorded 2/1/2008 by Mike
     '
     ' Keyboard Shortcut: Ctrl+q
     '
    Dim objLastRow As Range 
    Dim lastRow As Integer 
     
    Selection.Cut 
    Windows("Amazon Open.xls").Activate 
     
    Windows("Amazon Sold.xls").Activate '
     
     
    Set objLastRow = ActiveSheet.Cells.SpecialCells(xlLastCell) 
     
    lastRow = objLastRow.Row + 1 
     
    Rows(lastRow).Select 
    ActiveSheet.Paste 
     
    Range("L" & lastRow).Select 
     
    Selection.Copy 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can I add code to this Macro to paste that cell's text into an ALREADY OPEN word document named AmazonSale.Doc , AT THE PLACE WHERE THE CURSOR WAS LEFT when that word doc was last on screen ? When I do this manually and I reactivate the Word Doc to Ctrl+V the text, the cursor does still remain active where it was last left.

Thanks For Reading

HI, I have limited VBA knowledge. I have a very large worksheet where I need
a macro to delete rows that are blank. The cells look blank but have
formulas in them. Does anyone have a macro to do this. Also, I have three
worksheets in this file and the one I'm working with is called
"Implementation".

Thanks,
Cathy

I am just learning to write macros, and I am trying to write a macro to
work on several sheets in the same workbook. My goal is to trigger the
macro from a button on sheet one, then have the macro display rows 2
through 15 on sheet 2 if they are hidden, and then to hide rows 10-12
on sheet 2, and then do the same thing on sheet3. Here is the way it
now stands;

Sub Macro1()
'
' Macro1 Macro
'
With Sheets("Sheet2")
Range("A2:A15").Select
Selection.EntireRow.Hidden = False
Range("A10:A12").Select
Selection.EntireRow.Hidden = True
End With
With Sheets("Sheet3")
Range("A2:A15").Select
Selection.EntireRow.Hidden = False
Range("A10:A12").Select
Selection.EntireRow.Hidden = True
End With
End Sub

The button works great at triggering the macro. However, the result of
the macro is that it displays rows 2-15 and then hides rows 10-12 (As I
wanted it to do), but it affects sheet 1 only. I did not even want it
to affect sheet 1. It seems to have no affect on the other two sheets.
How can I make it function on other sheets instead of sheet 1? What
have I done wrong? (I am working in Excel 2000 by the way).

Thanks in advance for any help you can provide.

Bob Q.

For the following part of the code in my workbook I need to modify the macro to run through every worksheet and perform the same copy paste function. Right now the macro only runs on the JP Morgan worksheet. I would still like all of the data to be consolidated onto the JP Morgan worksheet starting in B6.


Sub CombineData()

Dim C As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim R As Long
Dim Rng As Range
Dim RngEnd As Range
Dim Wks As Worksheet

Set Wks = ActiveSheet

R = 6
FirstCol = Wks.Columns("G").Column
LastCol = Wks.Cells(6, Columns.Count).End(xlToLeft).Column

Set Rng = Wks.Range("B6")
Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd))

Rng.ClearContents
Application.Calculation = xlCalculationManual

For C = FirstCol To LastCol Step 2
Set Rng = Wks.Cells(6, C)
Set RngEnd = Wks.Cells(Rows.Count, C).End(xlUp)
If RngEnd.Row >= Rng.Row Then
If R + Rng.Rows.Count > Wks.Rows.Count Then
MsgBox "Aborting - not engough rows for data."
Exit For
End If
Set Rng = Wks.Range(Rng, RngEnd).Resize(ColumnSize:=2)
Wks.Cells(R, "B").Resize(Rng.Rows.Count, 2).Value = Rng.Value
R = R + Rng.Rows.Count
End If
Next C

[code/]

I have a worksheet with 1 column of random sequences and maybe 800 rows.
can you help with a macro to add row above and ">" sequential #.
Example:

actga
gttcg
cccttt

would like it to display:

>1
actga
>2
gttcg
>3
cccttt

I am a beginner. I was able to add rows, and >1, but did not know how to get it to # sequentially. Please help!!!


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