Copy to cell below the last entry in Sheet 2 [Solved/Closed]

Report
-
 Vino -
Hello,

I need to do the following
1.I have assigned a Macro button called Submit in Sheet 1 - done
2.Copy Cells E5,E7,E9 from Sheet 1 to Cells B3,C3,D3 in Sheet 2 (when the Macro button is pressed)- done
3. Last Step - When I enter new values in cells E5,E7,E9 in Sheet 1 and press the Submit Macro button , I want them to be copied in cells B4,C4,D4 (cells below the last entry cells) - Not done.

I need help only with the third step. I have not used much of VBA programming in the past. Your help will be much appreciated

Regards,

Vino


4 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
suppose in sheet2 B3,C3 and D3 are filled with data. then to get B4,C4,D4

use these code statements in the appropriate place in the macro


dim j as integer
for j=2 to 4
cells(rows.count,j).end(xlup).offset(1,0).pastespecial
'<whatever you have copied and parked in the clipboard it will paste it in B4>
'<after the macro steps to nextj then it will loop back and as J becomes 3
'it will be copied in column 3 that is C4
next  j



remember this works on the active sheet that is the sheet where the data is to be pasted. so you must activate that sheet in the macro. I am sure you have done it. But be careful this activation si there before this loop

those lines with single apostrophe are only instructions
those without single apostrophe are the codes.(four lines of code -beginning with
dim
for
cells
next
If there is problem post the whole code.
Hi Venkat,

Thanks for the reply. I tried your code but it is not working. I have pasted the whole macro of mine below (including your stuff). Could you please let me know what mistake I have made. Note: I copied only 2 cells from Sheet 1(Data Entry) to Sheet 2 (QA Inspections).

Sheets("QA INSPECTIONS").Select
Dim j As Integer
For j = 2 To 4
Cells(Rows.Count, j).End(xlUp).Offset(1, 0).PasteSpecial
Next j
ActiveCell.FormulaR1C1 = "=('Data Entry'!R[2]C[3])"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=('Data Entry'!R[4]C[2])"
Range("B3:C3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Awaiting your reply,

Vino
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
try this experiment
take a new workbook. copy the macro"test" given below in the vbeditor in a module
in sheet 1 type

E5 1
E7 2
E9 3

now run the macro and see what happens in sheet2

now change the numbers in E5,E7 E9 ins sheet 1
now again run the macro test and see what happens in sheet 2

is this what you want

if this is what you want copy the macro in your file (BUT CHANGE THE NAMES OF THE SHEETS)

the macro is


Sub test() 
Dim j As Integer, k As Integer 
k = 5 
j = 2 
Do 
With Worksheets("sheet1") 

If k > 9 Then Exit Do 
.Range("E" & k).Copy 
  End With 
With Worksheets("sheet2") 
.Cells(Rows.Count, j).End(xlUp).Offset(1, 0).PasteSpecial 
End With 
k = k + 2 
j = j + 1 
Loop 
End Sub 



now about your mistake in your macro


see this statment

Cells(Rows.Count, j).End(xlUp).Offset(1, 0).PasteSpecial


now consider what to copy . there is no "copy" statement before. that is the mistake

your pastespecial statement is correct. But those follwing "pastespecial" are default . for e.g. only when transpose is true than you give that. Othereise transpose:=false is dafault. that mean if you do not give transpose argument it takes it that transpose is false. Am I clear.
Thanks mate,

It works. Thanks a lot. I would be more grateful if you could explain it to me each step as I am still learning VBA codes. It will be much beneficial for me.

Thanks again. Wish to hear from you soon. I owe you one.

Regards,

Vinodh
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
Vino
you wanted some explanations of the macro. I tried to give it below. may be it is not exhaustive . If you have any question you are free to post your question either in this newsgroup or to me by email



Sub test() 
Dim j As Integer, k As Integer 
k = 5 
j = 2 
Do 
With Worksheets("sheet1") 

If k > 9 Then Exit Do 
.Range("E" & k).Copy 
`the dot in the beginning is necessary because it is with funcion
`first k=5 so it copied E5 
`in the next loop k=k+2-see below-k becomes 7 and E7is copied etc
  End With 
With Worksheets("sheet2") 
`now you go to sheet2
`j is 2 that is column B. that is the specified column to be pasted
.Cells(Rows.Count, j).End(xlUp).Offset(1, 0).PasteSpecial 
`the above gives the last entered row plus one row in the specified `column. Note the variable j in the cells(    ) function above 
`in the next loop j becomes 3 that is column C etc. 
End With 
k = k + 2 
j = j + 1 
Loop 
End Sub 


With statement is used you need not activate sheet1 and sheet2 alternatively
As a beginner you can activate sheet1 and sheet 2 alternatively and be careful to put this activate statements at the correct place

For e.g.

Do
Worksheets("sheet1").activate
`remember sheet1 is activesheet 
Range("E" & k).copy ` the dot is necessary because  you have activated.
Worksheets("sheet2").activate
`remember sheet 2 is active sheet you can paste the copied data.
`remember sheet2 is active sheet is active sheet . You have to activate sheet1 for copying `next cell.  but when you go to next loop.  `that is next to Do sheet1 is activated.
k = k + 2 
j = j + 1 
Loop 

You can attempt on it and after some trial and error you will get the hang of it.
Greetings. Practive makes you an expert.

If you are going to do lot of vb macros you can get a good book( perhaps by Walkenback on excel programming with reference to your version of excel.
Hi Venkat,

Thanks for the explanation. It is much helpful. Could you please send me your email ID so that we can be friends?

Hope to hear from you soon.

Regards,

Vinodh