Macro to find last row of data and insert row below

kmarsden

New Member
Joined
Oct 9, 2014
Messages
9
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,252
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

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Code:
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

New Member
Joined
Oct 9, 2014
Messages
9
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

New Member
Joined
Oct 9, 2014
Messages
9

ADVERTISEMENT

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

kmarsden

New Member
Joined
Oct 9, 2014
Messages
9
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
 

kmarsden

New Member
Joined
Oct 9, 2014
Messages
9
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,359
Messages
5,528,226
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top