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

Question

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


Configuration: Windows XP / Internet Explorer 7.0

venkat1926 · Answer

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.

venkat1926 · Answer

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.

Vino · Answer

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

venkat1926 · Answer

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.

Copy to cell below the last entry in Sheet 2

4 responses

Subscribe To Our Newsletter!