VBA to insert row and data on multiple sheets

[grays]

worksheet1 has 3 columns of data which are duplicated in multiple worksheets. I am looking to add code that would allow a user to insert a row and add data on worksheet1 while simultaneously doing the same thing on the other worksheets.

There are 3 things to note.
1- the data from worksheet1 is repeated 4 times on the other worksheets (segregated by Total & 3 locations). The data on theses worksheets also has a Total Row and and blank row between the 4 sections
2 - every worksheet will have worksheet1 column 1 data, but some will have column 2 data and the others will have column 3 data.

at a minimum what code would help with inserting the rows on each section of the worksheets assuming they each had one section that matched worksheet1?

also does anyone have suggestions on simplifying what I'm trying to do?

Thanks

 

[skorpionkz]

there was recently few treads about moving the rows. Copy the row is the same, you just dont need to delete it.

one of treads:
http://www.mrexcel.com/forum/excel-...ations-if-difference-between-two-cells-x.html

 

[grays]

there was recently few treads about moving the rows. Copy the row is the same, you just dont need to delete it.

one of treads:
http://www.mrexcel.com/forum/excel-...ations-if-difference-between-two-cells-x.html

thanks. unfortunatly the copy and paste wont work in my scenario becuase the other sheets have columns of data computed using other macros. I would need to insert the rows and then the other macro would update the range for its calculations. I have a file that I can easily use, but it is not user freindly for others to update if additional criteria needs to be added. I'm trying to develop macros to lessen the update complexity for other users.

 

[skorpionkz]

if you don't need to copy whole row and just some part of it in different order, then create ad Array and then paste array into the range

 

[grays]

if you don't need to copy whole row and just some part of it in different order, then create ad Array and then paste array into the range

Thank you for the suggestion. Arrays are new to me, but at first glance excited to give it a try as well as improve the macros I already have.

 

[grays]

I was able to create the array if I defined the range. however, I'm not sure if I'm going about it the right way since I wont always know what the range is (how many rows). I am currently getting a Type Mismatch error on the last line of the code.

Sub rangearray()
    Dim arr() As Variant
    Dim Rng As Range
    Dim myCell As Range
    Dim i As Long
    Dim LastRow As Long
    
    With labor_ODClist
        LastRow = .Rows(.Rows.Count).Row
    End With
    Set Rng = labor_ODClist.Range("A4:A" & LastRow)
    For Each myCell In Rng
        ReDim Preserve arr(i)
        arr(i) = myCell
        i = i + 1
    Next myCell
    LaborDetail.Range("C4:C" & LastRow).Value = WorksheetFunction.Transpose(arr)
End Sub

I will also need to continue adding the values on LaborDetail.Range. ie if the last row is 103 then the next range will be 4 rows below that. ie range ("C107:C", ....)
Is there a way to count rows and use that in the code?

thanks

 

[skorpionkz]

you have already counted length of your array with "i"

 

[grays]

I dont understand. how do I set the range on the first sheet where the array is identified to match the range where the data is being placed? how does the code identify where to place the data and then repeat the placement 4 rows later? my apologies if my "verbage" isnt correct in stating this question.

 

[skorpionkz]

see this example. it should give you a clue

Sub test()

Dim myArray As Variant
Dim i As Long

i = 10

myArray = Range(Cells(2, 1), Cells(11, 1))

'###Range(Cells(15, 1), Cells(15 + i, 1)).Value = myArray '- use if dont want to Transpose

Range(Cells(15, 1), Cells(15, 1 + i - 1)).Value = WorksheetFunction.Transpose(myArray) '- code with transpose option

End Sub

 

[grays]

I'm not getting the clue.
"i" and "myArray" are static and pre-defined in your code. In my scenario I dont know how many rows will be in the array or the range where the array is transposed (not using it for rows to columns. more like a copy and paste). I wont know in advance how many rows of data will be entered on labor_ODClist(worksheet). As a result I also dont know how to tell the code to transpose the same array to the same worksheet 4 blanks below where the array was transposed the first time