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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,771
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
If LCase(str1) = "yes" Then
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,771
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

tuks

New Member
Joined
Feb 9, 2022
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

tuks

New Member
Joined
Feb 9, 2022
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,771
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Forum statistics

Threads
1,176,265
Messages
5,902,239
Members
434,953
Latest member
matthiasarnbert

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
Top