Free Microsoft Excel 2013 Quick Reference

Macro to Move Data Between Sheets Based on Columns

Hello all,

Need help writing a macro that will move data in one specific column and row to another sheet based on the header in row 1.

The columns that need to be moved are:

Library
Deal_Date
Title

At the end of sheet 1 (row 11 in this case), I need to move the data in sheet 2 into sheet 1, keeping the data across each row just as it is in sheet 2.

See sheet 1, then sheet 2, then after macro sheet in attached file:

I'd like to use the headers in row 1 as the unique identifiers for each column. The macro will need to identify the last row of data across all columns in sheet 1, then insert the data from sheet 2 in the corresponding columns, beginning with row 2 in sheet 2. Will need to loop through several thousand rows. Must keep data being moved from sheet 2 to sheet 1 in the same row.

Any help would be most appreciated - thank you!!

Question also posed at Mr. Excel: http://www.mrexcel.com/forum/showthread.php?t=531778


Post your answer or comment

comments powered by Disqus
macro to move data between worksheets, between Sheet 1 - 10

I'm new to this forum and new to excel, could anyone help me figure out how to move rows into sheets based on cell data.
example; I have a row of information and I want to move that row to a specific sheet based on the status of column R or E whichever it may be?
Thank you very much!

Good morning all. I am hoping you geniuses out there can assist me with what is hopefully a fairly easy query. I'm very new to macros so still struggling over even basic things...

I found a code which allows a user of a workbook to show / hide certain sheets based on a certain password that is entered when the workbook is opened. For example, if you enter in the password 'Dan', all the sheets will open. If you enter 'Chris', 2 sheets open and 2 sheets are hidden. If you enter 'Jon', one sheet is opened.

This is all well and good and functions. However, I'd like to compress the code somewhat as I am creating a workbook with 62 sheets, and don't want to have to write out each line sheets(store 1) all the way to store 62.visible=True/False or whatever. This will make the macro stupendously large.

So my question:
Is there any way I can write into this macro
a) a code that would open -all- the sheets in the workbook;
b) a code that would ONLY open -one- sheet and keep all others hidden;
c) a code that would keep all other sheets hidden EXCEPT 3 or 4 specified sheets.

I've tried playing around with Activeworkbook.Sheets.Visible etc but to no avail.

Here is a sample of the macro:


	VB:
	
 
Private Sub Workbook_Open() 
    Dim pword As String 
    pword = InputBox("Enter Your Password") 
    Select Case pword 
         'if a login is not used change to
         'pword = InputBox("Enter Your Password")
         'Select Case pword
    Case Is = "Dan": Sheets("Total Region").Visible = True 
        sheets("Store 1").Visible = True 
        sheets("Store 2").Visible = True 
        sheets("Store 3").Visible = True 
        sheets("Store 4").Visible = True 
    Case Is = "Chris": Sheets("Total Region").Visible = False 
        sheets("Store 1").Visible = False 
        sheets("Store 2").Visible = True 
        sheets("Store 3").Visible = True 
        sheets("Store 4").Visible = False 
    Case Is = "Jon": Sheets("Total Region").Visible = False 
        sheets("Store 1").Visible = True 
        sheets("Store 2").Visible = False 
        sheets("Store 3").Visible = False 
        sheets("Store 4").Visible = False 
    End Select 
End Sub 
 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    Dim sht As Worksheet 
    Application.ScreenUpdating = False 
    Sheets("Front Page").Visible = xlSheetVisible 
    For Each sht In ActiveWorkbook.Sheets 
        If sht.Name  "Front Page" Then 
            sht.Visible = xlSheetVeryHidden 
        End If 
    Next sht 
    Application.ScreenUpdating = True 
    ThisWorkbook.Save 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any input would be greatly appreciated... thank you very much!

Dan

I would like a macro to move data from B5 and look for the last empty cell on column D and enter it there and clear B5 for the next entry. I need the macro to fill column D cells from the bottom instead of inserting at the top.

Thanks

Hi,

I have been doing C + P for most of this data for a long time, however i was wondering if anyone knows a macro to compare data between 2 worksheets and return the data in a 3rd worksheet (copy data from the 2 worksheets + return the difference in the 5th column). The data has been trimmed down - but has about 200 rows and about 100+ columns

Attached is an example

Please Help

Many thanks

Master P

Hi All,

I am new to VBA and this forum, but have already learned a lot since joining!

I think there are a few ways to do what i'm trying to do and my first way may not be working. I want to copy/paste between different sheets based on a value in the row. For example, if I have these rows:

Company New/Exist Communication Status
Business1 New No contact
Business2 New No contact
Business3 New No contact

And then I update the communication status (which will happen for multiple rows), then I want to move it to a different sheet. The columns do not exactly align, so I'd paste each column at a time. There are also two other sheets, for "Contacted" and "Negotiating", so I want to move them into the appropriate sheet based on the Communciation status.

The code I have so far is as follows:


	VB:
	
 
Sub UpdateProspects_2() 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Application.StatusBar = "Working..." 
     
    Dim SetWbkName As String 
     
    Dim LRowProspects As Long 
    Dim LRowContacted As Long 
    Dim HRow As Long 
    On Error Resume Next 
     
    SetWbkName = ThisWorkbook.Name 
     
    Sheets("Prospects").Select 
    LRowProspects = Range("A1048576").End(xlUp).Row 
    HRow = Range("A8").Row 
     
    Sheets("Contacted").Select 
    LRowContacted = Range("A1048576").End(xlUp).Row 
     
    Sheets("Prospects").Select 
    Range("A8").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
     
    If Not ActiveSheet.AutoFilterMode Then 
        Selection.AutoFilter 
    End If 
     
     '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     
    ActiveSheet.Range(Cells(8, 1), Cells(LRowProspects, 13)).AutoFilter Field:=4, Criteria1:= _ 
    "2 - Interested" 
     
    Range(Cells(HRow + 1, 1), Cells(LRowProspects, 3)).Copy Destination:=Sheets("Contacted").Cells(LRowContacted,
"A").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 4), Cells(LRowProspects, 8)).Copy Destination:=Sheets("Contacted").Cells(LRowContacted,
"E").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 9), Cells(LRowProspects, 10)).Copy Destination:=Sheets("Contacted").Cells(LRowContacted,
"K").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 11), Cells(LRowProspects, 13)).Copy Destination:=Sheets("Contacted").Cells(LRowContacted,
"P").Offset(1, 0) 
     
    Sheets("Contacted").Select 
    LRowContacted = Range("A1048576").End(xlUp).Row 
     
    Range(Cells(7, 1), Cells(7, 45)).Copy 
    Range(Cells(7, 1), Cells(LRowContacted, 45)).PasteSpecial Paste:=xlFormats 
     
     'Delete raw data to save space
    Sheets("Prospects").Select 
     
    With Range(Cells(HRow + 1, 1), Cells(LRowProspects, 13)) 
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete 
    End With 
     
     '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ActiveSheet.ShowAllData 
     
    Application.StatusBar = False 
     
End Sub 
 
 
Sub UpdateProspects_3() 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Application.StatusBar = "Working..." 
     
    Dim SetWbkName As String 
     
    Dim LRowProspects As Long 
    Dim LRowNegotiating As Long 
    Dim HRow As Long 
    On Error Resume Next 
     
    SetWbkName = ThisWorkbook.Name 
     
    Sheets("Prospects").Select 
    LRowProspects = Range("A1048576").End(xlUp).Row 
    HRow = Range("A8").Row 
     
    Sheets("Negotiating").Select 
    LRowNegotiating = Range("A1048576").End(xlUp).Row 
     
    Sheets("Prospects").Select 
    Range("A8").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
     
    If Not ActiveSheet.AutoFilterMode Then 
        Selection.AutoFilter 
    End If 
     
     '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     
    ActiveSheet.Range(Cells(8, 1), Cells(LRowProspects, 13)).AutoFilter Field:=4, Criteria1:= _ 
    "3 - Negotiating Deal" 
     
    Range(Cells(HRow + 1, 1), Cells(LRowProspects, 3)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"A").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 4), Cells(LRowProspects, 4)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"E").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 5), Cells(LRowProspects, 8)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"X").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 9), Cells(LRowProspects, 10)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"R").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 11), Cells(LRowProspects, 13)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"AB").Offset(1, 0) 
     
     
    Sheets("Negotiating").Select 
    LRowNegotiating = Range("A1048576").End(xlUp).Row 
     
    Range(Cells(52, 1), Cells(52, 45)).Copy 
    Range(Cells(52, 1), Cells(LRowNegotiating, 45)).PasteSpecial Paste:=xlFormats 
     
     'Delete raw data to save space
    Sheets("Prospects").Select 
     
    With Range(Cells(HRow + 1, 1), Cells(LRowProspects, 13)) 
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete 
    End With 
     
     
    ActiveSheet.ShowAllData 
     
    Application.StatusBar = False 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My first attempt was to filter the data, copy the visible range, then paste in new tab. However, it works the first time, but usually not the second time. I dont know if its just the volatility associated with this method, in which case, maybe I'd have to sort/copy/paste?

Any help would be greatly appreciated!

Hello everyone,

I was wondeirng if someone could help me out with a Macro for copying data between Excel worksheets. I have been trying to get to grips with the programming but as it is years since I have done any of this, I think it may be well beyond me now! Things seem to have moved on a lot It's annoying because the logic is so simple!

I have a workbook with three worksheets, two containing data (COMM and COMM2) and one where I would like to collate and sort the data from both (SCHEDULE). I wanted a macro to run which would automatically populate this third sheet when run.

The data in COMM and COMM2 is virtually identical, each row contains data pertaining to one item and Column I is a date field. Some rows are empty though. COMM2 is slightly different in that it has some rows I would only like to copy if column F has the word 'Yes' in it.

What I would like to do is run a macro which looks at COMM and copies all the rows between 1 and 1000 (if they contain any data in Column I) to the worksheet SCHEDULE. Then to look at COMM2 and copy all the rows between 1 and 1000 (if they contain any data in Column I and have the word 'Yes' in Column F) to the worksheet SCHEDULE, continuing on from where COMM finished copying. I would then like to sort the data in SCHEDULE by Column I (between rows 8 and the end).

I hope this makes sense! I have tried to outline the logic below, putting in a few variables to give me a bit of flexibility and to avoid header text!

StartRow = 8
EndRow = 1000
SourceRow = StartRow
DestinationRow = StartRow

Repeat
	If (SourceRow:Column I on Worksheet COMM) <> 0
		Copy (SourceRow on Worksheet COMM) to (DestinationRow on worksheet SCHEDULE)
		Add 1 to SourceRow and add 1 to DestinationRow
	Else add 1 to SourceRow
Until SourceRow = EndRow

SourceRow = StartRow

Repeat
	If ((SourceRow:Column I on Worksheet COMM2) <> 0 and (SourceRow:Column F on Worksheet COMM2) = YES)
		Copy (SourceRow on Worksheet COMM2) to (DestinationRow on worksheet SCHEDULE)
		Add 1 to SourceRow and add 1 to DestinationRow
	Else add 1 to SourceRow
Until SourceRow = EndRow

(on sheet SCHEDULE sort rows 8 to (StartRow x 2) by Column I (ascending)

END
If anyone could help me out I would be so happy!

Thanks in advance,

Gareth

Hey, I'm wondering if anyone can help me with a macro to do this for me as doing it manually for my constantly changing data is a real hassle.

My data in this example is in the cells: A5 to C54 however in my main datasets its thousands of rows and about 80 columns wide.I need a macro to go to the "Control" sheet and look at the list of names what are currently in A3:A5 then match those names to the datasets in "Main" based on Column A in this example they are in cells: (A6, A15, A22, A27, A33, A40, A48)

When it finds a match it needs to move this dataset to the "Moved" sheet, in this example it moves three datasets, once it's moved the datasets the "Main" sheet should end up looking like what you see in G5: I31 but in it's original location (I DO NOT want it moving it over to the right as shown in this example.

Main: Sheet containing the data what needs to be moved

Moved: End location for where the data needs to be moved to...

Control: Location where we find a list of names to match.

Please see the attachment as it's far easier to see it visually than reading

Any help is appreciated
- Hyflex

Hi all! I'm stuck (Yawn... again? Yes.)

Attached is a spreadsheet to eventually generate invioces...

Monthly Data sheet:
This is where data is entered by individuals, they enter the number of hours they spend on a particular project in a particular month. This calculates the amount chargeable.

Invoice Data sheet:
This is a summary of all the costs that are invoicable.

I need a macro that transfers all of the invoiceable data from the Monthly Data sheet to the Invoice Data sheet in a list with no spaces. Column Y dictates whether or not a row is invoicable based on a number of criterea. The trouble i have is that rows and columns are added in order to add projects and staff respectively. I have tried to use Names (as you can see in the sheet) but struggle to use them in VBA. Anyone be of any help...

Hello ozgrid faithful,

I have been researching a VBA solution to a common problem, moving row(s) to a new sheet based on column criteria. I have found several responses on this forum that deal with this issue, however the VBA responses are too customized to the individual poster, and the number of unique criteria is too large to use an advanced filter (repeatedly). I'm hoping someone could provide generic VBA coding to the problem, which I have illustrated below:

A data table has 'x' number of columns and 'y' number of rows. Column A has attribute data that will determine which row(s) are copied to a new (VBA created) sheet, named after the attribute. The workbook will start with 1 sheet, and end with multiple (creating a new one each time the Macro is run).

I understand that a Pivot Table would similarly summarize this information, however I need to be able to electronically distribute a confidential list specific to each attribute (AA can't see AB's, AB can't see AA's, etc). I'm not sure how the code is setup, but please ensure that it is generic enough where the number of rows and columns can fluctuate (though column A will always contain the determining attribute).

I have attached a sample document with the criteria described above. Your help is greatly appreciated.

The solution will have 5 tabs ["Sheet1", "AA", "AB", "AC", "AD"], each with 7 columns. "AA" and "AB" will have 2 rows, "AC" and "AD" will have 1 row.

I have a sheet that has a store number in Column "H".
I would like this to take that information and create a seperate SAVED workbook based on the values of "H".

So, there are 12,ooo rows and it is sorted by store ID (number) in "H". I would like to have a spereate sheet created and saved with only that store data in it

store 1 has 1032 rows of data then it goes to store 2

this macro would create a seprate sheet based on store# and then close the workbook.

It takes HOURS for me to cut the data and save by store

could be saved anywhere on the C: drive

I have a worksheet that contains multiple records for different departments. The department name is not on the worksheet. The only way I have of knowing what data belongs to what department is by the name.

I have been using an autofilter to manually go through and copy and paste while I try to figure out the macro to do this for me. Column A is the only column that has mutliple records - the rest of the data is unique. My end result needs to be a separate worksheet based on each department.

The list of people who are in the departments are on a master worksheet. I need to reference this worksheet. Any help would be appreciated. I have attached what I am trying to achieve.

Hi, as my name implies I am new to VBA/Macros/automation

I have a single workbook with five spreadsheets. My goal is to move data along a path (or work flow) from one sheet to the next by using a "trigger" pulldown menu choice.

Sheet1 = Prospects
Sheet2 = New Sale
Sheet3 = Upgrade Sale
Sheet4 = Won
Sheet5 = Lost
Sheets 1, 2, and 3 use the same data layout for column A - K.
Sheets 4 and 5 have the same A - K as above and also have columns L - R in common.
The last column in sheet 1, 2 and 3 is a pulldown menu (New, Upgrade, Won, Lost).

Data rows on each sheet start at row 7.

The goal is to use the pulldown choice to remove the data from the current sheet (ex: Prospect) and add it to the next open row in the required sheet (ex: New or Upgrade). I also need to be able to make a similar move from New/Upgrade to Won or Lost.

I can do this manually, but don't know where to start to automate, rather eleminate the "copy/paste" which is my goal. Any help would be greatly appreciated.

Hi there

I am trying to carry out a relatively simply copy/paste type function between two sheets based on the following criteria:
- range to be copied is from sheet 1A, column G, used range only (from row 6 onwards)
- data is to be "pasted" onto sheet HA2, same row numbers as the data on sheet 1A. The destination column is based on looking up and finding the value "1" in row 6 between columns 9 and 213.

I've tried the following code, using a function defined below, but it doesn't seem to work...

For the lookup of the destination column, I've just tried to find the last (only) column with data....

Please bear in mind, I'm a VBA absolute beginner, and the code I've hatched together is from other code a friend has prepared for me!

Thanks in advance

Sub PostProgressWBS01()
 
Dim cel As Range
Dim rng As Range
'Dim PutCol As Long
Dim PutCol As Long
Dim LastRow As Long
    
    'Get column where data is to be copied to on Sheet HA2
    Set rng = Sheets("HA2").Range(Cells(6, 9).Address, Cells(6, 213).Address)
    PutCol = Find_LastCol_Data(rng)
    
    'Get last Row with DATA on Sheet 1A
    Set rng = Sheets("1A").Range(Cells(18, 7).Address, Cells(9999, 7).Address)
    LastRow = Find_LastRow_Data(rng)
     
    'do the thing
    For Each cel In rng
        If cel.Value <> "" Then
        Sheets("HA2").Cells(cel.Row, PutCol) = cel
        End If
    Next cel

    Set cel = Nothing
    Set rng = Nothing
    
    Application.CutCopyMode = False
End Sub


Function Find_LastCol_Data(rng As Range)

    ' Find the last column
    On Error Resume Next
    Find_LastCol_Data = rng.Find(What:="*", _
                After:=rng.Cells(1), _
                Lookat:=xlPart, _
                LookIn:=xlValues, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Column
    On Error GoTo 0
    
End Function

Function Find_LastRow_Data(rng As Range)

    ' Find the last row
    On Error Resume Next
    Find_LastRow_Data = rng.Find(What:="*", _
                After:=rng.Cells(1), _
                Lookat:=xlPart, _
                LookIn:=xlValues, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row
    On Error GoTo 0
    
End Function


Hello,
I need your help in preparing a Dash Board. I have an excel file with data pulled from external source & have lot of dump in it. I am looking for specific data that need to be pulled from that dump and saved in another sheet of the same file (as in example file).

In the Received Sheet:
1. It should prompt for the date and after entering the date (21- Jan in example file) it should fetch the data of Team A to Team E and paste it in Received Sheet based on Column L (Insert_time).
2. If this sheet is not available it should create a new one, if the sheet is already available then it need to clear the old data and paste the new one.

In the Closed Sheet:
1. It should prompt for the date and after entering the date (21- Jan in example file) it should fetch the data of Team A to Team E and paste it in Closed Sheet based on Column N (Close_Time) & Column U (Status of only “Closed”, “Closure Pending”, “Verified Closed”)
2. If this sheet is not available it should create a new one, if the sheet is already available then it need to clear the old data and paste the new one.

In Open Sheet:
1. It should fetch the data of Team A to Team E based on Column U (Status of only “Open”, “New”, “pending”)

I have attached the sample spread sheet for your reference. Sorry if my request is not clear.

Thanks in advance for your help!!

Regards,
Humac

Hi am Stuggling to work out how to set up this excel sheet. basicly i have created a sheet with excel which i want to use to help me remeber and log what i have down whilst taken calls in a call centre. there are basicly there i have created a drop down list for certain viables once i have done or not done some thing i want to be able to make it as done then save on another work sheet what i have recorded and reset the changed value back the way it was and change from call 1 to 2 ect logging each call . so it needs to move data from the main sheet to the next sheet and paste it into one row. then next time i click next call do the same but post it into the next row ect ect. the idea is i will be able to see how many times ive promoted a product and by it copying row by row which call i offered a product on ect

Here's the setup:

- Sheet 2 has two columns (A&B)
- Column A has the employee name
- Column B has was shift the employee works (Day, Twi, Mid, Sun)

I would like a macro to do the following:

In Sheet 1, I want a macro to populate column A with the names of all employees who work the Day sort based off the data in Sheet 2.

Any help would be appreciated. Thanks!

First Post!

I have a workbook with many worksheets, but I'm only concerned with 6 of the sheets for this macro. On the main page of the workbook I have a drop down menu with currently 2 choices (later it will be expanded to more). I have a cell that is a reference cell on that sheet that just shows "1" or "2" based on which choice the user chose.

If the user clicks the first choice the reference cell says "1" and I want a macro that makes "Sheet 1", "Sheet 2", and "Sheet 3" visible while "Sheet 4", "Sheet 5" and "Sheet 6" are hidden. If the user selects choice 2 I want the opposite to happen.

I've tried about 50 different things and none seem to work. I first made subs in a module that were the "Hide_SetA" that hid the first 3 sheets and unhid the others while "hide_setB" that did the opposite. and in the coding on my main page I had a sub that was supposed to run either of those other subs depending if that cell's value was "1" or "2"

more recently I've had coding like this in my main page based on some google searches

Private Sub
Worksheet_Change(ByVal Target As Range)
If Target.Address = "$N$17" And Target.Value = "1" Then
    Sheets("Sheet 1").Visible = xlSheetVisible
    Sheets("Sheet 2").Visible = xlSheetVisible
    Sheets("Sheet 3").Visible = xlSheetVisible
    Sheets("Sheet 4").Visible = xlSheetHidden
    Sheets("Sheet 5").Visible = xlSheetHidden
    Sheets("Sheet 6").Visible = xlSheetHidden
ElseIf Target.Address = "$N$17" And Target.Value = "2" Then
    Sheets("Sheet 1").Visible = xlSheetHidden
    Sheets("Sheet 2").Visible = xlSheetHidden
    Sheets("Sheet 3").Visible = xlSheetHidden
    Sheets("Sheet 4").Visible = xlSheetVisible
    Sheets("Sheet 5").Visible = xlSheetVisible
    Sheets("Sheet 6").Visible = xlSheetVisible
End If
End Sub
Any help would be appreciated as this has become rather frustrating. Thanks!

Hi

I have a list with a few hundred rows of data. I would like to write a macro that will copy each row into a different sheet based on the value in column A.

I'm having trouble writing a code that will go through each row and recognize the value in column A. Once I get there, I could use if statements to place it in the right row on each new page.

I was approaching it using the code below. However I don't know how to determine the row that the code is in while going through the loop. Maybe there's a different approach


	VB:
	
 Range 
 
For Each rRng In Worksheets("All").Range("A4:A" & _ 
    Worksheets("All").Range("A65536").End(xlUp).Row) 
     
    If ("Value in column A of that row = Value1") Then 
        copy To Worksheets("Value1") 
    ElseIf ("Value in column A of that row = Value2") Then 
        copy To Worksheets("Value2") 
    End If 
     
     
Next rRng 

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

Thanks

I apologize for the weak Title but I don't know how to describe my issue. I know how to record macros and it's really helped me with my job but when it comes to customizing the code, I'm still learning. Here's my situation. I create the attached workbook daily(sheet1) and each day it will be different in the number of rows but the consistancy is all the extraneous stuff I'm trying to delete. I've been just sorting everything and deleting manually but I want to write a macro to speed this up and make it look like sheet2. I recorded this macro to do a custom autofilter based on criteria that is consistant with every report I run but it deletes all the data as well as the extraneous stuff I want gone. The other sorting and summation I can handle. I just can't tell it to delete specific rows because that will change daily depending on the data. I need it to hunt out that extra stuff and get rid of it. Can anyone help? Thanks!!

I'm using this formula to move data between worksheet but the data moves to same line on the new worksheet that it was on on the master even if there are open cells above it. Is there a way to have it move to the next open cell on the new sheet.

=IF('NEW MASTER'!D3="t",'NEW MASTER'!E3)

I have a spread sheet with 10 columns with an auto filter on row 2

The filter criteria is based on column 10 with the criteria1:="Yes"

Sheet name = Existing

I need to copy all rows that have Yes written in Column 10 to a new worksheed called "Existing completed"

Data starts being entered from Row 3, Row 1 and 2 are headings that are in both worksheets.

And i need it to do this when ever a button is clicked

any ideas on how i achieve this???

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!

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.


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