Excel Prob
 

I am writing a routine. Excel reads a list, and based on the value in
Column H, Excel will cut-n-paste the entire row from Worksheet A to
Worksheet B, C, or even Z, based on the value found in column H.

Private Sub cmdDoParse_Click()

Dim lastrow As Integer
Dim wks As Worksheet
Dim Codes As Range
Dim i As Integer
Dim importwks As String
Dim targetwks As String
Dim codetable As Range
Dim targetrow As Integer

' first thing is to parse the number of rows on the imported data sheet

Application.ScreenUpdating = False

Sheets("Import").Select
lastrow = Range("B65536").End(xlUp).Row - 1

For i = 1 To lastrow

Range("H2").Select

importwks = Range("H2").Value

targetrow = Sheets(importwks).Range("B65536").End(xlUp).Row + 1

Range("A2").EntireRow.Cut
Destination:=(Sheets(importwks).Range(targetrow))

Next i

Application.ScreenUpdating = True

End Sub

I always get a 1004 Error when it hits the Range-EntireRow cut
statement. Importwks is the name of the target worksheet, which is
found in Column H (this comes from another table, based on a vlookup),
Targetrow is the last row used on importwks.

Clear as mud yet? TIA - Replies to the group as I no longer use this
email address.

Excel Prob
 

Doug

you need to add a qualifier to the range address or use a different method,
(I have used A as an illustration

Range("A2").EntireRow.Cut _
Destination:=Sheets(importwks).Range("A" & targetrow)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


wrote in message
ups.com...
I am writing a routine. Excel reads a list, and based on the value in
Column H, Excel will cut-n-paste the entire row from Worksheet A to
Worksheet B, C, or even Z, based on the value found in column H.

Private Sub cmdDoParse_Click()

Dim lastrow As Integer
Dim wks As Worksheet
Dim Codes As Range
Dim i As Integer
Dim importwks As String
Dim targetwks As String
Dim codetable As Range
Dim targetrow As Integer

' first thing is to parse the number of rows on the imported data sheet

Application.ScreenUpdating = False

Sheets("Import").Select
lastrow = Range("B65536").End(xlUp).Row - 1

For i = 1 To lastrow

Range("H2").Select

importwks = Range("H2").Value

targetrow = Sheets(importwks).Range("B65536").End(xlUp).Row + 1

Range("A2").EntireRow.Cut
Destination:=(Sheets(importwks).Range(targetrow))

Next i

Application.ScreenUpdating = True

End Sub

I always get a 1004 Error when it hits the Range-EntireRow cut
statement. Importwks is the name of the target worksheet, which is
found in Column H (this comes from another table, based on a vlookup),
Targetrow is the last row used on importwks.

Clear as mud yet? TIA - Replies to the group as I no longer use this
email address.

Excel Prob
 

Use this:

Rows("2:2").Select
Selection.Cut
Sheets(importwks).Select
Range(targetrow).Select
ActiveSheet.Paste

Also, to find the last row, I would suggest this code
targetrow=Sheets(importwks).UsedRange.Rows.Count

Excel Prob
 

Thanks - works!

Nick Hodge wrote:
Doug

you need to add a qualifier to the range address or use a different
method,
(I have used A as an illustration

Range("A2").EntireRow.Cut _
Destination:=Sheets(importwks).Range("A" & targetrow)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS