Excel VBA to copy data from one worksheet to another

T

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!
 
Sort by date Sort by votes
How about
VBA Code: 
If LCase(str1) = "yes" Then
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
You're welcome & thanks for the feedback.
 
Upvote 0
Fluff said:
You're welcome & thanks for the feedback.
Click to expand...
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
tuks said:
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
Click to expand...

Ignore me! iv just figured the line below is referencing line 3 not 2! changed to 2 working fine now.
tuks said:
For int1 = i_last_row_tbl_Live_cases To 3 Step -1
Click to expand...
 
Upvote 0
Glad you sorted it & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question Question
Comments on macro to help solve the problem of Excel changing column widths
2
Replies
11
Views
337
JenniferMurphy
JenniferMurphy
C
  • Question Question
Move Completed cells auto using VBA when they have been changed to completed
Replies
5
Views
396
dmt32
D
R
  • Solved
VBA moving rows
2
Replies
14
Views
966
Joe4
Joe4
D
  • Question Question
Generating Reports in Word Using Data in Excel
Replies
1
Views
338
Dan_W
D
M
  • Question Question
Copied sheet code to new file and lost some functionality....not sure how/why
Replies
5
Views
90
MeaclH
M

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

Share this page

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