Solved Creating an Archive Macro

June 14, 2016 at 07:26:27
Specs: Windows 7
I want to cut some rows in my excel workbook and paste them to another tab in the same workbook. The main tab contain about 1400+ projects. i want to archive the projects that are closed/completed to a new tab.
I would appreciate any help!

See More: Creating an Archive Macro

Report •

✔ Best Answer
June 17, 2016 at 09:35:59
I suggest that you try this code in a backup copy of your workbook. Since it deletes rows, you may not want to mess up your original sheet in case it gets saved.

You might also want to reduce the size of the data. There is no need to test the code on 1400 rows.

Assumptions:

Your source sheet is named "Main"
Your destination sheet is named "Blue Items"
You have data in Column A of every row (Column A is used to determine the length of your data)

All of this can be edited to fit your exact situation.


Sub CutToBlue()
'Determine last Row with data on Main:
   lastRw = Sheets("Main").Range("A" & Rows.Count).End(xlUp).Row
'Loop through Main Column Q in reverse order
     For srcRw = lastRw To 1 Step -1
'Check for B in Main Column Q
      If Sheets("Main").Range("Q" & srcRw) = "B" Then
'Determine Paste location on Blue Items
        nxtRw = Sheets("Blue Items").Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy/Paste Row
         Sheets("Main").Range("Q" & srcRw).EntireRow.Copy _
         Sheets("Blue Items").Range("A" & nxtRw)  '.PasteSpecial Paste:=xlPasteAll
'Delete orginal Row on Main
          Sheets("Main").Range("Q" & srcRw).EntireRow.Delete
      End If
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
June 14, 2016 at 07:42:46
We will need more specifics before we can offer any help.

The first thing that would help is knowing your level of VBA expertise. Do you need a complete and working macro specific to your workbook or can you take some generic code and modify it to fit your needs?

You used the words "cut some rows". Was it your intention to manually select the rows to be "cut" or are you expecting the code to determine which rows to "cut" and then place in the other tab? If you are expecting the code to determine which projects are "closed/completed" we will need to know how those projects are designated as "closed/completed". The code can't "think", it can only do what we tell it to do. We need to tell it what to look for and what to do with it.

Please keep in mind that we can't see your workbook from where we are sitting, so the more detail you provide the better.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 14, 2016 at 08:03:18
Hi and thanks for your reply. Yes my bad for too little info.

My VBA expertise is quite low and I need a working macro.
The main tab has 20 columns. The 17th column (Q) will be marked B as to show completed/closed. I need the macro to automatically recognize the rows with Column Q cell containing B cut and paste them in the other tab named Blue Items.
Is this sufficient info?


Report •

#3
June 14, 2016 at 08:05:27
Oh 3 of the columns contain formulas and while cutting and pasting I will need the macro to retain the formulas. (if that's possible at all)

Report •

Related Solutions

#4
June 14, 2016 at 12:31:01
I assume that you want each "cut" row to be pasted to the bottom of the list on Blue Items.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
June 16, 2016 at 07:22:56
Yes. That is correct. I need to cut rows and paste them to the bottom of the Blue Items tab.

Report •

#6
June 16, 2016 at 07:24:46
I also need to make sure that the format is retained, meaning the formulas in the columns L, P and S remain.

Report •

#7
June 16, 2016 at 21:13:51
Retaining the format does not mean the formulas remain.

What do you want to retain: the formatting of the cells, the formulas in the cells, or both?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
June 17, 2016 at 06:19:26
Both. But that is not a priority. First I would like to test the archive and see how it works out. The goal is to ultimately retain both the format and formulas though.

Report •

#9
June 17, 2016 at 09:35:59
✔ Best Answer
I suggest that you try this code in a backup copy of your workbook. Since it deletes rows, you may not want to mess up your original sheet in case it gets saved.

You might also want to reduce the size of the data. There is no need to test the code on 1400 rows.

Assumptions:

Your source sheet is named "Main"
Your destination sheet is named "Blue Items"
You have data in Column A of every row (Column A is used to determine the length of your data)

All of this can be edited to fit your exact situation.


Sub CutToBlue()
'Determine last Row with data on Main:
   lastRw = Sheets("Main").Range("A" & Rows.Count).End(xlUp).Row
'Loop through Main Column Q in reverse order
     For srcRw = lastRw To 1 Step -1
'Check for B in Main Column Q
      If Sheets("Main").Range("Q" & srcRw) = "B" Then
'Determine Paste location on Blue Items
        nxtRw = Sheets("Blue Items").Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy/Paste Row
         Sheets("Main").Range("Q" & srcRw).EntireRow.Copy _
         Sheets("Blue Items").Range("A" & nxtRw)  '.PasteSpecial Paste:=xlPasteAll
'Delete orginal Row on Main
          Sheets("Main").Range("Q" & srcRw).EntireRow.Delete
      End If
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
June 17, 2016 at 09:44:04
Hi,

Many thanks! I will test this and get back to you. All the assumptions above are valid!


Report •

#11
June 17, 2016 at 12:35:48
If you haven't tested it yet why did you mark the thread as "Solved"?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
June 20, 2016 at 05:30:27
I did test it! And it works perfectly!

Report •

#13
July 20, 2016 at 13:42:14
Hi, I'm back with a new question, is it possible for you to take a look?
Thank you.

http://www.computing.net/answers/of...

message edited by AngieP


Report •

Ask Question