VBA Insert Copied Cells Shift Down
Sep 25, 2013
I'm using a macro to copy cells (2 columns and between 1 and 5 rows [19 - 23 on s/s]) from one workbook to another but if I use
Rows("2:2").Select
Selection.Insert Shift:=xlDown
then every cell in the inserted rows are populated.
this is what I currently have
Windows("agent master.xlsm").Activate
Range("B19:C23").Select
Selection.Copy
Windows("distribution master.xlsm").Activate
Sheets("L38").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
but that doesnt work either. It just gives me an empty cell in A2.
View 1 Replies
ADVERTISEMENT
Feb 6, 2009
If I put a length in (G13) such as 4.44 & position from (H13) is 1. Then I want that length of (G13) to move its value (4.44) into (C15) the 1st cell to add to is (C15)
Then Move (C15) to (C16) 11.12 becomes C16
and move (C16) to (C17) and so on all the way down the C column.
When it hits the last joint (C374) position 360. I want a messageBox that says you can not insert anymore.
Basically shift the existing values down untill (C374)
I have only column C to shift the calue from (G13)
I do NOT want to delete rows!
Below is how it looks before macro:
GH12LENGTHPOSITION134.441
C14LENGTH1511.121612.221711.561811.861913.112012.872113.062211.992312.03
Below is how it will look after macro: The RED moves to (C15)
C14LENGTH154.441611.121712.221811.561911.862013.112112.872213.062311.992412.03GH12LENGTHPOSITION134.441..........................
View 9 Replies
View Related
Jan 29, 2009
I want to be able to insert-shift cells down based on criteria in another cell. For instance, I would like a macro that would look at column B for saturday and sunday and then insert-shift cells down on the corresponding cells in column D...
View 9 Replies
View Related
Jul 23, 2009
I've been trying to solve this for days. In Excel 2007 there is a 'Insert copied cells' function after copying cells and right-clicking on a cell which basically just inserts all copied cells to your desired location.
My question is, how do you call this function in VB, I've used F1 extensively and searched the net and haven't come up with an answer.
View 2 Replies
View Related
Mar 29, 2014
Have you ever copy a row with formula in locked cells & insert it in a protected worksheet?
View 1 Replies
View Related
Sep 7, 2012
I am trying to create a macro that will add and delete individual cells depending on an IF statement. The code that I created deletes/adds cells regardless of the IF statement.
If Sheet1.Range("F3").Value = Sheet1.Range("G2").Value Then
Range("G2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End
ElseIf Sheet1.Range("F3").Value = Sheet1.Range("G4").Value Then
Range("G3").Delete Shift:=xlUp
End
End If
Also, what is the easiest way to have this code apply to all rows within column F, while adjusting the corresponding cells referenced in column G? The last row within the spreadsheet changes.
View 3 Replies
View Related
Jan 26, 2014
I don't know if a macro is necessary, but the built in Insert -> Insert copied cells -> Shift cells down function can't accomplish what I require.
I've pasted in two example tables for what I'd like to do. For the first table, the data in column C is a name and the cells from columns D-Z, rows 7-10 is what I want to copy and paste from row 12, column D; row 13, column D; etc, etc. However, I want the full rows below the name in column C to move down when I paste from column D. Using the Insert function on the Insert blade only gives me the data in columns D-Z moving down.
The second pasted table gives an example of how I want it to look.
A
B
C
D
E
F
G
H
I
[code].....
View 4 Replies
View Related
Aug 1, 2006
i have some columns which contain the IF function and so it returns me a value in each cell. Is there any way to copy only the values AND insert shift down the copied cells onto somehwere else?
View 3 Replies
View Related
Sep 14, 2009
I've been using the following macro ....
View 8 Replies
View Related
Aug 18, 2014
Any shortcut for "insert cut cells"? I want to paste and shift cells down instead of pasting over existing data.
View 1 Replies
View Related
Feb 26, 2013
I have a spreadsheet with 27 Columns and 439 rows of data. I need to copy each row of data that has a certain criteria and paste the same data 141 times below it and then manipulate the data. In the same spreadsheet I need to copy a row of data that has other criteria in it and past it 30 times below it, and then manipulate the data.
I have been using the copy and insert copied cells function, but I have to scroll down 141 or 30 rows each time to ensure I add in the correct amount of rows. Is there a more productive way to do this? I have about 10 workbooks with approximately 47 tabs/worksheets each that I will need to update in a similar fashion.
View 4 Replies
View Related
Sep 14, 2007
I wanna shift a particular column (lets say column A) to the left, so I do the following:
hojaFAC.Columns("A:A").Select
Selection.Insert Shift:=xlToLeft
hojaFAC is a worksheet
BUT I get the next error when I execute my macro :
RUNTIME ERROR 1004
Select method of range class failed
Why do I get runtime error nš 1004 all the time also for other reasons. I think itīs the only runtime error I ever jumped into. But If I ever get a runtime error It's allways number 1004.
View 4 Replies
View Related
Apr 19, 2008
One of the sheets in my workbook has 5 rows (Rows 1 - 5) that I need to copy and insert at the top of all but 3 of the other worksheets. I can specify the names of the 3 worksheets that I want to avoid changing. The other worksheets will be named differently each time I use the macro.
View 2 Replies
View Related
Apr 24, 2014
I have data in sheet1, which needs copied to sheet2
But in sheet2 already data is available. In Sheet2, i want code to go from header to down , to locate the first blank row in between data and to paste data by inserting same number of rows as number of rows copied.
View 3 Replies
View Related
Jan 23, 2007
I need to add a line on the top of a new worksheet with the data copied from another worksheets cells. Using a macro. The line has to be created everytime on the top of the new excel sheet with the previous data moving one down.
View 6 Replies
View Related
May 16, 2014
I have 4 rows that are on a "Resource" sheet need to be inserted into about 150 different variable locations across 20 different sheets. Here is the code I have so far the will copy it to one specific location (see below). I need making the macro insert these copied cells to which ever cell I currently have active. I'm still really new at VB programming so this probably something really simple to solve.
View 4 Replies
View Related
Apr 30, 2007
I'm currently trying to insert the copy range of one worksheet to another. Let's say I have 3 worksheets, one main and two additional info sheet. I want to search column C in main worksheet for blank cell and use column F in the same row as the worksheet name which I want to copy from, then use column E from main worksheet as the keyword for the range I want to copy(range from column E to column Z). But when inserting, I want the keyword in main worksheet such as 'G' in the same row as blank cell to match the 'G' in copied worksheet. Because the 'G' in main worksheet is not in the same column for each row, I try to search the forum but can't find the inserting part. I also attach the example workbook.
View 3 Replies
View Related
Dec 1, 2009
I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.
I have attached a screen shot of the current page,
In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.
The shift pay is the column i am having trouble with.
I would like it to change dependant on what is selected in the 'worked' column.
For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.
View 3 Replies
View Related
Apr 11, 2008
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!
examples:
start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333
start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000
start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000
Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................
View 8 Replies
View Related
Oct 20, 2013
with a macro. I am looking to copy row 2 to the last row and past the copied rows directly below the copied contents.
View 9 Replies
View Related
May 29, 2014
when I insert a row into the sheet called WBS - Explanation, it only copies over into columns A:D in my worksheet called Timeline & Key Tasks. Unfortunately, I need the whole row to be inserted in the Timline & Key Tasks worksheet.
I can see how the coding has been set up and why this is happening, unfortunately I don't have the knowledge to workout how to change it to the code to include the whole row without deleting information, which I don't want to do.
View 7 Replies
View Related
Nov 7, 2009
I have data in cells A1:FM1326 I want to delete all of the cells that say FALSE and shift the remaining cells up.
View 5 Replies
View Related
Feb 6, 2007
I have a problem in making a delete function/sub and i'm applying it to a listbox in userform ... i'm trying to delete a row of data in the listbox which refers to cells (A12:D12) ... and at the same row (row=12), there are other data cells (E12:H12) ... after deleting the cells all other data below the deleted cells will shift up taking over the deleted cells ...
View 9 Replies
View Related
Apr 30, 2009
how can i get this
Sheet1
ABCDEFGHIJKL7WTXMF31326154RIO TINTOAUDMLWSINBFSALE 258414.13 ML W/S INT BD FD 16-Mar-0917-Mar-09-251307.75N
View 9 Replies
View Related
Oct 20, 2009
Whenever "online" appears in the "Desc" column I need to shift the matching row down (shaded area). for example, "online" appears in H2, hence, C2:E2 need to be shifted down to C3:E3 leaving C2:E2 blank. Next, H3 has "online" there, C3:E3 need to be shifted down one row so the result will look like the data on the "result" tab.
View 5 Replies
View Related
Jan 13, 2012
I need to shift all the cells in columns B, C, D, E & F up a row to align with the names in column A.
I have code to delete the empty rows.
Start:
Bank Holiday 1 on a Week End
ABCDEF1YASMIN AKHTAR (YA1) 2 59038.753MARINA BELAIDI (MB2) 4 12325.55MARGARET CANT (MC6) 6 33002.25
Result Required:
Bank Holiday 1 on a Week End
ABCDEF1YASMIN AKHTAR (YA1)59038.752 3MARINA BELAIDI (MB2)12325.54 5MARGARET CANT (MC6)33002.25
View 3 Replies
View Related
Jan 30, 2013
I am trying to delete a group of four cells and have the remaining block shift up. My code is below and crashes on the red. What is the right code to get this to work?
Code:
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = Range("G1").Value Then
Range(ActiveCell:ActiveCell.Resize(0, 3)).Delete Shift:=xlUp
Exit Do
End If
Loop
View 1 Replies
View Related
Jul 1, 2007
I am looking for a macro that can delete whole rows (shift cells up then). How i would like it to perform is if there is nothing in cell(s) B then delete the whole row?
View 9 Replies
View Related
Dec 27, 2008
In the range A1:Y66, for every cell whose formula returns a 0, I want it to delete that cell and shift the cells up.
Here is the code I tried, but nothing happened:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 25 Then Exit Sub
If Target.Rows > 66 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = 0 Then Target.Delete Shift:=xlUp
End Sub
View 9 Replies
View Related
Jun 18, 2009
i need a button to copy the content of A8:C28 and paste in another section of the worksheet, i get the button to copy and paste the selected range, but when i copy and paste again it overides my current paste, i need it to paste my new selection underneath my previous paste.
View 9 Replies
View Related
