Vba to determine last row containing data and copy the data to another sheet

[kenny9002]

Dear All,

Please I need help with a vba code that will enable me to coipy data from one worksheet to another.

1. The code should be able to determine the last row with data on a worksheet named 'EMP'.

2. Some of the data on that row should then be copied to certain specific cells in another worksheet named 'OUTPUT' - as follows:

a) Data in Column C (EMP sheet) copied to Cell C3 of (OUTPUT sheet)
b) Data in column B (EMP sheet) copied to Cell C13 of OUTPUT sheet.
c) Data in column A (EMP sheet) copied to Cell C18 of OUTPUT sheet.
d) Data in column H (EMP sheet) copied to Cell I15 of OUTPUT sheet.
e) Data in column F (EMP sheet) copied to Cell I17 of OUTPUT sheet

I would also need a 'PRINT' code that would enable me to Print out the OUTPUT sheet (the Print Area is A1:I26).

Please I need to add that the data in column C of EMP sheet is 'name of customer' which is picked up using Data Validation. As mentioned in (c) above, this data is copied to Cell C18 of OUTPUT sheet.

Although I do not have any problems with inserting a data validation drop-down list. I thought I should mention it in case there is a special tweaking of the code that will copy that particular data (from drop down list) to another worksheet.

Thank you in anticipation of your help.

Kenny

 

[JLGWhiz]

Hi Kenny,
In response to private message:
The probable reason no one has replied to your post is that the parameters for copying and pasting appear to be ambiguous. The way it is written, it looks like you want to copy the entire columns and then paste them to a certaing cell on the ouput sheet, which would lead to automatic error messages for trying to paste to an area smaller than the one copied. You need to clarify what in columns A, B, C, H and F of sheet 'EMP' is to be copied. Remember that unless you post a screen shot or a link to a share server, people who respond to your post cannot see your worksheets. So, you must give clear parameters for what you want the code to do. If you are copying data from single cells or a range of cells in those columns, specify which cell, or range of cells in each column need to be copied. It would be pure guesswork to try and write code to the parameters as they are currently stated.

 

[kenny9002]

Hi JLG,

Thanks for your response and clarification. That's very kind of you. I now understand that my initial post was somewhat vague.

Meanwhile, I have uploaded a sample of the file on Box.net and here is the link:

https://app.box.com/s/mkayn60zfj48t618b8yp

Also to clarify issues (and looking at the sample file), what I am asking for are as follows:

1. a code that will determine the last row with data on the worksheet named 'EMP', and

2. some of the data on that last row should then be copied to certain specific cells in another worksheet named 'OUTPUT' - as follows:

a) Cell 1 of the last row (i.e.in Name column) copied to Cell C18 of 'Output' sheet
b) Cell 2 (in the Payee column) copied to Cell C13 of 'Output' sheet
c) Cell 3 (in the Ref column) copied to Cell C3 of 'Output' sheet
d) Cell 6 (in the Amount3 column) copied to Cell I17 of 'Output' sheet
e) Cell 8 (in the Amount5 column) copied to Cell I15 of 'Output' sheet

As I indicated in my initial post, I would also need a 'PRINT' code that would enable me to Print out the OUTPUT sheet (the Print Area is A1:I26).

I hope that with the uploaded sample file and the clarification above, I have presented my request clearer and easier to work on. I look forward to getting help.

Thank you very much for everything.

Kenny

 

[JLGWhiz]

Sub cpyNprnt()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long
Set sh1 = Sheets("EMP")
Set sh2 = Sheets("OUTPUT")
lr = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
With sh2
    .Range("C18") = sh1.Cells(lr, 1).Value
    .Range("C13") = sh1.Cells(2, 2).Value
    .Range("C3") = sh1.Cells(3, 3).Value
    .Range("I17") = sh1.Cells(6, 6).Value
    .Range("I15") = sh1.Cells(8, 8).Value
    With .PageSetup
        .Orientation = xlLandscape
        .PrintArea = "A1:I26"
    End With
End With
sh2.PrintOut
End Sub

 

[kenny9002]

Thanks JLG for your response.

I have tried the code that you provided. Unfortunately, it is not working quite right. When I activate the macro with the two rows of data, only the 1st and 3rd lines of the code work i.e. these lines:

[SIZE=3][FONT=Arial][COLOR=black][FONT=Arial].Range("C18") = sh1.Cells(lr, 1).Value
[/FONT][/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Arial][COLOR=black][FONT=Arial].Range("C3") = sh1.Cells(3, 3).Value[/FONT][/COLOR][COLOR=#222222][FONT=Times New Roman]<o:p></o:p>[/FONT][/COLOR][/FONT][/SIZE]

The rest of the lines do not copy across to the 'Output' sheet.

And when I added data to the third row of the EMP sheet, and tried the code, only the 1st line of the code was able to copy across OK. The rest of the lines did not either copy across at all. Where something was copied across, it was the wrong data that was copied across. In future, data would have to be added to the next blank row, and the code should pick the data from the last row with data and copy across to the 'Output' sheet. It is not doing that correctly now. Could you kindly check it for me to determine what might be wrong.

Is it also possible to achieve the following:

1) Separate the Print part of the code so I could have a separate button for it.

2. Secondly, when the Output sheet is printed, the cells copied across to the Output sheet should be cleared i.e. made blank until the code to copy across is activated again, and the Print button is clicked again.

3. The print orientation should please be 'portrait' and not landscape.

Thanks for all your help.

Kenny

 

[kenny9002]

Dear JLG,

In the course of fiddling with the code, and also trying to learn, I have managed to get the copying across working. To do so, I had to alter the lines as follows: I replaced the first of the numbers in brackets with 'lr'. So instead of (2,2) or (3,3), etc. I replaced them with (lr,2), (lr, 3) etc. and it worked.

I also made an attempt to take the print aspect out of the other part of the code. I got the main part of the code working. However, my 'Print and clear macro' is not working. Here is a separate 'Print & Clear macro' that I have used:

[COLOR=#000000][FONT=Arial]Sub printclear()<o:p></o:p>[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]   Sheets("OUTPUT").PrintOut <o:p></o:p>[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]   Range("C3", "C13", "C18", "I17", "I15").ClearContents<o:p></o:p>[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]End Sub<o:p></o:p>[/FONT][/COLOR]

But for some reason beyond me, when I click on the Print/Clear button, I get an error message as follows:

"Compile Error: Wrong number of argument or invalid property assignment".

I'll be grateful for any help with this.

Thanks very much.

Kenny

 

[JLGWhiz]

Dear JLG,

In the course of fiddling with the code, and also trying to learn, I have managed to get the copying across working. To do so, I had to alter the lines as follows: I replaced the first of the numbers in brackets with 'lr'. So instead of (2,2) or (3,3), etc. I replaced them with (lr,2), (lr, 3) etc. and it worked.

I also made an attempt to take the print aspect out of the other part of the code. I got the main part of the code working. However, my 'Print and clear macro' is not working. Here is a separate 'Print & Clear macro' that I have used:

[COLOR=#000000][FONT=Arial]Sub printclear()<o:p></o:p>[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]  Sheets("OUTPUT").PrintOut <o:p></o:p>[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]  Range("C3", "C13", "C18", "I17", "I15").ClearContents<o:p></o:p>[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]End Sub<o:p></o:p>[/FONT][/COLOR]

But for some reason beyond me, when I click on the Print/Clear button, I get an error message as follows:

"Compile Error: Wrong number of argument or invalid property assignment".

I'll be grateful for any help with this.

Thanks very much.

Kenny

Too many quotation marks. Should be:

Range("C3, C13, C18, I17, I15").ClearContents

 

[kenny9002]

Thanks JLG. It's all working OK now. You've been very kind. I appreciate all your help.

Kenny

 

[JLGWhiz]

Thanks JLG. It's all working OK now. You've been very kind. I appreciate all your help.

Kenny

You're welcome,
regards, JLG