Macro: Select Ranges Using Go to-->special-->last cell and current range

[jjobrien03]

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.

[TABLE="width: 1234"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]TEST 2013-AAA B[/TD]
[TD] TEST 2013-AAA*B[/TD]
[TD]TEST 2013-AAA[/TD]
[TD] B[/TD]
[TD]TEST 2013-AAA B[/TD]
[TD="align: center"]TRUE[/TD]
[TD]142[/TD]
[TD]142/[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]5x[/TD]
[/TR]
[TR]
[TD]TEST 2013-BBB C[/TD]
[TD] TEST 2013-BBB*C[/TD]
[TD]TEST 2013-BBB[/TD]
[TD] C[/TD]
[TD]TEST 2013-BBB C[/TD]
[TD="align: center"]TRUE[/TD]
[TD]145[/TD]
[TD]145/[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5x[/TD]
[/TR]
[TR]
[TD]TEST 2013-AAA 1[/TD]
[TD] TEST 2013-AAA*1[/TD]
[TD]TEST 2013-AAA[/TD]
[TD] 1[/TD]
[TD]TEST 2013-AAA 1[/TD]
[TD="align: center"]TRUE[/TD]
[TD]147[/TD]
[TD]147/[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5x[/TD]
[/TR]
[TR]
[TD]TEST 2013-AAA 2[/TD]
[TD] TEST 2013-AAA*2[/TD]
[TD]TEST 2013-AAA[/TD]
[TD] 2 [/TD]
[TD]TEST 2013-AAA 2[/TD]
[TD="align: center"]TRUE[/TD]
[TD]150[/TD]
[TD]150/[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5x[/TD]
[/TR]
[TR]
[TD]TEST 2014-CBA D[/TD]
[TD] TEST 2014-CBA*D[/TD]
[TD]TEST 2014-CBA[/TD]
[TD] D[/TD]
[TD]TEST 2014-CBA D[/TD]
[TD="align: center"]TRUE[/TD]
[TD]155[/TD]
[TD]155/[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]5x[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fazza]

what about this? Basically it fills down the cells in B2:G2 to the end of the current region (based on A1).

With Range("A1").CurrentRegion
  .Offset(1, 1).Resize(.Rows.Count - 1, 6).FillDown
End With

 

[jjobrien03]

Sorry, I am a VBA beginner and just starting to learn. So what your code is doing is after I define Range("A1") using current region, it moves one cell down and one to the right, then resizes the range to (why the negative one?) then six rows. FillDown copies down the range. Do i insert your code after defining range A1 as below? How does it know when to stop filling cells in the current range because I have 6,000+ rows and counting and each day is represented by a different range of the same format separated by "empty row, date, empty row". This is mainly a scrap sheet for submission into a final database.

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

With Range("A1").CurrentRegion .Offset(1, 1).Resize(.Rows.Count - 1, 6).FillDownEnd WithActiveCell.Activate
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

 

[Fazza]

the code I offered was to replace your current code

but I'm not sure now as there is mention of "empty row, date, empty row" which is not how databases are done

if your set up like a database, then what I offered should be good
if not, then best to start again