Free Microsoft Excel 2013 Quick Reference

auto cut and paste...

hi guys , this is my problem:

i got 5 worksheets in a workbook and now i am creating another worksheet that

is the summary of all the 5 worksheets.The 5 worksheets are named 1-5 and

the formats are the same. Now, instead of transfering the data from each sheet

and do a "cut and paste" job i was thinking if the datas in worksheets 1-5 can

be automatically transfered to "summary" based on the last entry of each

sheet.

pls advise.....


I have created a macro within a file which needs to go to data-filter-auto filter, then autofilter to a specific number. From there I wish to grab the first 20 rows. Unfortunately it may be rows 23, 45, 69, 83 (in that order since its sorted) that I need and their sequence is subject to change on a month to month basis.

The cut and paste seems flawed as I am not returning the right data within the macro.I need to find a way to tell excel, "Grab the first 20 rows I sorted, despite the row number".

Here is my code at this point:

Selection.AutoFilter
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Selection.AutoFilter Field:=12, Criteria1:="100%"
Rows("23:42").Select
Selection.Copy

Any help would be appreciated!

I have created a macro which cuts and pastes a few separate rows which are separated by category. Unfortunately the macro tends to miss rows as the data set changes on a monthly basis. Take the first row selection as an example, after I autofilter data may be in row 2 or 3 which was not in the previous month. Is there a way to set a wildcard to grab all the data in the auto filter so that nothing is ever missed? Would it be as simple as:
Rows("1:18000").Select for each worksheet I cut and paste into?

Cells.Select
Range("D1").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="1"
Rows("4:16879").Select
Selection.Copy
Sheets("Intrusion").Select
Range("A22").Select
ActiveSheet.Paste
Sheets("Item Summary").Select
Selection.AutoFilter Field:=2, Criteria1:="2"
Rows("93:17248").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Fire").Select
Range("A22").Select
ActiveSheet.Paste
Sheets("Item Summary").Select
Selection.AutoFilter Field:=2, Criteria1:="3"
Rows("6:19671").Select
Application.CutCopyMode = False
Selection.Copy

I am new to Scripts and have been playing with the script below.
The data will be entered and when leaving the last cell I want it to cut and copy into another sheet then I want it to sort the second sheet.
So Far I can get the sort OK and the Cut but when it selects the second sheet I get an error..
Any help would be great.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Application.Intersect(Worksheets("INPUT - Instructions").Range("J2"), Target) Is Nothing)
Then
       doSort
    End If
End Sub
Private Sub doSort()

    Worksheets("INPUT - Instructions").Range("Input").Select
    Selection.Cut
    Worksheets("NEW Handicap System").Range("Output").Select
    Selection.Paste
    Range("Completed").Select
    
    Worksheets("NEW Handicap System").Range("B1:J10001").Sort Key1:=Worksheets("NEW Handicap
System").Range("B1"), Order1:=xlDescending, _
    Key2:=Worksheets("NEW Handicap System").Range("B1"), Order2:=xlDescending, Header:=xlYes
    Worksheets("NEW Handicap System").Select
   
End Sub


How can I get Excel 2003 not remove the gridlines after a cut and paste? I
have to manually replace the gridlines after each cut and paste. Can Excel do
this automatically?

I have a set of Data that I pull from Access and export to Excel. Each day is a new set of Data. The columns are "Manger" "Date" "Week" "LaborHours" "OperationsComplete."

My task is to auto filter on each manager, there are 25 of them. Copy all of the information from that day and Paste it into each Manager's Personal Workbook; they then add their own information to the data I have presented them.

They are then to manually input "CompleteTime" "QualityRemarks" "EmployeeName".

Each day I append another set of Data under the already added data from previous days, without tampering with prior data.

I have tried a macros that cuts and paste, but the macros always pastes in cell a1 and overlaps the previous days' data.

I have also tried a macros that that pastes to the last cell of the worksheet, then deleting blank cells in between, this was also a failure.

I am using Macros recorder, I have no idea how to write VBA.

Could anyone please help me with this problem? Thank You.

Hi, the xls workbook Test3 is located in a specific folder on a memory stick, drive letter unknown. The single sheet collects data in the range C6:H35. The data in each row of 6 cells is strung together in cell A6 (say) and the value only of cell A6 stored in B6. Range B6:B35 is then exported to range A2:A31 of a csv file, Data_Import3. Both files then need to be saved and closed.

Cell A1 in the csv file must always be empty.

Straightforward you'd think, but after experimenting with many coding variants from other contributors, I still can't get this to work.

Depending on the code used failure occurs as follows:
1) Out of Range errors during cutting and pasting; and/or
2) If cut and paste works, when csv file is opened, data in Col A has been shifted up by one cell, meaning A1 is no longer empty. (the code below hangs during cut/paste).

Any pointers would be gratefully welcomed.


	VB:
	
 CommandButton2_Click() 
    Dim relativePath1 As String 
    relativePath1 = ThisWorkbook.Path & "" & "Test3.xls" 
    Application.DisplayAlerts = False 
    ActiveWorkbook.SaveAs Filename:=relativePath1 
    Application.DisplayAlerts = False 
     
    Range("C6").Select 
    ActiveSheet.Unprotect Password:="open" 
     
     '   Create new .csv file
    Workbooks.Add 
    Dim relativePath2 As String 
    relativePath2 = ThisWorkbook.Path & "" & "Data_import3.csv" 
    Application.DisplayAlerts = False 
    ActiveWorkbook.SaveAs Filename:=relativePath2 
    Application.DisplayAlerts = False 
     
     
     '   Remove sheets 2 & 3 for proper save
    Sheets("Sheet3").Select 
    ActiveWindow.SelectedSheets.Delete 
    Sheets("Sheet2").Select 
    ActiveWindow.SelectedSheets.Delete 
    ActiveWorkbook.SaveAs Filename:=relativePath2, FileFormat:=xlCSV, CreateBackup:=False 
     
    Set sourceBook = Workbooks("Test3.Xls") 
    Set SourceRange = sourceBook.Sheets("PatDat").Range("B1:B35") 
    Set destBook = Workbooks("Data_import3.csv") 
    Set destRange = destBook.Sheets("Data_import3.csv").Range("A1:A35") 
    SourceRange.Copy destRange 
     
    ActiveWorkbook.SaveAs Filename:=relativePath2, FileFormat:=xlCSV, CreateBackup:=False 
    ActiveWindow.Close 
     
    ActiveSheet.Protect Password:="open" 
    ActiveWorkbook.Save 
    Application.Quit 
     
     
End Sub 

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

So I have data that comes in with each row being a different instance. Each row has data in the same columns. My problem is that some of the data needs to be cut and pasted one row below a summary row (which is below the rest of the data with 1 row separating the data and the sum row). There is sometimes no data that needs cut and some times a lot - all rows that contain a specific keyword in the B column need to be cut and moved.

Do you know how I can do this using VBA? I would like to make it so it does it until all are removed, then stop without erroring.

Thank you!

I have a column with messed up data.
It is column A.

Messed up data says:
Stupid Text Here 02/16/2007

I do not want the date thier, i want it to be cut and pasted to column B. The year is always 2006 or 2007.

I messed around but dont know how to look at certain parts of a cell for data...any help ?


	VB:
	
 Test() 
    Dim cl As Range 
    For Each cl In Range("A1", Range("A65536").End(xlUp)) 
        If cl.RightValue = "2006 or 2007" Then 
            cl.Offset(0, 1) = trim right 10 characters 
        End If 
    Next 
End Sub 

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


While browsing for some code I came accross a usefull link (which I have inserted below) which allow you to cut and paste from a textbox.
This works beautifully,however I would like to be able to cut and paste to and from a combobox.
I have tried to vary the code but with no luck.
Is this possible and if so could anyone point me in the right direction.

http://word.mvps.org/FAQS/Userforms/...tClickMenu.htm

New to this so bear with me,

i have the following code, the selection is pasted to the new worksheet ok, but only the figures not the whole format of the file. So all figures are mixed together is it possible to have an exact cut and past, if so where have i gone wrong?

	VB:
	
Range("a1:n44").Copy 
Set wbopen = Workbooks.Open(Filename:="z:CommproddataLine1.xls") 
Application.Goto Sheets("Sheet1").Range("a65536").End(xlUp).Offset(1, 0) 
ActiveCell.PasteSpecial Paste:=xlValues 
wbopen.Close Savechanges:=True 
Application.ScreenUpdating = True 

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

Richard

Beginner macro writer attempting to build non-beginner macro. Hope I'm not in over my head.
I'm looking to build a macro module that will find and cut all rows that have the same column value. The worksheet this would cut and paste from would have a list of SSNs in column A and other values in columns B, C, D... Column A would be sorted in assending order- so all SSNs of the same value would appear together. Since same SSN appears multiple times in the worksheet. I'd like to be able to have the macro identify all rows that contain the same SSN, then cut these out of one sheet and paste them into another. I'd like this done one SSN at a time. Any help with this would be appreciated- including you recommending a good guide to building Excel macros book.

Yuletide felicitations to all

I've done a search but to no avail so please can someone help on this New Year's Eve?

Column "A" contains numbered text and I need to scour the column and if the number is less than zero (negative) then I need to cut and paste the corresponding cells in both column "A" and "B" and paste to "C" and "D".

It's driving me mad as it only seems to cut and paste the cell below the one that I want.

Many, many thanks in advance
Reg

I've had trouble with the users of a program I wrote cutting and pasting information rather than follow the procedure of clicking a button to do that for them - in comsequence they ruin the formatting of the sheet! Correcting the resulting mess is a bit of a pain!

Is there any way, using VBA, of preventing the cut and paste method of copying data.

Help appreciated

Dear all,

I am trying to cut and paste an object (an excel shape to be more precise) into its same position. It could, for example, be relative to the top left corner, but any help on how to modify the paste method will be welcome.

Surely this is a piece of cake for most of you, but I haven't been able to find the answer for objects floating on the screen.

Thanks in advance,

Ares

Here is my problem probably pretty easy. I have to show an example I dont know how to explain it. I have this spreadsheet
8/13/2005 08/13/2005 M 16-5-23 SIMPLE
3 4943562 ABBOTT KEVIN S
6/26/2006 06/26/2006 T 40-5-121 DRIVING
6/26/2006 06/26/2006 T 40-2-8 NO
6/26/2006 06/26/2006 T 40-6-49 FOLLOWING
4 2929481 ABDO SAM 06/23/1974
12/7/2005 12/22/2005 M CR-GOT VIO.OF
5 8387540 ABDULLAH AHMAD R
5/12/2006 08/07/2006 F 16-8-18 ENTERING
10/30/2005 11/08/2005 F 16-8-18 ENTERING
6 8593639 ABELL JOCK T
5/17/2006 06/17/2005 M 16-11-44 MAINTAINING
5/17/2006 05/17/2006 M 16-13-2 POSSESSION

I want to move the rows that begin with date and cut and paste them at the end of the row that begins with a number.

I have a spreadsheet with 30,000 rows and four columns. The cells contain text strings as well as commas and numbers. I need to locate specific text at the left side of the cells in column B, cut it from the string and place it in an empty cell five columns to the right. I then need to look for a string of text and numbers at the right side of the cells in column B and then cut and paste that six columns to the right. I need it to search from the top of the column all the way to the bottom. Also would like to be able to specify the search string via a dialog box.

Hey, advice and help is extremely appreciated to my pathetic vba skills...i'll try to illustrate my prob below:

A B C

Above are three columns A, B and C where i have my info. the formula in cell C is (A-B). i need to be able to cut and paste the values of C onto A (thus deleting values in column A and replacing it with those in C) but keep the formulas in C intact so that it will recalculate new values. I can't seem to do it in the first place and it being a circular reference just makes it worse!

I have two separate worksheets in which one has the new up-to-date information but needs two columns from the other worksheet.

But the problem is i cannot just cut and paste the two columns into the new worksheet, the columns information has to go to the correct rows in the new worksheet. This is because each row has unique information which are position differently in the old worksheet and in the second worksheet.

For example:

In row 27 in the old worksheet their is:

Column A: ABC
Column B: abcd
Column C: 1
Column D: £1.00
Column E: £0.50
Column F: 45%

But in the new worksheet columns A to D are in row 57, i need to put the columns E and F from the old worksheet to the new one into row 57.

The columns needed are E and F.

Please help!!!

Thanks

Mini12

Hello, oh wise gurus of the forum!

I'm working on coming up with a few Macros which can assist with some of the monthly billing worksheets utilized by my company. There are a whole lot of different things that need to be done to prepare, plus a whole other set of steps that need to be done to actually fill in all billing info.

To prevent this from being a huge post outlining every different thing that needs to be done, I would like to post several different ones (maybe one post per every couple of days or so) until I've addressed each process individually. I'm still learning the company's billing process along the way, as well, so please bear with me with each of my posts.

That being said, I come to you with what I hope will be a fairly easy one at first.

Column A lists a product ID code. Column B lists the product name.

In column C is the beginning inventory for the previous month. Columns D & E are additions and subtractions that occurred to the inventory for each product for this month. Column F is the ending inventory for the month.

Hence, column F = C+D-E

Each of the columns C, D, E, and F have a SUM formula in the last row.

Pretty easy so far, right?

I'm seeking a Macro that can "prep" these columns each month for our inventory changes in columns C and D to be entered to present new values in F. Put a little more simply, the Macro should:

1. Take the value of F (NOT the formula) and copy it into C.
2. Put a "0" in every cell of D and E.
3. End this process when it reaches the row that has the SUM formulas.

I have been pretty successful in doing 1. and 2. above, but I'm having trouble telling it where to stop cutting and pasting. It would be ok if there were a set number of products, as I would simply cut and paste the range of cells in F and copy into C. However, the number of products change from month-to-month. For one month, there may be 30 products. For the next, there may be 27, or 35 or 40, or any other amount.

Can anyone help with this one?

Thanks so much for your time and your assistance!

Michael

#ref in a linked cell (with formula in it), when i copy/cut and past in original cell

i have an order entry sheet that feeds a invoice sheet...

the invoice sheet has formulas that refer to the order entry sheets data (e.g., quantity)

when i move data around (cut/copy and paste) as i change/modify the order, my formulas in the invoice sheet turn to #ref...

?what can i do to stop the #ref...?

thank you.

Hi Need help. Fairly new to macros and VBA.
Looking to create two macro buttons that will cut and paste an entire row based on a selection listed in column N. The copied (or cut) row is to be posted to the next available row in the worksheet matching the selection, and delete the blank row from the source worksheet.

I want the row cut and pasted from the "Funnel" worksheet to the "Dead" worksheet if column N has "Dead" selected, and a similar macro for "Closed" selection to the "Closed" worksheet.

This is a carrier thread.

Wayne9

Macro Cut and Paste

I have a spreadsheet with several columns of data and hundreds of rows. Column A contains an account number. I would like macro code that will cut all rows with a specified account number, say 123456 and paste these rows into sheet2.
Can someone help me please?

-----------------------------------------------------------------------------

pangolin

see if this code helps


	VB:
	
 ctpste() 
    Dim myrange, mycell As Range 
    Sheets("sheet1").Select 
    Set myrange = Sheets("sheet1").Range("A1", Range("A65536").End(xlUp)) 
    i = 0 
    For Each mycell In myrange 
        If mycell.Value = "123456" Then 
            i = i + 1 
            mycell.EntireRow.Cut Destination:=Sheets("sheet2").Range("A1").Offset(i, 0) 
        End If 
    Next mycell 
End Sub 

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


Hey,

I need to be able to write a macro that will cut and paste data from one worksheet into a new worksheet but when it pastes the data, i want it to be copied into the free cells next to the previous data. I can create the macro to do the cut and paste, but dont know what to write in the macro when i want to paste to a new cell each time.

So for example i paste some data from book 1 into column 2 of book 2. Then i close book 1, open book 3 and cut paste some data from book 3 into column 3 of book 2. I just need a macro to recognise that the column is not empty, so therefore it needs to paste to the next free column.

Any help would be greatly appreciated!

Cheers,

Rhys

Hi all, I have this great conditional copy and paste macro from this site. Does anyone know hoe to make it a cut and paste marco?

	VB:
	
 Cond_Copy() 
     
    Application.ScreenUpdating = False 
    ReturnSheet = ActiveSheet.Name 
    Set AreaRange = ActiveCell.CurrentRegion 
    Set MyRange = Intersect(ActiveCell.EntireColumn, AreaRange) 
     
     '   Define area that matches selected cell value
    x = ActiveCell.Value 
    For Each Cell In MyRange 
        If Cell.Value = x Then 
            If i = 0 Then 
                Set NewRange = Cell.EntireRow 
            Else 
                Set NewRange = Union(NewRange, Cell.EntireRow) 
            End If 
            i = i + 1 
        End If 
    Next 
    Set NewRange = Intersect(NewRange, AreaRange) 
     
     '   Copy & Paste
    Sheets(2).Select 
    Cells.ClearContents 
    Range("A2").Select 
     
    NewRange.Copy 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
    Range("A1").Select 
     
    Sheets(ReturnSheet).Select 
    Application.ScreenUpdating = True 
     
    MsgBox ("See Sheet2") 
    Sheets(2).Select 
     
End Sub 

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