Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For-each to copy rows to another workbook
I received help on this from Tom Ogilvy (which I appreciate, of
course), but I need additional help. I thought I would try to lay out what I needed differently, as I tend to be too wordy... This macro should go down a list, and copy rows based on 2 criteria. 1) to determine what the next workday is, like Monday, Tues, etc (a formula is in place to do this) 2) based on the next workday, it should read a certain table that contains the warehouse row numbers to look for in the source workbook. If tomorrow is a Tuesday, then it should look at a table with a range name of CycleCount_Tuesday, and during the For-each section, copy all rows from the source list that contain these warehouse row numbers, and paste them to the target workbook. Please look at the coding so far. Currently there are no errors given when I run this. The cursor ends up at the bottom of the source list like it should, but nothing at all is copied. My guess is that the assignment is not made during the If-Then-Else section, so it finds nothing that matches the criteria. Thanks to all for your help. J.O. Sub CycleCount() Dim RowsToCount As Range Dim CycleCount_Monday As Object Dim CycleCount_Tuesday As Object Dim CycleCount_Wednesday As Object Dim CycleCount_Thursday As Object Dim CycleCount_Friday As Object Dim DayOfWeek As Integer 'Opens up the Blank inventory workbook (target) ChDir "S:\Furniture Staging List\Staging List Inventories" Workbooks.Open Filename:= _ "S:\Furniture Staging List\Staging List Inventories\Inventory Wk of BLANK.xls" 'Switch back to original (source) file ThisWorkbook.Activate Worksheets("Cycle Count").Activate 'DayOfWeek is the numeric value of the next weekday based on a formula. 'CycleCount_Monday (Tues, Wed, etc) are ranges containing the warehouse rows. '*****I think this is where the problem is. Assignment value does not seem ' to be given to RowsToCount.***** If Range("DayOfWeek") = 2 Then Set RowsToCount = CycleCount_Monday ElseIf Range("DayOfWeek") = 3 Then Set RowsToCount = CycleCount_Tuesday ElseIf Range("DayOfWeek") = 4 Then Set RowsToCount = CycleCount_Wednesday ElseIf Range("DayOfWeek") = 5 Then Set RowsToCount = CycleCount_Thursday ElseIf Range("DayOfWeek") = 6 Then Set RowsToCount = CycleCount_Friday End If 'This sets the range name TempColumnName to the column on the source list 'to be read by the macro. Worksheets("Official List").Activate Application.Goto Reference:="FirstRowOfficialList" ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="TempColumnName", RefersToR1C1:= _ "='Official List'!R6C3:R416C3" 'Makes sure cursor starts at the top of the source list. Range("FirstRowOfficialList").Select ActiveCell.Offset(1, 0).Select 'Looks at each row for matching criteria based on above IF-Then-Else. For Each cell In Range("TempColumnName") If Not RowsToCount Is Nothing Then If cell.Value = RowsToCount Then CopyRows 'sub below that will perform copy routine Else MsgBox "Nothing copied because " & vbNewLine & _ "Value of Range DayofWeek is " & Range("DayofWeek").Value End If End If ActiveCell.Offset(1, 0).Select Next cell ThisWorkbook.Activate ActiveWorkbook.Names("TempColumnName").Delete End Sub Sub CopyRows() 'Copies rows to target workbook. Rows(ActiveCell.Row).Select Selection.Copy Windows("Inventory Wk of BLANK.xls").Activate Sheets("Data Dump").Select Range("B65536").End(xlUp).Offset(1, -1).Select ActiveSheet.Paste ThisWorkbook.Activate End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For-each to copy rows to another workbook
the only help I gave you was to put in a line of code to check if rowstocount
was actually set to something other than nothing. if Not RowsToCount is nothing then After I told you that was probably the problem. You didn't seem to understand what I was saying, but further in the thread came back and seemed to discover on your own that that appears to be the problem. two weeks later, you are saying that again appears to be the problem. If Range("DayOfWeek") = 2 Then Set RowsToCount = CycleCount_Monday ElseIf Range("DayOfWeek") = 3 Then Set RowsToCount = CycleCount_Tuesday ElseIf Range("DayOfWeek") = 4 Then Set RowsToCount = CycleCount_Wednesday ElseIf Range("DayOfWeek") = 5 Then Set RowsToCount = CycleCount_Thursday ElseIf Range("DayOfWeek") = 6 Then Set RowsToCount = CycleCount_Friday End If this code seems straight forward, If Range("DayofWeek) contains a 2, 3, 4, 5, or 6, it should attempt to assign RowsToCount to what I assume is a range variable such as CycleCount_Monday. I don't see anywhere in your code that you set CycleCount_Monday or any of the similar variables to refer to a location anywhere. And in this post, you say they are named ranges. You can't use a named range directly as a range variable. If the named range is named "CycleCount_Monday", you would refer to that range as Range("CycleCount_Monday") so retaining your declarations of CycleCount_Monday as an object variable and how you use it in your if statement, You would need to add lines like set CycleCount_Monday = Range("CycleCount_Monday") set CycleCount_Tuesday = Range("CycleCount_Tuesday") before you get to your if statement. or adjust your if statements If Range("DayOfWeek") = 2 Then Set RowsToCount = Range("CycleCount_Monday") ElseIf Range("DayOfWeek") = 3 Then Set RowsToCount = Range("CycleCount_Tuesday") ElseIf Range("DayOfWeek") = 4 Then Set RowsToCount = Range("CycleCount_Wednesday") ElseIf Range("DayOfWeek") = 5 Then Set RowsToCount = Range("CycleCount_Thursday") ElseIf Range("DayOfWeek") = 6 Then Set RowsToCount = Range("CycleCount_Friday") End If Again, this assumes that these are defined names in Insert=Name=Define If you get that working, you will next run into problems with a command like If cell.Value = RowsToCount Then Of RowToCount is a multicell range containing warehouse numbers, you would need something like if not iserror(applicaton.Match(cell.Value,rowsTocount,0) ) then this assumes that rowstocount refers to only a single column, multirow name (or single row, multicolumn). -- Regards, Tom Ogilvy "excelnut1954" wrote: I received help on this from Tom Ogilvy (which I appreciate, of course), but I need additional help. I thought I would try to lay out what I needed differently, as I tend to be too wordy... This macro should go down a list, and copy rows based on 2 criteria. 1) to determine what the next workday is, like Monday, Tues, etc (a formula is in place to do this) 2) based on the next workday, it should read a certain table that contains the warehouse row numbers to look for in the source workbook. If tomorrow is a Tuesday, then it should look at a table with a range name of CycleCount_Tuesday, and during the For-each section, copy all rows from the source list that contain these warehouse row numbers, and paste them to the target workbook. Please look at the coding so far. Currently there are no errors given when I run this. The cursor ends up at the bottom of the source list like it should, but nothing at all is copied. My guess is that the assignment is not made during the If-Then-Else section, so it finds nothing that matches the criteria. Thanks to all for your help. J.O. Sub CycleCount() Dim RowsToCount As Range Dim CycleCount_Monday As Object Dim CycleCount_Tuesday As Object Dim CycleCount_Wednesday As Object Dim CycleCount_Thursday As Object Dim CycleCount_Friday As Object Dim DayOfWeek As Integer 'Opens up the Blank inventory workbook (target) ChDir "S:\Furniture Staging List\Staging List Inventories" Workbooks.Open Filename:= _ "S:\Furniture Staging List\Staging List Inventories\Inventory Wk of BLANK.xls" 'Switch back to original (source) file ThisWorkbook.Activate Worksheets("Cycle Count").Activate 'DayOfWeek is the numeric value of the next weekday based on a formula. 'CycleCount_Monday (Tues, Wed, etc) are ranges containing the warehouse rows. '*****I think this is where the problem is. Assignment value does not seem ' to be given to RowsToCount.***** If Range("DayOfWeek") = 2 Then Set RowsToCount = CycleCount_Monday ElseIf Range("DayOfWeek") = 3 Then Set RowsToCount = CycleCount_Tuesday ElseIf Range("DayOfWeek") = 4 Then Set RowsToCount = CycleCount_Wednesday ElseIf Range("DayOfWeek") = 5 Then Set RowsToCount = CycleCount_Thursday ElseIf Range("DayOfWeek") = 6 Then Set RowsToCount = CycleCount_Friday End If 'This sets the range name TempColumnName to the column on the source list 'to be read by the macro. Worksheets("Official List").Activate Application.Goto Reference:="FirstRowOfficialList" ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="TempColumnName", RefersToR1C1:= _ "='Official List'!R6C3:R416C3" 'Makes sure cursor starts at the top of the source list. Range("FirstRowOfficialList").Select ActiveCell.Offset(1, 0).Select 'Looks at each row for matching criteria based on above IF-Then-Else. For Each cell In Range("TempColumnName") If Not RowsToCount Is Nothing Then If cell.Value = RowsToCount Then CopyRows 'sub below that will perform copy routine Else MsgBox "Nothing copied because " & vbNewLine & _ "Value of Range DayofWeek is " & Range("DayofWeek").Value End If End If ActiveCell.Offset(1, 0).Select Next cell ThisWorkbook.Activate ActiveWorkbook.Names("TempColumnName").Delete End Sub Sub CopyRows() 'Copies rows to target workbook. Rows(ActiveCell.Row).Select Selection.Copy Windows("Inventory Wk of BLANK.xls").Activate Sheets("Data Dump").Select Range("B65536").End(xlUp).Offset(1, -1).Select ActiveSheet.Paste ThisWorkbook.Activate End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For-each to copy rows to another workbook
I've tried different things to eliminate the error I get. It's a 424
Object required error shown below. See the ****************** marker. Since my VBA projects are a secondary part of my job, I'm able to dive into it only occasionally. So, since Tom last responded, I've spent some time trying to learn about what might be causing this error. I'm stuck. I've tried adding different Dim statements, Set statements. I don't know what Object the error is referring to. Do I need to declare the individual sheets as objects? Each workbook? Any suggestions would be appreciated. Thanks, J.O. Sub CycleCount() Dim RowsToCount As Range Dim CycleCount_Monday As Object Dim CycleCount_Tuesday As Object Dim CycleCount_Wednesday As Object Dim CycleCount_Thursday As Object Dim CycleCount_Friday As Object Dim DayOfWeek As Integer 'Opens up the Blank inventory workbook (target) ChDir "S:\Furniture Staging List\Staging List Inventories" Workbooks.Open Filename:= _ "S:\Furniture Staging List\Staging List Inventories\Inventory Wk of BLANK.xls" 'Switch back to original (source) file ThisWorkbook.Activate 'DayOfWeek is the numeric value of the next weekday based on a formula. 'CycleCount_Monday, Tues, etc are ranges containing the warehouse row 'numbers to look for on the list. With Sheets("Cycle Count") If Range("DayOfWeek") = 2 Then Set RowsToCount = Range("CycleCount_Monday") ElseIf Range("DayOfWeek") = 3 Then Set RowsToCount = Range("CycleCount_Tuesday") ElseIf Range("DayOfWeek") = 4 Then Set RowsToCount = Range("CycleCount_Wednesday") ElseIf Range("DayOfWeek") = 5 Then Set RowsToCount = Range("CycleCount_Thursday") ElseIf Range("DayOfWeek") = 6 Then Set RowsToCount = Range("CycleCount_Friday") End If End With Worksheets("Official List").Activate Application.Goto Reference:="FirstRowOfficialList" ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="TempColumnName", RefersToR1C1:= _ "='Official List'!R6C3:R416C3" Range("FirstRowOfficialList").Select ActiveCell.Offset(1, 0).Select 'Looks at each row for matching criteria based on above IF-Then-Else. With Sheets("Official List") For Each cell In Range("TempColumnName") If Not RowsToCount Is Nothing Then '********this is where I get "Runtime error 424 Object Required" error *********** If Not IsError(applicaton.Match(cell.Value, RowsToCount, 0)) Then CopyRows 'sub to perform copy routine Else MsgBox "Nothing copied because " & vbNewLine & _ "Value of Range DayofWeek is " & Range("DayofWeek").Value End If End If ActiveCell.Offset(1, 0).Select Next cell End With ThisWorkbook.Activate ActiveWorkbook.Names("TempColumnName").Delete End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For-each to copy rows to another workbook
applicaton
Should be spelled properly.... application HTH, Bernie MS Excel MVP "excelnut1954" wrote in message ups.com... I've tried different things to eliminate the error I get. It's a 424 Object required error shown below. See the ****************** marker. Since my VBA projects are a secondary part of my job, I'm able to dive into it only occasionally. So, since Tom last responded, I've spent some time trying to learn about what might be causing this error. I'm stuck. I've tried adding different Dim statements, Set statements. I don't know what Object the error is referring to. Do I need to declare the individual sheets as objects? Each workbook? Any suggestions would be appreciated. Thanks, J.O. Sub CycleCount() Dim RowsToCount As Range Dim CycleCount_Monday As Object Dim CycleCount_Tuesday As Object Dim CycleCount_Wednesday As Object Dim CycleCount_Thursday As Object Dim CycleCount_Friday As Object Dim DayOfWeek As Integer 'Opens up the Blank inventory workbook (target) ChDir "S:\Furniture Staging List\Staging List Inventories" Workbooks.Open Filename:= _ "S:\Furniture Staging List\Staging List Inventories\Inventory Wk of BLANK.xls" 'Switch back to original (source) file ThisWorkbook.Activate 'DayOfWeek is the numeric value of the next weekday based on a formula. 'CycleCount_Monday, Tues, etc are ranges containing the warehouse row 'numbers to look for on the list. With Sheets("Cycle Count") If Range("DayOfWeek") = 2 Then Set RowsToCount = Range("CycleCount_Monday") ElseIf Range("DayOfWeek") = 3 Then Set RowsToCount = Range("CycleCount_Tuesday") ElseIf Range("DayOfWeek") = 4 Then Set RowsToCount = Range("CycleCount_Wednesday") ElseIf Range("DayOfWeek") = 5 Then Set RowsToCount = Range("CycleCount_Thursday") ElseIf Range("DayOfWeek") = 6 Then Set RowsToCount = Range("CycleCount_Friday") End If End With Worksheets("Official List").Activate Application.Goto Reference:="FirstRowOfficialList" ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="TempColumnName", RefersToR1C1:= _ "='Official List'!R6C3:R416C3" Range("FirstRowOfficialList").Select ActiveCell.Offset(1, 0).Select 'Looks at each row for matching criteria based on above IF-Then-Else. With Sheets("Official List") For Each cell In Range("TempColumnName") If Not RowsToCount Is Nothing Then '********this is where I get "Runtime error 424 Object Required" error *********** If Not IsError(applicaton.Match(cell.Value, RowsToCount, 0)) Then CopyRows 'sub to perform copy routine Else MsgBox "Nothing copied because " & vbNewLine & _ "Value of Range DayofWeek is " & Range("DayofWeek").Value End If End If ActiveCell.Offset(1, 0).Select Next cell End With ThisWorkbook.Activate ActiveWorkbook.Names("TempColumnName").Delete End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For-each to copy rows to another workbook
Thanks Bernie.
That was it. I spent more than an hour checking spelling of range names, etc. I usualy copy commands like this. But, must have hit the delete key...... Thanks again J.O. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
For-each to copy rows to another workbook
One tip is always type in lower case - if you don't make a mistake, then excel will change the case,
depending on the object name or declaration of a variable (Always use at least one cap in every variable name). The other tip is to use autocomplete. When you type application. a dialog with properties and methods should show up, so if you typed applicaton. no dialog would show up and you would know that you misspelled application. HTH, Bernie MS Excel MVP "excelnut1954" wrote in message ups.com... Thanks Bernie. That was it. I spent more than an hour checking spelling of range names, etc. I usualy copy commands like this. But, must have hit the delete key...... Thanks again J.O. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Autofiltered rows to another workbook using macro | Excel Discussion (Misc queries) | |||
copy rows and paste in another workbook | Excel Programming | |||
Copy Range with Additional Rows to New Workbook | Excel Programming | |||
copy rows to another workbook | Excel Discussion (Misc queries) | |||
how to copy succeeding rows from source workbook to destination w. | Excel Programming |