How to determine last row and copy data from one sheet to another sheet

C

ciecie66

New Member
Joined
Sep 23, 2012
Messages
5
I have a spreadsheet that is attached to a database. The first sheet has the data that is to be copied into a second spreadsheet. The values in columns C through I have to be copied and repeated in the second spreadsheet repeatedly. The data in these columns are to be used to store the data into the database from the second spreadsheet. This application is a payroll type application and the data should be copied for each Employee Number,Business Area etc. The other columns which start at column (AW) have the monthly calculations per employee. Each section has different accounts that are pulled based on properties for each employee and business area combination. So for the other columns of data I have to copy the differing account numbers and the monthly data that is to be sent back into the database by employee. So that the monthly data and the accounts are lined up with the repeating rows of the Employee Number,Business Area, etc. The complexity comes in because the data in the first spreadsheet could have multiple thousands of rows and columns of data and I need to figure out how to determine the last row of data in each section that is copied from the first spreadsheet into the second one.

Example: For the Dental/Medical section in the first spreadsheet starts at column (C10:I29) which is (Employee Number,Business Area,Paytype,CostCenter,Company,Department and profit center). This is the first section that has to be copied into the second spreadsheet starting at column (A5:G54). Once that is copied over I need the data in columns (AW10:BO29) copied over to the second spreadsheet. But I need the data for the FIN Account column (AW10:AW29) in the first spreadsheet to be pasted to Columns (H5:H54) in the second spreadsheet and the monthly data for dental/medical columns (AW10:BO29) in the first spreadsheet pasted into columns (J5:AA54) in the second spreadsheet. Once that is done I need the data for the next section to be copied from the first sheet into the second sheet the same way along with the repeat of the data in columns (C10:I29) the Employee Number,Business Area,Paytype,CostCenter,Company,Department and profit center. But I need to figure out dynamically what the last row of data that was copied from sheet 1 into sheet 2 so that the next section of data will not copy over the first section of data. This has to occur for multiple employees and accounts so there could be thousands of rows of data to be copied and moved to the second spreadsheet. Any help would be appreciated. The workbook was too big for me to post them in the same workbook. So I separated them into two workbooks but in the solution both spreadsheets will be in the same workbook. I am Using Excel 2007.
Example of Columns C-I
EMPBAPTCCCOMPDPTPC
E_1BUSINESS AREA 1PT_ExPatUSCOST CENTER 1COMPANY 1DEPARTMENT 1PROFIT CENTER 1
E_2BUSINESS AREA 2PT_ExPatUSCOST CENTER 2COMPANY 2DEPARTMENT 2PROFIT CENTER 2
E_3BUSINESS AREA 3PT_ExPatUSCOST CENTER 3COMPANY 3DEPARTMENT 3PROFIT CENTER 3
E_4BUSINESS AREA 4PT_ExPatNonUSCOST CENTER 4COMPANY 4DEPARTMENT 4PROFIT CENTER 4
E_5BUSINESS AREA 5PT_ExPatUSCOST CENTER 5COMPANY 5DEPARTMENT 5PROFIT CENTER 5

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Example of DentMed Calculation Section Columns (AW-BO)
FIN ACCOUNTDENTMEDDENTMEDDENTMEDDENTMED
650120$126.96$126.96$126.96$126.96
650120$126.96$126.96$126.96$126.96
650120$957.63$957.63$957.63$957.63
650120$412.00$412.00$412.00$412.00
650120$957.63$957.63$957.63$957.63


<colgroup><col><col span="4"></colgroup><tbody>
</tbody>


<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

Example of Combined:
EMPBAPTCCCOMPDPTPCFIN ACCOUNTDENTMEDDENTMEDDENTMEDDENTMED
E_1BUSINESS AREA 1PT_ExPatUSCOST CENTER 1COMPANY 1DEPARTMENT 1PROFIT CENTER 1650120$126.96$126.96$126.96$126.96
E_2BUSINESS AREA 2PT_ExPatUSCOST CENTER 2COMPANY 2DEPARTMENT 2PROFIT CENTER 2650120$126.96$126.96$126.96$126.96
E_3BUSINESS AREA 3PT_ExPatUSCOST CENTER 3COMPANY 3DEPARTMENT 3PROFIT CENTER 3650120$957.63$957.63$957.63$957.63
E_4BUSINESS AREA 4PT_ExPatNonUSCOST CENTER 4COMPANY 4DEPARTMENT 4PROFIT CENTER 4650120$412.00$412.00$412.00$412.00
E_5BUSINESS AREA 5PT_ExPatUSCOST CENTER 5COMPANY 5DEPARTMENT 5PROFIT CENTER 5650120$957.63$957.63$957.63$957.63

<colgroup><col><col><col><col><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>
E_1BUSINESS AREA 1PT_ExPatUSCOST CENTER 1COMPANY 1DEPARTMENT 1PROFIT CENTER 1650160$18.53$18.53$18.53$18.53
E_2BUSINESS AREA 2PT_ExPatUSCOST CENTER 2COMPANY 2DEPARTMENT 2PROFIT CENTER 2650160$18.53$18.53$18.53$18.53
E_3BUSINESS AREA 3PT_ExPatUSCOST CENTER 3COMPANY 3DEPARTMENT 3PROFIT CENTER 3650160$65.47$65.47$65.47$65.47
E_4BUSINESS AREA 4PT_ExPatNonUSCOST CENTER 4COMPANY 4DEPARTMENT 4PROFIT CENTER 4650160$163.67$163.67$163.67$163.67
E_5BUSINESS AREA 5PT_ExPatUSCOST CENTER 5COMPANY 5DEPARTMENT 5PROFIT CENTER 5650160$65.47$65.47$65.47$65.47

<colgroup><col><col><col><col><col><col><col><col><col span="5"></colgroup><tbody>
</tbody>
The section in bold is the second set of calculations that should be copied it is the Long Term Insurance Calculations. I tried to line them up accordingly. I need to figure out how to get the last row of data that was copied for the DentMed section so that when I copy the Long Term Insurance Section to the second sheet it will not over write any part of the DentMed section. Any help would be greatly appreciated.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Hi Ciecie66,

Welcome to the Board...

To determine last row using VBA is

Code: 
Lastrow = ActiveSheet.UsedRange.Rows.Count
 
Upvote 0
Thank you for the quick reply but I am new to VBA how do I use this in the code? I used the macro recorder to record the copy vba for the first section and I was going to repeat it for the other 18 sections of calculations. But how do I get the code you supplied to work? Do I put it in at the beginning of the code or do I put it in at the end in each section? I forgot to mention that the second sheet will not be visible to the user. To tweak the code you supplied would I just change the Activesheet to the second sheet name? Here is the code from the macro recorder.
Application.Goto Reference:="EMPDATA" Selection.Copy
Sheets("Sheet1").Select
Range("C10").Select
ActiveSheet.Paste
Sheets("Employee Inputs Before VBA").Select
Application.CutCopyMode = False
Application.Goto Reference:="DENTMED"
Selection.Copy
Sheets("Sheet1").Select
Range("J10").Select
ActiveSheet.Paste
Application.CutCopyMode = False

'Start of the Second Section to be Copied
Sheets("Employee Inputs Before VBA").Select
Application.Goto Reference:="EMPDATA"
Selection.Copy
Sheets("Sheet1").Select
Range("C30").Select
ActiveSheet.Paste
Sheets("Employee Inputs Before VBA").Select
Application.CutCopyMode = False
Application.Goto Reference:="LINS"
Selection.Copy
Sheets("Sheet1").Select
Range("J30").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Employee Inputs Before VBA").Select
Range("K8").Select
Click to expand...
 
Upvote 0
Hi..

just replace

Code: 
Range("C10").Select

ActiveSheet.Paste</pre>
with
Code: 
Range("C10").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveSheet.Paste

and for each section, no need to define the new range name like Range("C30").Select
 
Upvote 0
Thank you so much for your help !!



mgm05267 said:
Hi..

just replace

Code: 
Range("C10").Select

ActiveSheet.Paste
with
Code: 
Range("C10").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveSheet.Paste

and for each section, no need to define the new range name like Range("C30").Select
Click to expand...
 
Upvote 0
Try this code..

Code: 
Sub Update_Sheet_Data()


Application.ScreenUpdating = False


Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("EMPDATA").Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)


Sheets("Employee Inputs Before VBA").Range("EDUCOST").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("UNEMPLOYMENT").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("SOCIALSECURITY").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("LINS").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("LONGTERMDIS").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("HOUSE").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("DENTMED").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("MONTHLY401K").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("PITAX").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("MEDCARE").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("RETBONUS").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("ANNBONUS").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("SIGNON").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("RELOCATION").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("PERDEIM").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("TRAVEL").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("FSPRE").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("PAYRAISE").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Employee Inputs Before VBA").Range("BASE").Copy Destination:=Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp).Offset(1)


Application.ScreenUpdating = True


End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Complex Move data from two spreadsheets with similar data in different columns to a specific column in an existing spreadsheet(Sheet 3)
Replies
0
Views
138
JMC57
J
N
  • Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
220
DanteAmor
DanteAmor
k3yn0t3
  • Solved
INDEX(MATCH()) pulling wrong row data & Macro to duplicate sheets with formulas
Replies
4
Views
392
Alex Blakenburg
A
B
  • Solved
Copying a row of data from another sheet and pasting it into another sheet into the correct column headings
Replies
8
Views
397
kevin9999
K
M
  • Question
Hi, Have a problem with Power Automate and decided to use Excel VBA
Replies
3
Views
240
ides315
I

Forum statistics

Threads
1,215,472
Messages
6,125,005
Members
449,203
Latest member
Daymo66

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