Solved

Copy range of cells from one workbook to newly created workbook based on selection criterion

Posted on 2008-06-11
14
196 Views
Last Modified: 2011-10-19
I have a spreadsheet with values that I need to copy to another based on a set of rules.  I started a macro but cannot figure out how to tell it to run for the number of rows in the spreadsheet. The only thing I do know is that somewhere in the loop I need to tell it the last row.  I have attached my spreadsheet and code for review.  Any help would be appreciated.
Sub CreateFile()

    Dim statuswb As Workbook

    

    Dim I As Integer

    Dim NS As Integer

    Dim LastRow As Integer

        

    I = 2 'Beginning point of the loop

    NS = 1 'Beginning point of load file for status

    LastRow = Range("A65536").End(xlUp).Row + 1

    

    Set statuswb = Workbooks.Add

    ActiveWorkbook.SaveAs Filename:="C:\my documents\Status" & CStr(Format(Now, "MMDDYYHHm")) & ".xls"

    

    Windows("CreateFileDev.xls").Activate

    

    Do While Cells(I, 10).Value <> "Y" And Cells(I, 11) <> "Not in System"

       If Cells(I, 1) = "Modify Status" Then

       Cells(I, 12).Select

       Selection.Copy

       statuswb.Activate

       Cells(NS, 1).Select

       Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

       Windows("CreateFileDev.xls").Activate

       Cells(I, 10).Select

       ActiveCell.FormulaR1C1 = "Y"

       End If

    I = I + 1

    NS = NS + 1

    Loop
 

End Sub

Open in new window

CreateFileDev.xls
0
Comment
Question by:ilrosebud26
  • 5
  • 5
  • 4
14 Comments
 
LVL 3

Expert Comment

by:supdawgtt94
ID: 21763212
The simplest way to end it is to test for a known value that will never happen for a data row.  For example, if you know all the rows in the sheet in a specific column has to contain data, you can check for no data, and then exit your loop.  If you might have empty cells in that column of data, you could check for a keyword like "end" that you enter into the test cell after the last row of data.

Here, I'm testing for the first column of a row to not be "end".  If it is, exit the loop.  Otherwise, test every row in the sheet for the two conditions to be met for processing.  I'm assuming you manually type in the word end into the last row's first cell.

    Do While lcase(Cells(I, 1)) <> "end"
    if Cells(I, 10).Value <> "Y" And Cells(I, 11) <> "Not in System"
       If Cells(I, 1) = "Modify Status" Then
       Cells(I, 12).Select
       Selection.Copy
       statuswb.Activate
       Cells(NS, 1).Select
       Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
       Windows("CreateFileDev.xls").Activate
       Cells(I, 10).Select
       ActiveCell.FormulaR1C1 = "Y"
       End If
    I = I + 1
    NS = NS + 1
    Loop
 
End Sub
0
 
LVL 24

Expert Comment

by:purplepomegranite
ID: 21763417
In my opinion, the best way to process data tables like this is to use Excel's built-in function to return the last cell that contains data, as attached.
Sub CreateFile()

    Dim statuswb As Workbook

    

    Dim I As Integer

    Dim NS As Integer

    Dim LastRow As Integer

        

    I = 2 'Beginning point of the loop

    NS = 1 'Beginning point of load file for status

    LastRow = Range("A1").SpecialCells(xlCellTypeLastCell).Row

    

    Set statuswb = Workbooks.Add

    ActiveWorkbook.SaveAs Filename:="C:\my documents\Status" & CStr(Format(Now, "MMDDYYHHm")) & ".xls"

    

    Windows("CreateFileDev.xls").Activate

    

    Do While I <= LastRow

       If Cells(I, 1) = "Modify Status" Then

       Cells(I, 12).Copy

       statuswb.Activate

       Cells(NS, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

       Windows("CreateFileDev.xls").Activate

       Cells(I, 10).FormulaR1C1 = "Y"

       End If

      I = I + 1

      NS = NS + 1

    Loop

    

End Sub

Open in new window

0
 
LVL 24

Expert Comment

by:purplepomegranite
ID: 21763484
You can also speed the routine up considerably if instead of activating each worksheet as you copy and paste, you set up variables and reference them directly.  You may not notice a huge speed increase on the small table you have in your spreadsheet, but on a large table there will be a huge difference.
Sub CreateFile()

    Dim statuswb As Workbook

    Dim createFileDevwb As Workbook

    Dim createFileDevWS As Worksheet

    Dim statusWS As Worksheet

    

    Dim I As Integer

    Dim NS As Integer

    Dim LastRow As Integer

        

    I = 2 'Beginning point of the loop

    NS = 1 'Beginning point of load file for status

    LastRow = Range("A1").SpecialCells(xlCellTypeLastCell).Row

    

    Set createFileDevwb = ActiveWorkbook ' Obtain a reference to this workbook (assumed active at macro start)

    Set createFileDevWS = ActiveSheet

    Set statuswb = Workbooks.Add

    ActiveWorkbook.SaveAs Filename:="C:\my documents\Status" & CStr(Format(Now, "MMDDYYHHm")) & ".xls"

    Set statusWS = ActiveSheet

    

    

   Do While I <= LastRow

      If createFileDevWS.Cells(I, 1) = "Modify Status" Then

         statusWS.Cells(NS, 1).Value = createFileDevWS.Cells(I, 12).Value

         createFileDevWS.Cells(I, 10).FormulaR1C1 = "Y"

      End If

      I = I + 1

      NS = NS + 1

   Loop

    

End Sub

Open in new window

0
 

Author Comment

by:ilrosebud26
ID: 21763591
purplepomegranite-
    Thanks for you quick reply.  I will be able to try this out in the morning.  I had to change gears for the rest of the day.

Rosemary
0
 

Author Comment

by:ilrosebud26
ID: 21769799
purplepomegranite-
   This worked great!  I just had the user through in another change.  On the copy paste comand they want to copy the values in columns L(12) and M(13) to columns A(1) and B(2) on the vewly created workbook.  Can you help me with that?  I have increased the point value to reflect the additional question.

Thanks!
0
 
LVL 24

Accepted Solution

by:
purplepomegranite earned 350 total points
ID: 21769877
If I understand correctly, that is achieved by adding one line, as below.
Sub CreateFile()

    Dim statuswb As Workbook

    Dim createFileDevwb As Workbook

    Dim createFileDevWS As Worksheet

    Dim statusWS As Worksheet

    

    Dim I As Integer

    Dim NS As Integer

    Dim LastRow As Integer

        

    I = 2 'Beginning point of the loop

    NS = 1 'Beginning point of load file for status

    LastRow = Range("A1").SpecialCells(xlCellTypeLastCell).Row

    

    Set createFileDevwb = ActiveWorkbook ' Obtain a reference to this workbook (assumed active at macro start)

    Set createFileDevWS = ActiveSheet

    Set statuswb = Workbooks.Add

    ActiveWorkbook.SaveAs Filename:="C:\my documents\Status" & CStr(Format(Now, "MMDDYYHHm")) & ".xls"

    Set statusWS = ActiveSheet

    

    

   Do While I <= LastRow

      If createFileDevWS.Cells(I, 1) = "Modify Status" Then

         statusWS.Cells(NS, 1).Value = createFileDevWS.Cells(I, 12).Value

         statusWS.Cells(NS, 2).Value = createFileDevWS.Cells(I, 13).Value

         createFileDevWS.Cells(I, 10).FormulaR1C1 = "Y"

      End If

      I = I + 1

      NS = NS + 1

   Loop

    

End Sub

Open in new window

0
 

Author Closing Comment

by:ilrosebud26
ID: 31466288
You have been a great help with this.  Thanks for your patience with a beginner!
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:ilrosebud26
ID: 21770759
purplepomegranite-
After executing I realized that the new created sheet is not starting records in cell A1.  Why?  If I need to open an additional question....just let me know.

Rosemary
0
 
LVL 24

Expert Comment

by:purplepomegranite
ID: 21770927
It will copy the data to the same row in the new sheet i.e. if the data is in row 3 on the source sheet, it will be in row 3 of the target sheet.  Also, if there is a gap (e.g. one line does not have a modify status set), then there will also be a gap on the results.

This can be amended, but probably does warrant another question.  Try and be as specific as you can about how you want the output presented.
0
 
LVL 3

Expert Comment

by:supdawgtt94
ID: 21770983
I hope this is not out of protocol here, but I'll throw you the answer anyway.

First, remove this line.  It's not used.

Set statuswb = Workbooks.Add

Second, to do what you want, move the line
      NS = NS + 1
into the if statement.

That'll only increment the row count as it copies the row into the new workbook.
0
 
LVL 3

Expert Comment

by:supdawgtt94
ID: 21771002
My last line wasn't clear.  Here's what the loop should look like:

   Do While I <= LastRow
      If createFileDevWS.Cells(I, 1) = "Modify Status" Then
         statusWS.Cells(NS, 1).Value = createFileDevWS.Cells(I, 12).Value
         statusWS.Cells(NS, 2).Value = createFileDevWS.Cells(I, 13).Value
         createFileDevWS.Cells(I, 10).FormulaR1C1 = "Y"
         NS = NS + 1
      End If
      I = I + 1
   Loop
0
 
LVL 24

Expert Comment

by:purplepomegranite
ID: 21771336
The line:
Set statuswb = Workbooks.Add
IS used, it's just the variable that isn't.  The line shouldn't be removed.  It could be changed to:
Workbooks.Add
However, leaving it as it is means that there is a reference to the workbook in case it is needed for future modifications.
0
 
LVL 3

Expert Comment

by:supdawgtt94
ID: 21771801
You're right purple.  I'm helping someone else with the same issue but working in the same workbook, so I got confused.
0
 

Author Comment

by:ilrosebud26
ID: 21772652
I want to thank everyone for there help.  This works now.  I will try to be clearer on what I need.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel - count records based name and based on null and not null 5 31
Child Form in front 4 37
Freeze Panes Solution 6 28
Most Consistent Performer 4 20
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now