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]

In your original post you said:
"I would need the summary sheet to automatically copy the rows 1,2 and 3 from Store 1, Store 2..... until the last store."

Now your saying just copy part of the row.

This is now beyond my knowledgebase.

Someone else here at Mr. Excel will need to help you.

 

[ItsImpulse]

oh no it's to copy the entire row. I meant that if the last column of store 1 was f I need store 2s stuff to appear from g onwards. and so on

 

[Peter_SSs]

oh no it's to copy the entire row.

I don't think you do mean that. I think you mean the the rows only as far as the data extends to the right.

The reason is, if you are using a recent Excel version then each row has 16,384 columns. Suppose your first Store sheet has data in columns A:J (10 columns) and you paste that data in columns A:J of the Summary sheet.
In the Summary sheet, there are now only 16,374 vacant columns available for the next paste, so you cannot paste all 16,384 columns from the second Store sheet - they won't fit!!

However, I think we can pretty easily accommodate what you want. Give this a try.

Sub Copy_Data()
  Dim nc As Long
  Dim ws As Worksheet, wsSumm As Worksheet
  
  Set wsSumm = Sheets("Summary")
  For Each ws In Worksheets
    If ws.Name <> "Summary" And ws.Name <> "database" Then
      nc = 1
      On Error Resume Next
      nc = wsSumm.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
      On Error GoTo 0
      ws.UsedRange.Resize(3).Copy Destination:=wsSumm.Cells(1, nc)
    End If
  Next ws
End Sub

 

[ItsImpulse]

thank you so much! it works perfectly

 

[Peter_SSs]

thank you so much! it works perfectly

You are very welcome.