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]

you have I defined

    For Each myCell In Rng
        ReDim Preserve arr(i)
        arr(i) = myCell
        i = i + 1
    Next myCell

so after loop will finish you will know what is your Array length.

try this code:

Sub rangearray()
    Dim arr As Variant
    Dim i As Long
    Dim LastRow As Long

    LastRow = labor_ODClist.Rows(.Rows.Count).Row
    
    arr = labor_ODClist.Range(Cells(4, 1), Cells(LastRow, 1))
    
    i = LastRow - 4 + 1
    
    LaborDetail.Range(Cells(4, 1), Cells(LastRow, 1)).Value = WorksheetFunction.Transpose(arr)
    
    LaborDetail.Range(Cells(LastRow + 4, 1), Cells(LastRow + 4, 1 + i - 1)).Value = WorksheetFunction.Transpose(arr)

End Sub

 

[grays]

Thanks so much for all of your help! I figured out that was having issues becuase it was going to the end of the sheet instead of the last row with data (i was too large). I was close to getting there when you responded. below is my final 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 = .Range("A4").End(xlDown).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(Cells(4, 3), Cells(LastRow, 3)).Value = WorksheetFunction.Transpose(arr)
    LaborDetail.Range(Cells(LastRow + 4, 3), Cells(LastRow + LastRow, 3)).Value = WorksheetFunction.Transpose(arr)
End Sub

very excited about this...

 

[skorpionkz]

Please check this code. It should do same think but without loop. And it looks like you dont need Transpose here

Sub rangearray()

    Dim arr As Variant
    Dim LastRow As Long

     LastRow = labor_ODClist.Cells(4, 1).End(xlDown).Row
    
    arr = labor_ODClist.Range(Cells(4, 1), Cells(LastRow, 1))
    
    LaborDetail.Range(Cells(4, 3), Cells(LastRow, 3)).Value = arr
    LaborDetail.Range(Cells(LastRow + 4, 3), Cells(LastRow + LastRow+4-1, 3)).Value = arr

End Sub

 

[skorpionkz]

Rng = labor_ODClist.Range("A4:A" & LastRow)

the range you have is Vertical and same is an Array (Arr)

so if you would Transpose it as in your code you will have first cell value in whole range

LaborDetail.Range(Cells(4, 3), Cells(LastRow, 3)).Value

if you want to transpose it then

 LaborDetail.Range(Cells(LastRow + 4, 3), Cells(LastRow + 4, 3 + LastRow - 1)).Value = WorksheetFunction.Transpose(arr)

 

[grays]

thanks if I wanted to add a loop where the array is used the same way on more than just the LaborDetail worksheet, what do you think is the best approach? A for each Loop with a Select Case scenario?

 

[skorpionkz]

Try to avoid loop whenever there is an option to do so.

Check this thread written by sjipie.

http://www.mrexcel.com/forum/excel-...t-guide-better-visual-basic-applications.html

 

[grays]

Thanks. Instead of looping, could I create another array that contained all of the worksheets where I wanted the data to "paste"? It would be a 1 dimensional static array. However, I'm not sure how to fit that into the code we've already discussed.

 

[grays]

I tried this code. I can see where it highlights all of the right sheets, but the data only shows up on the first sheet.

 With Sheets(Array(LaborDetail.Name, Sheet3.Name, Sheet5.Name, Sheet6.Name, Sheet8.Name, Sheet9.Name)).Select
        Range(Cells(4, 3), Cells(LastRow, 3)).Value = WorksheetFunction.Transpose(arr)
        ...

suggestions?