Creating a macro that will change as the document changes

[Nicole333333]

Hi Everyone!

I am still new to this Macro thing, but I am trying to create a macro that will copy and paste data from one column to another; however, I need it to still work if new rows are added in the future without having to go in and change the code every time. The other trick is, there is a certain point (a Total's) row, that I have to stop copying over at. Otherwise, I would have just highlighted the entire column. If someone could point me in the right direction I would really appreciate it!

Thank you all so much!

Nicole

 

[Lymm]

Hi Nicole, if you upload a sample file with your requirements Im sure you will get some help soon.

 

[John()]

This might point you in the right direction .... first off it works it gets the info from one cloumn and puts it in another ..... if you then add to it and run again it adds to the other column ..... i added a total in second column and it sums up the info .... if you add more data to first column and it exceeds the space left in second column before the total cell ... it dose not add this data and a msg box pops up and tells you there is not enough space

now i dont write vb code as have only started to teach myself but this wasnt to difficult so i posted ...
But as Lymm pointed out more info is required eg are the column the data is going to be entered on and the column its going to be pasted too going to be on same sheeth or different sheets in same workbook or in different workbooks

if there is not enough space in the second column when you add more data to the first column ....what do you want done ... do you want coded into the vba to move the total row down so as to fit the extra data fits ....
giving more info like this with a sample spreadsheet of what you would like would make it easier for someone to help you.

anyway heres what i wrote its just a example as it just moves the data from the first colum in the sheet to the second column in the sheet and totals them

FORGOT TO ADD the TOTAL CELL is manually added in the spreadsheet and is not part of the code so if you are trying this out to see how it works add your data in colum A and then in colum B pick a cell to put your total function in eg =sum(B1:B whatever cell you placed it in) ... then you can run the macro

Sub copy_sum()
'checks to see where your data ends
num_rows = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
'checks to see what row your total is on
num_rows2 = Sheet1.Cells(Rows.Count, 2).End(xlUp).Row
'this assigns all your data in the colmn to a variable
ra = Range("a1:a" & num_rows).Value
'the if checks if you have enough space to add the data
'or dose it extend past the Total Cell
If (num_rows < num_rows2) Then
Range("b1:b" & num_rows2 - 1).ClearContents
Range("b1:b" & num_rows).Value = ra
Else
'if there is not enough space it dosent copy data
'and informs you with a message box
MsgBox ("not enough space")
End If
End Sub

 

[Nicole333333]

Unfortunately, I cannot upload the exact Excel file I need this done on as it is for my work. But! Here is an extremely basic one I have created in order to help better illustrate my issue. So, I am trying to create a macro that will copy the data in Column A to Column B. However, I do not want to copy over anything from the cell marked "Totals" and down. I only want to copy the information above the "Totals" cell over. But, I need it to still work if I decide to insert a row to add more data. So, in other words, I want it to be flexible enough that I can add more data above the "Totals" cell and the macro will still copy that information over. I do not need to actually code a "Totals" macro, the "Totals" cell is just an example of a constraint on the length of the column being copied over.

 

[John()]

Ok try this .... only thing you need to do is always leave a blank cell before totals ..... when you run out of space just insert more rows if needed.

Sub move_cells()
'finds first blank row in Column A
num_rows = Sheet1.Range("a1").End(xlDown).Row
'finds the number of rows to the blank before total
num_rows2 = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row - 2
'clears contents in Column C (incase you enter numbers
'in column A and made a error you can delete and update
Range("c2:c" & num_rows2).ClearContents
'assigns the values in column A to a variable
ra = Range("a2:a" & num_rows).Value
'this then puts the values in Column C
Range("c2:c" & num_rows).Value = ra
End Sub

 

[Nicole333333]

Okay! That's definitely working so far! One more question with it, if Column A is actually a summation of other columns and I am trying to copy over just the values of those summations, is it the same?

 

[John()]

not quite sure what you mean ...... think what you mean is ... for example cell1 row A is a total from say column D ... cell 2 row A is a total from colum E etc
then could you just pull from those colums into row C rather than put them in Column A first ..... if thats it .... yes you should be able to do that but need a new macro lol ...
if thats not what you mean .... will need you to explain it again but either way will need a new spread sheet with exactly what you want done ... with as much info as possible makes it far easier to understand

 

[Nicole333333]

I have uploaded another sample document of what I mean. In this example, I want a macro that will copy over the values of Column C to Column D. Column C is a summation of Columns A and B, but I don't want to copy over the formula to Column D, I just want the values of Column C to be copied over to Column D. I also need this macro, like my previous example, to be able to add more rows and still work. (as in, I like the "find the first blank cell thing"). Again, thank you so much for all your help, I really, really appreciate it! I apologize for not being more clear.

 

[John()]

Ok able to do that but have run into a little problem so wont get a chance till tomorrow evening to finish it .... either my version of excel is playing up or i discovered a strange occurance in excel which i posted a new thread on to see if it happens to anyone else ... and is messing up the way i want the macro to work ... so will have to change the way the columns are totaled you can read about my strange excel problem in the below thread
http://chandoo.org/forum/threads/strange-occurance-in-excel-need-advice.23826/

 

[John()]

Ok wrote a macro hopefully covers your needs ... discovered the little problem above was a autofill feature of excell which caused problems , as it kept adding the sum formula to the blank row required in this macro ... so i added a line to disable it when running the macro ... i also added the sum formula to the macro so no need to manually put it in
i also added a one line macro that if you wish you can apply to another button which turns this feature back on when you are finished doing you main macro

I have uploaded a sheet without the macro but has the buttons so you can assign the macros to them and test it out
there are two macros below one called Copy_Values other called TurnOnn

Sub Copy_Values()

'finds the number of rows used in Column A
num_rows = Sheet1.Range("a1").End(xlDown).Row
'finds the number of rows to the blank before total
num_rows2 = Sheet1.Cells(Rows.Count, 3).End(xlUp).Row - 2
'if used just in case someone runs macro on empty sheet
If num_rows < 1048576 Then
'if used in case only one row of data added
If num_rows < num_rows2 Then
Range("C2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-2]"
'if for when more than one row of data added
'this fills the sum formula into the required amount of rows
If num_rows > 2 Then
  Selection.AutoFill Destination:=Range("C2:C" & num_rows), Type:=xlFillDefault
  Application.ExtendList = False
End If

'this first clears contents in G column
' and then add in the data
If num_rows < num_rows2 Then
Range("g2:g" & num_rows2).ClearContents
'assigns the values to a variable
ra = Range("c2:c" & num_rows).Value
'this then puts the values in Column C
Range("g2:g" & num_rows).Value = ra

End If
Else
'error msg if you extend your data over rows available

MsgBox "Not Enough Space"
End If
Else
Range("g2:g" & num_rows2).ClearContents
End If


End Sub
Sub TurnOnn()
'
'
'

'Turns back on auto extend formulas
    Application.ExtendList = True
End Sub

 

[John()]

Lol forgot to add file ........ also added another macro which adds 10 rows so if you get fed up adding single rows this will add ten or if you change the value below as many as you think you will need

Sub addingRows()
'
' addingRows Macro
'

Dim i As Integer
count_r = Sheet1.Cells(Rows.Count, 3).End(xlUp).Row - 2
'Change the + 10 to the REQUIRED AMOUNT OF ROWS NEEDED
count_B = count_r + 10
    Rows(count_r & ":" & count_B).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Cells(count_r, 1).Select
End Sub