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
[TABLE="width: 706"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 706"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]EMP[/TD]
[TD]BA[/TD]
[TD]PT[/TD]
[TD]CC[/TD]
[TD]COMP[/TD]
[TD]DPT[/TD]
[TD]PC[/TD]
[/TR]
[TR]
[TD]E_1[/TD]
[TD]BUSINESS AREA 1[/TD]
[TD]PT_ExPatUS[/TD]
[TD]COST CENTER 1[/TD]
[TD]COMPANY 1[/TD]
[TD]DEPARTMENT 1[/TD]
[TD]PROFIT CENTER 1[/TD]
[/TR]
[TR]
[TD]E_2[/TD]
[TD]BUSINESS AREA 2[/TD]
[TD]PT_ExPatUS[/TD]
[TD]COST CENTER 2[/TD]
[TD]COMPANY 2[/TD]
[TD]DEPARTMENT 2[/TD]
[TD]PROFIT CENTER 2[/TD]
[/TR]
[TR]
[TD]E_3[/TD]
[TD]BUSINESS AREA 3[/TD]
[TD]PT_ExPatUS[/TD]
[TD]COST CENTER 3[/TD]
[TD]COMPANY 3[/TD]
[TD]DEPARTMENT 3[/TD]
[TD]PROFIT CENTER 3[/TD]
[/TR]
[TR]
[TD]E_4[/TD]
[TD]BUSINESS AREA 4[/TD]
[TD]PT_ExPatNonUS[/TD]
[TD]COST CENTER 4[/TD]
[TD]COMPANY 4[/TD]
[TD]DEPARTMENT 4[/TD]
[TD]PROFIT CENTER 4[/TD]
[/TR]
[TR]
[TD]E_5[/TD]
[TD]BUSINESS AREA 5[/TD]
[TD]PT_ExPatUS[/TD]
[TD]COST CENTER 5[/TD]
[TD]COMPANY 5[/TD]
[TD]DEPARTMENT 5[/TD]
[TD]PROFIT CENTER 5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Example of DentMed Calculation Section Columns (AW-BO)
[TABLE="width: 558"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD] [TABLE="width: 558"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]FIN ACCOUNT[/TD]
[TD]DENTMED[/TD]
[TD]DENTMED[/TD]
[TD]DENTMED[/TD]
[TD]DENTMED[/TD]
[/TR]
[TR]
[TD]650120[/TD]
[TD="align: right"]$126.96[/TD]
[TD="align: right"]$126.96[/TD]
[TD="align: right"]$126.96[/TD]
[TD="align: right"]$126.96[/TD]
[/TR]
[TR]
[TD]650120[/TD]
[TD="align: right"]$126.96[/TD]
[TD="align: right"]$126.96[/TD]
[TD="align: right"]$126.96[/TD]
[TD="align: right"]$126.96[/TD]
[/TR]
[TR]
[TD]650120[/TD]
[TD="align: right"]$957.63[/TD]
[TD="align: right"]$957.63[/TD]
[TD="align: right"]$957.63[/TD]
[TD="align: right"]$957.63[/TD]
[/TR]
[TR]
[TD]650120[/TD]
[TD="align: right"]$412.00[/TD]
[TD="align: right"]$412.00[/TD]
[TD="align: right"]$412.00[/TD]
[TD="align: right"]$412.00[/TD]
[/TR]
[TR]
[TD]650120[/TD]
[TD="align: right"]$957.63[/TD]
[TD="align: right"]$957.63[/TD]
[TD="align: right"]$957.63[/TD]
[TD="align: right"]$957.63

[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Example of Combined:
[TABLE="width: 1264"]
<colgroup><col><col><col><col><col><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD]EMP[/TD]
[TD]BA[/TD]
[TD]PT[/TD]
[TD]CC[/TD]
[TD]COMP[/TD]
[TD]DPT[/TD]
[TD]PC[/TD]
[TD]FIN ACCOUNT[/TD]
[TD]DENTMED[/TD]
[TD]DENTMED[/TD]
[TD]DENTMED[/TD]
[TD]DENTMED[/TD]
[/TR]
[TR]
[TD]E_1[/TD]
[TD]BUSINESS AREA 1[/TD]
[TD]PT_ExPatUS[/TD]
[TD]COST CENTER 1[/TD]
[TD]COMPANY 1[/TD]
[TD]DEPARTMENT 1[/TD]
[TD]PROFIT CENTER 1[/TD]
[TD]650120[/TD]
[TD="align: right"]$126.96[/TD]
[TD="align: right"]$126.96[/TD]
[TD="align: right"]$126.96[/TD]
[TD="align: right"]$126.96[/TD]
[/TR]
[TR]
[TD]E_2[/TD]
[TD]BUSINESS AREA 2[/TD]
[TD]PT_ExPatUS[/TD]
[TD]COST CENTER 2[/TD]
[TD]COMPANY 2[/TD]
[TD]DEPARTMENT 2[/TD]
[TD]PROFIT CENTER 2[/TD]
[TD]650120[/TD]
[TD="align: right"]$126.96[/TD]
[TD="align: right"]$126.96[/TD]
[TD="align: right"]$126.96[/TD]
[TD="align: right"]$126.96[/TD]
[/TR]
[TR]
[TD]E_3[/TD]
[TD]BUSINESS AREA 3[/TD]
[TD]PT_ExPatUS[/TD]
[TD]COST CENTER 3[/TD]
[TD]COMPANY 3[/TD]
[TD]DEPARTMENT 3[/TD]
[TD]PROFIT CENTER 3[/TD]
[TD]650120[/TD]
[TD="align: right"]$957.63[/TD]
[TD="align: right"]$957.63[/TD]
[TD="align: right"]$957.63[/TD]
[TD="align: right"]$957.63[/TD]
[/TR]
[TR]
[TD]E_4[/TD]
[TD]BUSINESS AREA 4[/TD]
[TD]PT_ExPatNonUS[/TD]
[TD]COST CENTER 4[/TD]
[TD]COMPANY 4[/TD]
[TD]DEPARTMENT 4[/TD]
[TD]PROFIT CENTER 4[/TD]
[TD]650120[/TD]
[TD="align: right"]$412.00[/TD]
[TD="align: right"]$412.00[/TD]
[TD="align: right"]$412.00[/TD]
[TD="align: right"]$412.00[/TD]
[/TR]
[TR]
[TD]E_5[/TD]
[TD]BUSINESS AREA 5[/TD]
[TD]PT_ExPatUS[/TD]
[TD]COST CENTER 5[/TD]
[TD]COMPANY 5[/TD]
[TD]DEPARTMENT 5[/TD]
[TD]PROFIT CENTER 5[/TD]
[TD]650120[/TD]
[TD="align: right"]$957.63[/TD]
[TD="align: right"]$957.63[/TD]
[TD="align: right"]$957.63[/TD]
[TD="align: right"]$957.63[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1195"]
<colgroup><col><col><col><col><col><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD]E_1[/TD]
[TD]BUSINESS AREA 1[/TD]
[TD]PT_ExPatUS[/TD]
[TD]COST CENTER 1[/TD]
[TD]COMPANY 1[/TD]
[TD]DEPARTMENT 1[/TD]
[TD]PROFIT CENTER 1[/TD]
[TD][/TD]
[TD]650160[/TD]
[TD="align: right"]$18.53[/TD]
[TD="align: right"]$18.53[/TD]
[TD="align: right"]$18.53[/TD]
[TD="align: right"]$18.53[/TD]
[/TR]
[TR]
[TD]E_2[/TD]
[TD]BUSINESS AREA 2[/TD]
[TD]PT_ExPatUS[/TD]
[TD]COST CENTER 2[/TD]
[TD]COMPANY 2[/TD]
[TD]DEPARTMENT 2[/TD]
[TD]PROFIT CENTER 2[/TD]
[TD][/TD]
[TD]650160[/TD]
[TD="align: right"]$18.53[/TD]
[TD="align: right"]$18.53[/TD]
[TD="align: right"]$18.53[/TD]
[TD="align: right"]$18.53[/TD]
[/TR]
[TR]
[TD]E_3[/TD]
[TD]BUSINESS AREA 3[/TD]
[TD]PT_ExPatUS[/TD]
[TD]COST CENTER 3[/TD]
[TD]COMPANY 3[/TD]
[TD]DEPARTMENT 3[/TD]
[TD]PROFIT CENTER 3[/TD]
[TD][/TD]
[TD]650160[/TD]
[TD="align: right"]$65.47[/TD]
[TD="align: right"]$65.47[/TD]
[TD="align: right"]$65.47[/TD]
[TD="align: right"]$65.47[/TD]
[/TR]
[TR]
[TD]E_4[/TD]
[TD]BUSINESS AREA 4[/TD]
[TD]PT_ExPatNonUS[/TD]
[TD]COST CENTER 4[/TD]
[TD]COMPANY 4[/TD]
[TD]DEPARTMENT 4[/TD]
[TD]PROFIT CENTER 4[/TD]
[TD][/TD]
[TD]650160[/TD]
[TD="align: right"]$163.67[/TD]
[TD="align: right"]$163.67[/TD]
[TD="align: right"]$163.67[/TD]
[TD="align: right"]$163.67[/TD]
[/TR]
[TR]
[TD]E_5[/TD]
[TD]BUSINESS AREA 5[/TD]
[TD]PT_ExPatUS[/TD]
[TD]COST CENTER 5[/TD]
[TD]COMPANY 5[/TD]
[TD]DEPARTMENT 5[/TD]
[TD]PROFIT CENTER 5[/TD]
[TD][/TD]
[TD]650160[/TD]
[TD="align: right"]$65.47[/TD]
[TD="align: right"]$65.47[/TD]
[TD="align: right"]$65.47[/TD]
[TD="align: right"]$65.47[/TD]
[/TR]
</tbody>[/TABLE]
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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 Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
294
Jagadeshrao
J
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
250
mumps
mumps
J
  • Question 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
336
JMC57
J
A
  • Question Question
Excel data from one sheet, copy cells from row i'm working on to excel 'form' based on cell value
Replies
2
Views
510
asiuol84
A
N
  • Question Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
453
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

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