VBA or Macro to copy last row of active sheet, active worksheet to Sheet1Row2 of pre-existing workbook

sxmont

New Member
Joined
Mar 28, 2013
Messages
4
Hello,

I am struggling with this one. I need to copy all of the contents on the last row of data from an active workbook to Sheet1Row2 of a pre-defined workbook which has multiple sheets with formulas referencing the data in the cells on Sheet1Row2 of that pre-defined workbook. This code needs to over-write the data that is pre-existing in Sheet1Row2.

So, its copy the contents of the last row of data from sheet1 of a workbook, (names change on that workbook), to sheet1Row2 of a pre-existing workbook,(call this MASTER and the name never changes)

I thought I would be able to figure this out, but am struggling. Any help or guide would be helping and appreciated.

-sXmont
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Hello,

I am struggling with this one. I need to copy all of the contents on the last row of data from an active workbook to Sheet1Row2 of a pre-defined workbook which has multiple sheets with formulas referencing the data in the cells on Sheet1Row2 of that pre-defined workbook. This code needs to over-write the data that is pre-existing in Sheet1Row2.

So, its copy the contents of the last row of data from sheet1 of a workbook, (names change on that workbook), to sheet1Row2 of a pre-existing workbook,(call this MASTER and the name never changes)

I thought I would be able to figure this out, but am struggling. Any help or guide would be helping and appreciated.

-sXmont

The code below will do the copy and paste. I am not quite sure that I understand the bit about the formulas in the 'Master' workbook (destination) sheet 1, row 2. This code will overwrite whatever is in that row, including formulas. If other cells have formulas that are reliant on the cells in row 2, they could be affected by the data from the other workbook. Sorry, but that is how copy and paste works. If that bit of knowledge deters you from using the copy method, then think about maybe linking the cells in row 2 of the Master, sheet 1, to the last row of sheet in source wb. That's a trick too, if the range in the source workbook is dynamic. However, If you are a John Walkenbach fan, you can overcome that.
Code:
Sub cpyStuf()
Dim wb1 As Workbook, wb2 As Workbook, lr As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Set wb1 = ThisWorkbook
Set wb2 = Workbooks("Master.xlsx") 'Edit file extension
Set sh1 = wb1.Sheets(1) 'Edit Sheet Name
Set sh2 = wb2.Sheets(1) 'Edit Sheet Name
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row 'assumes Col A is representative of column lengths.
sh1.Rows(lr).Copy sh2.Range("A2")
End Sub
 

sxmont

New Member
Joined
Mar 28, 2013
Messages
4
The problem is that the name of the initial workbook always changes. Can I run this code as a macro? And What I mean is that the MASTER workbook have multiple sheets Sheet2 Sheet3 Sheet4 etc..etc.. that have different formulas setup to reference Sheet1Row2 of that same MASTER workbook. I need to copy the last row from a different workbook into Sheet1Row2 of that MASTER workbook so the formulas would work and yes I need to overwrite the data in Sheet1Row2 each time.
 

sxmont

New Member
Joined
Mar 28, 2013
Messages
4
Thank you for your code, but i get a runtime error 9 Subscript out of range.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

You would need to edit the Workbook name and file extension to be sure it matches your files. I used xlsx as the extension, but if it is xlsm or xls then it will give you the error.
 

sxmont

New Member
Joined
Mar 28, 2013
Messages
4
THANK YOU!!! This code works properly... My question is now. how I can set this so that is "installed" on the toolbar for different users.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
THANK YOU!!! This code works properly... My question is now. how I can set this so that is "installed" on the toolbar for different users.

You can create a button and assign a macro to it, then put the button on the tool bar. See Excel help - "Add button to toolbar"
 

Watch MrExcel Video

Forum statistics

Threads
1,133,530
Messages
5,659,355
Members
418,498
Latest member
nattynat

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