Macro to find last row of data and insert row below

[kmarsden]

Hi There,

I am using a spreadsheet with lots of tabs on it. I have a main tab, then 10 different tabs for machine numbers. The main tab gets updated daily with machine issues. I want to be able to update the relevant machine tab, (as in Machine 1), with the text in that machines cell on the main tab, but KEEP the data that is already on the machine tab, which is a rolling history. Any ideas?
Thanks in advance

 

[NewOrderFac33]

Welcome to the forum!

Just a suggestion, but wouldn't it be easier just to have 2 tabs, one for the info that comes in each day, and a second, which would contain cumulative data for ALL your machines, into which data from the first tab is copied daily, or at whatever frequency is appropriate.
Then, just filter the information in the second tab to the machine number for which you want to see the information - much easier to keep control of than multiple tabs IMHO (this of course assumes that your data has a field containing a machine identifier for each issue record)

Pete

 

[NeonRedSharpie]

Sub updateMachineTabs()

    For x = 2 To Sheets.Count
        With Sheets(x)
            .Cells(.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1) = Sheets(1).Cells(x, 1)
        End With
    Next x


End Sub

So this is shell code because I don't know your workbook. But, what it does, is look through all your sheets EXCEPT your main tab (assuming your main tab is the first sheet), find the last row, and then insert a value into column A, last row + 1. The value it is grabbing is from the MAIN sheet, same row as the sheet number, and column A. So this assumes (most likely incorrectly) that your MACHINE 1 information you want to copy is in A2. Then your MACHINE 2 information is in A3.

 

[kmarsden]

Welcome to the forum!

Just a suggestion, but wouldn't it be easier just to have 2 tabs, one for the info that comes in each day, and a second, which would contain cumulative data for ALL your machines, into which data from the first tab is copied daily, or at whatever frequency is appropriate.
Then, just filter the information in the second tab to the machine number for which you want to see the information - much easier to keep control of than multiple tabs IMHO (this of course assumes that your data has a field containing a machine identifier for each issue record)

Pete

Thanks Pete, but I am sort of having to use this format as my colleagues use the same sheet at another location, but I will suggest it. Thanks for the advice.

 

[kmarsden]

Thanks Neon, I will give this a try when time allows.

 

[kmarsden]

Hi Neon,
Just been messing with this and it looks like it will work. There is just one thing though, I only want it to update the 10 Tabs after the first one. I assume I need to change the "For x = 2 to Sheets.Count" part...?

Regards,

Karl

 

[NeonRedSharpie]

Right. For x = 2 to 11.

 

[kmarsden]

Right. For x = 2 to 11.

Ok, next problem....The above part works, but the data I want to extract and update starts in the first tab at cell H4. At the moment, the macro is starting at cell H1....how do I change that?

Regards,
Karl