Script to update inventory.

[ItsImpulse]

Hi there! I'm not that familiar with using excel macros and formulae but I need help with creating something that will help me update inventory.

Basically I have multiple sheets named Store 1, Store 2 etc. and I have a sheet called summary.

I would need the summary sheet to automatically copy the rows 1,2 and 3 from Store 1, Store 2..... until the last store.

Each store has a different amount of columns so how would I go about doing this?

 

[My Aswer Is This]

So we want to copy rows 1,2 and three from all sheets in your workbook and put them in sheet named "Summary" is that correct?

Is the Summary sheet sheet(1)
Meaning it is in the far left position on tab bar.

So we can copy all sheets from sheet (2) to last sheet into sheet(1)

And when you say "Automatically" do you mean when you click a button or use a short cut key.

Nothing in Excel happens automatically.

 

[My Aswer Is This]

Try this:

Sub Copy_To_Summary()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "Summary" Then
            Sheets(i).Rows("1:" & 3).Copy Sheets("Summary").Rows(Lastrow)
            Lastrow = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
        End If
    Next
Application.ScreenUpdating = True
End Sub

 

[ItsImpulse]

Hi! My Summary Sheet is the last sheet. I meant by pressing a button.

 

[My Aswer Is This]

Try the script in post 3

Hi! My Summary Sheet is the last sheet. I meant by pressing a button.

 

[ItsImpulse]

sorry I forgot to add. I have another sheet called database inside the workbook but it has nothing to do with the summary

 

[My Aswer Is This]

Try this:

Make sure sheet names marked in red are exactly correct. Otherwise modify script.

Sub Copy_To_Summary()
'Modified 9-21-17 3:00 AM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "[COLOR=#ff0000]Summary[/COLOR]" And Sheets(i).Name <> "[COLOR=#ff0000]database[/COLOR]" Then
            Sheets(i).Rows("1:" & 3).Copy Sheets("Summary").Rows(Lastrow)
            Lastrow = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
        End If
    Next
Application.ScreenUpdating = True
End Sub

 

[ItsImpulse]

hi sorry for the trouble but it copies row 1 from Store 1, Row 1 from Store 2 and then Roe 1 to 3 from Store 3 and stacks them on top of each other. Can it be row 1 to 3 for all the 3 stores and side by side?

 

[My Aswer Is This]

I test all my scripts and it copies rows 1 to 3 from all sheets to the summary sheet.
This is assuming you always have values in column "A"

Do not understand "and side by side"

hi sorry for the trouble but it copies row 1 from Store 1, Row 1 from Store 2 and then Roe 1 to 3 from Store 3 and stacks them on top of each other. Can it be row 1 to 3 for all the 3 stores and side by side?

 

[ItsImpulse]

my bad. there was an empty box. by "side by side I mean that say store 1 has values from A to F and store 2 has values from A to D. I need the summary to have values from A to J. I.e. only rows 1,2 and 3 have data