Excel VBA to copy data from one worksheet to another

tuks

New Member
Joined
Feb 9, 2022
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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!
 
How about
VBA Code:
If LCase(str1) = "yes" Then
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
You're welcome & thanks for the feedback.
 
Upvote 0
You're welcome & thanks for the feedback.
Hi mate,

Sorry to keep pestering you!

we had a little issue enabling this current spreadsheet to be shared on our network drive. Tried all sorts of solutions after searching the web with no luck so i decided to create a fresh new spreadsheet, copy the data over and the VBA code. All working fine however for some weird reason row 2 does not move over to my CG ready worksheet when the Macro button is pressed, all other rows move and delete from the original worksheet perfectly fine? Its just row 2 it seems to have a problem with. 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

i_last_row_tbl_CG_ready = Sheets("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, "AF").Value
If LCase(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

MsgBox "Moved data successfully.", vbInformation, "Success"
'MsgBox i_last_row_tbl_CG_ready

End Sub

Thank you in advance.
 
Upvote 0
Hi mate,

Sorry to keep pestering you!

we had a little issue enabling this current spreadsheet to be shared on our network drive. Tried all sorts of solutions after searching the web with no luck so i decided to create a fresh new spreadsheet, copy the data over and the VBA code. All working fine however for some weird reason row 2 does not move over to my CG ready worksheet when the Macro button is pressed, all other rows move and delete from the original worksheet perfectly fine? Its just row 2 it seems to have a problem with. 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

i_last_row_tbl_CG_ready = Sheets("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, "AF").Value
If LCase(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

MsgBox "Moved data successfully.", vbInformation, "Success"
'MsgBox i_last_row_tbl_CG_ready

End Sub

Thank you in advance

Ignore me! iv just figured the line below is referencing line 3 not 2! changed to 2 working fine now.
For int1 = i_last_row_tbl_Live_cases To 3 Step -1
 
Upvote 0
Glad you sorted it & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top