Setting up an archive (VBA)

[dlamb43]

Hey guys, I've been trying to set up an archive where you enter in values on Sheet 1 and then press the button so that it moves it into Sheet 2. All existing rows with their stored data in Sheet 2 would move down one to accommodate what was entered on Sheet 1. Here's the snippet I cooked up for it to work. It sort of does what I want, but it ends up making every row the same as the one just entered.

n = Worksheets("Sheet2").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count


    For i = 1 To 6


        For j = 3 To n


            Worksheets("Sheet2").Cells(j+1, i) = Worksheets("Sheet2").Cells(j, i)


        Next j


            Worksheets("Sheet2").Cells(3, i) = Worksheets("Sheet1").Cells(3, i)


    Next i

There's probably a far nicer way of doing this, but a for loop is all I could think of. The first part of the loop is supposed to shift all the data down (pretty sure this is where my problem is), the second copies data from Sheet1 (which is entered in row 3) and pastes it into Sheet2.

Any help would be greatly appreciated!

 

[dlamb43]

Re: Help with setting up an archive (VBA)

In fact that is exactly my problem. The code is basically saying:

ROW 2 = ROW 1
THEN
ROW 3 = ROW 2 (Which already = ROW 1)
THEN
ROW 4 = ROW 3 (Which already = ROW 2 = ROW 1)

Then that code at the end is what pastes the new set of data to be archived into Sheet2 so it looks like it's somewhat doing what I want haha.

 

[dlamb43]

Re: Help with setting up an archive (VBA)

So I guess my question is:

Is there a nice way where you can get VB to shift all rows down one (but keep their data intact) and add a new row of data in the vacant row at the top?

 

[footoo]

Re: Help with setting up an archive (VBA)

Try creating a macro from the macro recorder :
- Select the row(s) on Sheet1 and Copy
- Go to Sheet2 and InsertCopiedCells

 

[dlamb43]

Re: Help with setting up an archive (VBA)

This is great except there are multiple sheets. The way the data is archived is due to a selection made on Sheet1. Sorry for not clarifying earlier, I just thought I better wrap my head around trying to get it done once first.

 

[footoo]

Re: Help with setting up an archive (VBA)

I just thought I better wrap my head around trying to get it done once first.

In that case follow my suggestion.
If you want to do it for multiple sheets, the Copy/Insert code would be the same - you would just need to loop through the required sheets.

 

[dlamb43]

Re: Help with setting up an archive (VBA)

In that case follow my suggestion.
If you want to do it for multiple sheets, the Copy/Insert code would be the same - you would just need to loop through the required sheets.

I just want to say thanks so much for your help. Doing that really helped me with understanding commands (safe to saw I'd never have got there ) and it's all working seamlessly now. You're a true champion!