MACRO to Copy/Delete a Range of Rows depending on Location of Cell Value (Dynamic)?

[skomarni]

Hi,

I've avoided posting my own thread for years when it has come to excel help, but this time I am really stuck. I am not too experienced with macros... I'll typically look for an alternative solution or else I record simply copy/delete/hide etc. macros using excel's 'Record Macro'.

I have created a costing program in excel for the industry I'm in which is intended to cost out hundreds of products through the use of individual 12 col/40 row template. Starting with a template range of A1:O40 (Columns L,M,N are always hidden, but must be copied with the rest of the range) I will select the entire range ctrl-c, ctrl-v into cell A41, which duplicates the former range in it's entirety with identical functionality. I use different worksheets for different categories, but products within the same category are copy and pasted directly below one another (A1,A41,A81,A121 etc.). Functionally, the system works fine, however I've been trying to find a way where I can add 3 buttons along the top of each template which will be labeled: DELETE, COPY, NEW.

Here's what I need the macro's to do:
DELETE: I need this macro to be able to delete the row numbers belonging to the current product template being used (ex. If I want to delete the template for the product which is in range A41:080, I could click a button w/ assigned macro within that template to remove the desired rows). Since I'm not sure sure whether a macro can determine the row number of where a button was clicked and delete that row + 39 below it, I was thinking I could add a confirmation cell. In cell G1 (and G41, G81, G121 etc.) I can have the user type "DEL" and then activate the macro. The macro would scan 'Column G' for "DEL" and when it finds it, it would delete that row + the 39 below it? Is this possible?

COPY: Similarly, in J1 (and J41, J81, J121 etc.) I would have the user type "COPY" on the template they wish to copy and activate the copy macro which would scan 'Column J' for "COPY" and when it finds it, it would select that row + the 39 below it and insert that copied range after the last row containing data, and lastly, remove the text "COPY" from the original template and the new.

NEW: This macro needs to copy Rows 1-40 and insert that copied range after the last row containing data, and then delete cell values in these ranges + the row number it was pasted in - 1: (A4, A14:C28, B31:C36, B39:D40, I4:I10, O4:O10, G14, G16, G19, G21, G23:G24, G26, G32:I40 on the original template) so if it was pasted into Row41 (41-1=40) then the values would be (A5, A54-C68, B71:C76, B79:D80, I44:I50, G54, G56, G59, G61, G63:G64, G66, G72:I80).

Unfortunately I can not share the workbook as it contains confidential costing information, but hopefully I have done a well enough job explaining what I am trying to achieve. Let me know if anyone needs anymore details.

I'll gladly take any help I can get on any of these 3 macros I have described.

Thank you so much,
Stephen

 

[asad]

Ignore my last post, I changed

n = Range("A" & Rows.Count).End(xlUp).Row + 1

to

n = Range("B" & Rows.Count).End(xlUp).Row + 1

and it fixed my problem

I realized that the macro wasn't getting the correct row count, which was causing it to paste onto itself as opposed to below

Instead of selecting different column, try this:

Sub CopyRows()
Dim x As String
Dim y As String
Dim n As String
Dim m As String
Dim rng As Range
  With ActiveSheet
  x = .Cells.Find(What:="Copy", After:=.Range("G1")).Row
  y = x + 39
  Set rng = Range("A:O")
  n = rng.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 1
  m = n + 39
  Range("A" & x & ":O" & y).Copy Destination:=Range("A" & n & ":O" & m)
  End With
   Range("G" & x).Clear
   Range("G" & n).Clear
   Range("A" & n).Select
End Sub

And a question for the TEMPLATE macro - What is the sheet name that you are pasting template into.

Asad

 

[skomarni]

Instead of selecting different column, try this:

Sub CopyRows()
Dim x As String
Dim y As String
Dim n As String
Dim m As String
Dim rng As Range
  With ActiveSheet
  x = .Cells.Find(What:="Copy", After:=.Range("G1")).Row
  y = x + 39
  Set rng = Range("A:O")
  n = rng.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 1
  m = n + 39
  Range("A" & x & ":O" & y).Copy Destination:=Range("A" & n & ":O" & m)
  End With
   Range("G" & x).Clear
   Range("G" & n).Clear
   Range("A" & n).Select
End Sub

And a question for the TEMPLATE macro - What is the sheet name that you are pasting template into.

Asad

K, I'll try that macro instead, thanks

And the user would be pasting into whatever sheet they are currently in (activesheet?)

 

[skomarni]

Set rng = Range("A:O") 
 n = rng.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 1

Works like a charm, thanks

 

[asad]

OK, here is the final macro Hopefully it does what you wanted. For this, you have to first go in the Template worksheet and name the range A1:O40 as Template. And then run this code in the worksheet that you want template to go in.

Sub CopyRows()
Dim n As String
Dim m As String
Dim rng As Range
  With ActiveSheet
  Set rng = Range("A:O")
  n = rng.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 1
  m = n + 39
  Range("Template").Copy Destination:=Range("A" & n & ":O" & m)
  End With
   Range("A" & n).Select
End Sub

 

[skomarni]

Everything is great, thanks asad! You were extremely helpful throughout this whole process!

I now have 3 macros set-up and all are working as intended I even gained some knowledge along the way by seeing what you were doing and why you were doing it, and was able to make changes to the macros on my own! Like this for example:

Sub CopyRows()
Dim x As String
Dim z As String
Dim y As String
Dim n As String
Dim m As String
Dim o As String
Dim rng As Range
  With ActiveSheet
  x = .Cells.Find(What:="Copy", After:=.Range("A5")).Row
  z = x - 4
  y = x + 35
  Set rng = Range("A:O")
  n = rng.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 1
  m = n + 39
  o = n + 4
  Range("A" & z & ":O" & y).Copy Destination:=Range("A" & n & ":O" & m)
  End With
  Range("A" & x).ClearContents
  Range("A" & o).ClearContents
  Range("B" & n).Select
End Sub

(I had moved some things around the template sheet, but this made it all A-OK!)


I did go ahead and re-add the form buttons back to the template as I believe it makes the process a whole lot more user friendly as opposed to keyboard shortcuts that they may or may not memorize. I just prefer the buttons on the sheet, although I use the shortcuts myself.
^With that, I'm now looking for a way to remove perhaps remove objects from the area I am deleting, and then delete the rows after the objects have been removed.

And lastly, I'm trying to find a macro that can change the data selection for a chart. When I copy the templates, they have a chart in them that breaks down the percentage of cost for four different categories, and that chart gets pasted along with the template all nicely, however the old data selection remains until I manually change it.

You've helped me enough though! Thanks again! There's no rush for these other two problems, I'll browse around and see if a solution to either of my problems exists and if not I may come back or simply deal with the small inconvenience.

 

[asad]

Glad to know that I was of help to someone. I do not know how to delete the macro button. May be you could start another thread and someone can help you and I will learn as well .

 

[skomarni]

Will do, thanks asad. Not sure how to properly close a thread, but consider this thread closed.

Reason: Solution found

Other Comments: asad is pro