Excel VBA to copy data from one worksheet to another

[tuks]

Hi there,

Newbie to VBA here, I have a VBA code where I can copy specific cells to a second worksheet when cell "AG" specifies "yes" which is all working fine. The cells I need copying over all transfer over fine but for some reason it is not copying it to the next available row, instead it is copying to which seems the middle of the worksheet (around row 300). Even if the second worksheet is completely empty (just headings on row 1) it will still copy to the middle.

Code below:

Sub s_move_completed_Live_cases_to_CG_ready()
Dim i_last_row_tbl_Live_cases As Integer, i_last_row_tbl_CG_ready As Integer
Dim i_curr_row_tbl_Live_cases As Integer
Dim int1 As Integer
Dim str1 As String

Sheets("CG ready ").Select
i_last_row_tbl_CG_ready = Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Live cases").Select
i_last_row_tbl_Live_cases = Cells(Rows.Count, "A").End(xlUp).Row

For int1 = i_last_row_tbl_Live_cases To 3 Step -1
str1 = Sheets("Live cases").Cells(int1, "AG").Value
If str1 = "yes" Then
i_last_row_tbl_CG_ready = i_last_row_tbl_CG_ready + 1
Sheets("CG ready ").Cells(i_last_row_tbl_CG_ready, "A").Value = Sheets("Live cases").Cells(int1, "A").Value
Sheets("CG ready ").Cells(i_last_row_tbl_CG_ready, "B").Value = Sheets("Live cases").Cells(int1, "B").Value
Sheets("CG ready ").Cells(i_last_row_tbl_CG_ready, "C").Value = Sheets("Live cases").Cells(int1, "C").Value
Sheets("CG ready ").Cells(i_last_row_tbl_CG_ready, "D").Value = Sheets("Live cases").Cells(int1, "D").Value
Sheets("CG ready ").Cells(i_last_row_tbl_CG_ready, "E").Value = Sheets("Live cases").Cells(int1, "E").Value
Sheets("CG ready ").Cells(i_last_row_tbl_CG_ready, "F").Value = Sheets("Live cases").Cells(int1, "F").Value
Sheets("CG ready ").Cells(i_last_row_tbl_CG_ready, "G").Value = Sheets("Live cases").Cells(int1, "G").Value
Sheets("CG ready ").Cells(i_last_row_tbl_CG_ready, "H").Value = Sheets("Live cases").Cells(int1, "H").Value
Sheets("CG ready ").Cells(i_last_row_tbl_CG_ready, "I").Value = Sheets("Live cases").Cells(int1, "i").Value
Sheets("CG ready ").Cells(i_last_row_tbl_CG_ready, "J").Value = Sheets("Live cases").Cells(int1, "J").Value
Sheets("CG ready ").Cells(i_last_row_tbl_CG_ready, "O").Value = Sheets("Live cases").Cells(int1, "K").Value
Sheets("CG ready ").Cells(i_last_row_tbl_CG_ready, "P").Value = Sheets("Live cases").Cells(int1, "L").Value
Sheets("CG ready ").Cells(i_last_row_tbl_CG_ready, "Q").Value = Sheets("Live cases").Cells(int1, "M").Value
Sheets("CG ready ").Cells(i_last_row_tbl_CG_ready, "T").Value = Sheets("Live cases").Cells(int1, "AC").Value
Sheets("CG ready ").Cells(i_last_row_tbl_CG_ready, "U").Value = Sheets("Live cases").Cells(int1, "AD").Value
Sheets("Live cases").Rows(int1).EntireRow.Delete
Else
End If
Next int1
End Sub

Any help would be much appreciated!

 

[Joe4]

Welcome to the Board!

So it sounds like you are having issues with this calculation:

Sheets("CG ready ").Select
i_last_row_tbl_CG_ready = Cells(Rows.Count, "A").End(xlUp).Row

(which by the way can be simplified to)

i_last_row_tbl_CG_ready = Sheets("CG ready ").Cells(Rows.Count, "A").End(xlUp).Row

You can add a MsgBox to see what that value is being calculated to, i.e.

MsgBox i_last_row_tbl_CG_ready

Is that value returning the value you are expecting?
If not, check out what is in column A on that row on your sheet.
Do you perhaps have some formula or a space or something?

Also, is there really a space at the end of the sheet name, "CG Ready ", or is that a typo?

 

[Fluff]

Hi & welcome to MrExcel.
It sounds as though you have cells in col A on the "CG ready " sheet that are not totally empty.

 

[tuks]

Hi & welcome to MrExcel.
It sounds as though you have cells in col A on the "CG ready " sheet that are not totally empty.

Thank you very much! I cleared all contents in Col A, seemed to have done the trick even though there was no data in there!

Very much appreciated

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[tuks]

Welcome to the Board!

So it sounds like you are having issues with this calculation:

Sheets("CG ready ").Select
i_last_row_tbl_CG_ready = Cells(Rows.Count, "A").End(xlUp).Row

(which by the way can be simplified to)

i_last_row_tbl_CG_ready = Sheets("CG ready ").Cells(Rows.Count, "A").End(xlUp).Row

You can add a MsgBox to see what that value is being calculated to, i.e.

MsgBox i_last_row_tbl_CG_ready

Is that value returning the value you are expecting?
If not, check out what is in column A on that row on your sheet.
Do you perhaps have some formula or a space or something?

Also, is there really a space at the end of the sheet name, "CG Ready ", or is that a typo?

Hi, thank you for your reply.

It seemed there were cells in Col A that were not totally empty as Fluff suggested. Seems to be working fine now.

Again appreciate your reply

 

[tuks]

Glad we could help & thanks for the feedback.

Could i pick your brain for another matter.

I want to send reminder emails to members of staff when certain dates are met. For example i have 3 dates, 1 reminder sent after 2 weeks of the first date, second reminder after 4 weeks of 2nd date and lastly third reminder after 5 weeks of 3rd date. All 3 dates will be on 1 row which would all need to be sent to a single staff member whose email i was thinking of having in a cell on each specific row, each row will have different staff members email addresses.

What would be the best way to implement this?

 

[Fluff]

As that is a totally different question, it needs a new thread. Thanks

 

[tuks]

As that is a totally different question, it needs a new thread. Thanks

No problem, thanks

 

[tuks]

No problem, thanks

Hi,

Re the code above, at the moment if we type 'yes' into column AG it transfer my data to another worksheet. How can i get it to accept both lowercase 'yes' and uppercase 'Yes'?

Thanks