jjobrien03
New Member
- Joined
- Sep 2, 2014
- Messages
- 41
I manage a database that must be updated with information daily, but I never know how many entries I will have to input in a day. Herein lies my problem. When I created a macro to run my submacro (to copy my text manipulation formulas which are frozen in cells B2:G2) then use go to-->special-->last cells in order to paste those formulas into the raw data I have just input (B6493:G6531) I encounter an error as the macro only copies into the specific range I used to run the macro the first time. How do I use VBA to paste into all the cells I need to rather than just my pre-defined range? I was trying to use "last cell" and "current region", but once I drag the active cell over to copy the rows B6531:G6531 I change the macro such that it only pastes into those rows everytime, or at least that number of rows. I don't know if I'm describing it accurately enough, but I have posted the VBA Code below (which I am pretty unfamiliar with), I can try to post pictures as well to better illustrate the situation. Thanks for the help!
Sub lastcells()
'
' lastcells Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Application.Run "'formatting_9-8-2014_input.xlsm'!Copy_Rows" *all this does is copy B2:G2
Selection.SpecialCells(xlCellTypeLastCell).Select
ActiveCell.Offset(0, -11).Range("A1").Select
Selection.CurrentRegion.Select
ActiveCell.Offset(0, -5).Range("A1:F39").Select
ActiveCell.Activate
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
The bold cells are those that I input manually and the regular cells are the outputs of the formulas I am trying to copy from B2:G2 into the last range of raw data (bold text ) I have input into my database.
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Sub lastcells()
'
' lastcells Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Application.Run "'formatting_9-8-2014_input.xlsm'!Copy_Rows" *all this does is copy B2:G2
Selection.SpecialCells(xlCellTypeLastCell).Select
ActiveCell.Offset(0, -11).Range("A1").Select
Selection.CurrentRegion.Select
ActiveCell.Offset(0, -5).Range("A1:F39").Select
ActiveCell.Activate
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
The bold cells are those that I input manually and the regular cells are the outputs of the formulas I am trying to copy from B2:G2 into the last range of raw data (bold text ) I have input into my database.
TEST 2013-AAA B | TEST 2013-AAA*B | TEST 2013-AAA | B | TEST 2013-AAA B | TRUE | 142 | 142/ | 5x | |||
TEST 2013-BBB C | TEST 2013-BBB*C | TEST 2013-BBB | C | TEST 2013-BBB C | TRUE | 145 | 145/ | 5x | |||
TEST 2013-AAA 1 | TEST 2013-AAA*1 | TEST 2013-AAA | 1 | TEST 2013-AAA 1 | TRUE | 147 | 147/ | 5x | |||
TEST 2013-AAA 2 | TEST 2013-AAA*2 | TEST 2013-AAA | 2 | TEST 2013-AAA 2 | TRUE | 150 | 150/ | 5x | |||
TEST 2014-CBA D | TEST 2014-CBA*D | TEST 2014-CBA | D | TEST 2014-CBA D | TRUE | 155 | 155/ | 5x |
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Last edited: