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]

try this,

Sub CopyRows()
Dim x As String
Dim y As String
Dim n As String
Dim m As String
  With ActiveSheet
  x = .Cells.Find(What:="Copy", After:=.Range("G1")).Row
  y = x + 39
  n = Range("A" & Rows.Count).End(xlUp).Row + 1
  m = n + 39
  Range("A" & n & ":O" & m).Value = Range("A" & x & ":O" & y)
  End With
End Sub

 

[asad]

This one is bit better

Sub CopyRows()
Dim x As String
Dim y As String
Dim n As String
Dim m As String
  With ActiveSheet
  x = .Cells.Find(What:="Copy", After:=.Range("G1")).Row
  y = x + 39
  n = Range("A" & Rows.Count).End(xlUp).Row + 1
  m = n + 39
  Range("A" & n & ":O" & m) = Range("A" & x & ":O" & y).Value
  End With
    
End Sub

 

[asad]

yesterday, I forgot to add the deleting part. Here it is now:

Sub CopyRows()
Dim x As String
Dim y As String
Dim n As String
Dim m As String
  With ActiveSheet
  x = .Cells.Find(What:="Copy", After:=.Range("G1")).Row
  y = x + 39
  n = Range("A" & Rows.Count).End(xlUp).Row + 1
  m = n + 39
  Range("A" & n & ":O" & m) = Range("A" & x & ":O" & y).Value
  End With
   Range("G" & x).Clear
   Range("G" & n).Clear
End Sub

 

[skomarni]

yesterday, I forgot to add the deleting part. Here it is now:

Sub CopyRows()
Dim x As String
Dim y As String
Dim n As String
Dim m As String
  With ActiveSheet
  x = .Cells.Find(What:="Copy", After:=.Range("G1")).Row
  y = x + 39
  n = Range("A" & Rows.Count).End(xlUp).Row + 1
  m = n + 39
  Range("A" & n & ":O" & m) = Range("A" & x & ":O" & y).Value
  End With
   Range("G" & x).Clear
   Range("G" & n).Clear
End Sub

Thanks again asad! This macro does a pretty good job by copying the right product and all 40 rows and then pasting the copy exactly where it is supposed to go, however, it only pastes values? I need it to do a paste 'All'
What could we change to make that happen? and on a slightly less important note, how can we make it so after the macro is run it selects say the top left corner of what was pasted/inserted so it brings the user down to that copy to work on?

Very nice macro though, if we can get it to copy everything and not just values, we're golden :D

 

[asad]

Try this

Sub CopyRows()
Dim x As String
Dim y As String
Dim n As String
Dim m As String
  With ActiveSheet
  x = .Cells.Find(What:="Copy", After:=.Range("G1")).Row
  y = x + 39
  n = Range("A" & Rows.Count).End(xlUp).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

 

[skomarni]

Try this

Sub CopyRows()
Dim x As String
Dim y As String
Dim n As String
Dim m As String
  With ActiveSheet
  x = .Cells.Find(What:="Copy", After:=.Range("G1")).Row
  y = x + 39
  n = Range("A" & Rows.Count).End(xlUp).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

Hot dang, you're on fire asad! The copy macro is perfect!

I'm now using CTRL+SHFT+D for Delete, CTRL+SHFT+C for Copy, and both are working as intended

The only other macro I would request would be a modified version of the already created 'Copy' macro, which would clear a set of desired cells from what is pasted (essentially leaving a 'new' template). Just quoting myself from my first post:

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).

^I also forgot to add B2 to that list.

I assume it could be achieved through a series of Range("_" & _).Clear ?



Note: It would probably be easier to leave a blank template in another sheet and then simply copy that using the 'new' macro. However, I was afraid of doing that as I had a problem in the past with a series of named ranges which create dynamic drop-down menus using data validation-lists throughout each 40row/12col template:

example of named ranges:
boxCategory =boxMenuTable[#Headers]
boxIndex =INDEX(boxMenuTable,0,MATCH(SHEET!A#,boxMenuTable[#Headers],0))
boxMenu =OFFSET(boxIndex,0,0,SUM(COUNTA(boxIndex),-COUNTBLANK(boxIndex)),1)

^ So in another sheet I have tables where the headers of those tables = the categories and then everything listed below belongs to that category, and then the user can select a category in one column and then select an item in the next. What used to happen was that for every sheet, I had to rename the name ranges by adding the product category to the end of the name ranges or else the drop-down menus would break ex. boxMenuPC1, boxMenuPC2, boxMenuPC3 (If I selected say Category1 in A1 on sheet#1, then I would have a list of values to select from in A2 corresponding to the selection in A1. But then on sheet#2 if i selected Category2 in A1, I would still have the list of values corresponding to A1 due to some sort of conflict.

I can't seem to recreate the problem though? I have multiple sheets using boxCategory, boxIndex, boxMenu etc. and all sheets are working as intended so I may have accidentally fixed the problem in my newest version.

If that problem does not exist and if it is less difficult than clearing a bunch of cells, then what we could do is take the macro you created for COPY:

Sub CopyRows()
Dim x As String
Dim y As String
Dim n As String
Dim m As String
  With ActiveSheet
  x = .Cells.Find(What:="Copy", After:=.Range("G1")).Row
  y = x + 39
  n = Range("A" & Rows.Count).End(xlUp).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

with the only differences being to not search for "Copy" in col G, and to copy the first 40 rows from a sheet titled "TEMPLATE" as the range to paste.

Let me know,
Thanks asad, you're incredible

 

[asad]

I will do the macro and post it here with in a day or two. It takes me a bit of time to write these macros because I have no knowledge of these things. I just go for hit and trial method. Anyway, I am just bit busy at the moment so I will get back to you within a day or two. Sorry for the inconvenience, but I will get back to you unless someone else does it for us. Asad

 

[skomarni]

Completely understandable, and thank you for all of your help so far! There's no rush

 

[skomarni]

Hmm.... the Copy macro is now giving me an error :/

Sub CopyRows()
Dim x As String
Dim y As String
Dim n As String
Dim m As String
  With ActiveSheet
  x = .Cells.Find(What:="Copy", After:=.Range("J1")).Row
  y = x + 39
  n = Range("A" & Rows.Count).End(xlUp).Row + 1
  m = n + 39
  [COLOR=#ff0000]Range("A" & x & ":O" & y).Copy Destination:=Range("A" & n & ":O" & m)[/COLOR]
  End With
   Range("J" & x).Clear
   Range("J" & n).Clear
   Range("A" & n).Select
End Sub

The text in red is what gets highlighted and I get the error:
Run-time error '1004':
You cannot change part of an array.

I removed the

Range("J" & x).Clear
   Range("J" & n).Clear
   Range("A" & n).Select

to see if that had anything to do with it but it doesn't. I'm not sure what array it is pointing too for the error and why a copy would change an array in the first place?

 

[skomarni]

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