Button to add rows to different sheets & use formulas from row above

[Tootlez]

I have a feeling I've over my head, and I may just have to back down from this idea -- more because my knowledge of VBA is limited... but I know how to generally read the code.

My Set up:
I have a 6 tabbed Book: Info, Main, Inventory, Images, No of Images, Manu Info
The connections: Main uses Info & Manu Info to determine the formulated values; Inventory uses a mixture of Info and Main to gather what it needs; Images uses Main and No of Images (just a counting sheet) to gather what it needs, Manu Info stands alone.

What I'm after:
When I have gathered all my data in the Info tab, I want to click a button: "Finished" that will count how many rows there are, and in Main, Inventory and Images drag down the information (pre-formulated in the first row) that many of rows.

I found a tutorial with a button which adds a row, but I can't figure how to add a row to a different sheet, AND grab the formula from the row above.
I started with the tutorial of the button and I have a cell above the button that counts the number of rows i currently have (which varies from import to import) -- but i just am not sure where to go from there.


I have been finishing my Info, and going through each tab and dragging it down --- I just thought this would help save a few more minutes in the long run -- but if anything it will help me play around with some VBA.

 

[j_unsuitable]

Hey,

see if this works for you:

Sub finishedButton()
Dim lastRow As Long
Dim LastCol As Integer
Dim ws As Worksheet

With Worksheets("Info")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For Each ws In ThisWorkbook.Sheets(Array("Main", "Inventory", "Images"))
        With ws
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        .Range(.Cells(1, 1), .Cells(1, LastCol)).AutoFill _
            Destination:=.Range(.Cells(1, 1), .Cells(lastRow, LastCol))
        End With
Next ws

End Sub

Julian

 

[Tootlez]

Julian,
The Code does indeed work but i have a little problem,
This copied my headers (and the pages that have merged headers it didn't work on), I'm going to fiddle and see if I can figure it out myself, but additional help would be grateful too.
Thanks for the jump start!

T

 

[Tootlez]

(Just a little update on what research I've been looking at )
I removed the "Inventory" from the Array because it has merged cells Rows 1-4, and was throwing the code off and it wouldn't do the "Images" tab at all.
I've tried adjusting the Cells(x,x) hoping that i could get it to ignore the header row(s) and it only seems to break it.
Searching around I've found a few things that have a way to ignore the first row, but I can't seem to find the right place to insert that code into what I have now.

 

[j_unsuitable]

So the formulas you want to copy down are in the second row?
if so, change this line

.Range(.Cells(1, 1), .Cells(1, LastCol)).AutoFill _
            Destination:=.Range(.Cells(1, 1), .Cells(lastRow, LastCol))

to

.Range(.Cells([COLOR=#ff0000]2[/COLOR], 1), .Cells([COLOR=#ff0000]2[/COLOR], LastCol)).AutoFill _
            Destination:=.Range(.Cells([COLOR=#ff0000]2[/COLOR], 1), .Cells(lastRow, LastCol))

This should also work with merged cells in the first row
Also:

because it has merged cells Rows 1-4

Do you really mean rows or do you mean columns?
If you do that job manually, do you simply select the cells in row 2 and drag them down or are there any other steps included?

 

[Tootlez]

I tried changing the Cells(1,x) to Cells(2, x) and it didn't seem to work, I'll try it again, and see if I just copied it wrong or something.

My Inventory tab has 2 headers (1st row is the headers for the import (oblique names that mean nothing to me), and the 2nd (rows 2-4) are explanations in meaningful, and similar terms to the other sheets) So the Inventory formulas to copy down start on Row 5. So for right now I'll just skip it and see if Main and Images work, since both of those start on row 2.

Manually, I just select the row with the formula and drag it down until I hit the #N/As (meaning that I have hit the rows that are blank in the Info tab). Anything after the drag down is just error checking my formulas, since it's impossible to perfect terminology between brands.


[FONT=Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif](Edit: it worked!!! I was changing the first line's

Cells(1, .Columns.Count)...

to a 2, Good intentions but i should have left it alone.[/FONT]
[FONT=Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif]I am now working on a way to get the Inventory one to work. So i would technically need to add another grouping of those lines that uses the "Inventory" instead of the "Main" & "Images" but instead of the 2 I should be using a 5 ....[/FONT]

 

[Tootlez]

Ahhhh My attempt at making the Inventory to drop down worked! BUT ... It didn't go as far down as the others. My temporary Info tab is set up with 14 rows (minus my header and (2)blanks above my button), Main and images do well by grabbing even a blank above my button, but the Inventory stops 3 above where the others do ...

There maybe a simpler way to do it but I copied the For Each loop and changed the array to just "Inventory", and changed the Cells(1, x) to Cells(5, x)

For Each ws In ThisWorkbook.Sheets(Array("Inventory"))        With ws
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        .Range(.Cells(5, 1), .Cells(5, LastCol)).AutoFill _
            Destination:=.Range(.Cells(5, 1), .Cells(lastRow, LastCol))
        End With
Next ws

But again it stops 3 rows above what the others do ...i'll see if I can find something out myself but if you have an idea, let me know! (I was leaning towards doing lastRow+X

Thanks for your help so far, i'm extremely happy that this is possible!

T

 

[Tootlez]

I fixed my offset issue!!!

Taking lastRow and adding the extra 2 rows fixed the offset on the Inventory tab! Also by subtracting 1 on the others, fixed the extra row that i was getting (above the button)

Julian thanks for your code, it helped a ton!

 

[j_unsuitable]

Glad to see you making some progress.
And youre right about the lastRow + 3, but you dont need a loop if youre only applying the code to one worksheet, so

With Worksheets("Inventory")
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        .Range(.Cells(5, 1), .Cells(5, LastCol)).AutoFill _
            Destination:=.Range(.Cells(5, 1), .Cells(LastRow + 3, LastCol))
End With

should do the trick

 

[Tootlez]

Yeah I was figuring that I didn't need the loop, I was eager to see results, and I knew that code worked, so I just used it. Thanks for the simpler form!

I've been thinking about this button for a while, and finally got the book up to a good standing and figured this was one of the last steps to get this thing as close to entirely automated as I possibly can --- It's come a long way from when I've first started when they would manually type everything on these sheets!

I'm just stoked that this is finally working, thanks again SO much!!!!
(now I can get back to gathering my information and feel the power of the button when I'm done!)