CODEWITHSUNDEEP
excelvba
Rookie
Rookie

Reputation: 1

Excel, VBA and moving rows

I am a rookie re: VBA and macros (creating or running). I need some assistance with code that will help me copy a row from one spreadsheet to another, leaving it in the original sheet and marking somehow (I think), or otherwise ensuring it's not copied again in the future. I found a few posts about moving rows, most relied on a date entry, but this does not work for my purpose. My sheet has a column (H) with drop down data validation - any entry into the cell from the drop down will identify the row as one to copy to the next sheet. However, there is also a column that follows (J), where data entry is optional, and I don't want the row copied before giving the user the opportunity to enter into that column, so that it's contents would copy also (or maybe that's a set of separate code?). Rows copied to new spreadsheet should be inserted into next available row. thank you for any assistance & instructions you can offer.

Upvotes: 0

Views: 538

Answers (1)

BruceWayne
BruceWayne

Reputation: 23285

Okay - with the info. I have gathered from your posts, I have the following:

Sub copyRows()
Dim xRow As Integer, xCol As Integer, lastCol As Integer
Dim dataValidation As Range, rowToCopy As Integer, copyRange As Range, destRange As Range
Dim copyWSRow As Integer, jColValue As String
Dim origWS As Worksheet, copyWS As Worksheet

Set origWS = ActiveSheet

' Edit these two lines as needed.  If you have the "copy" worksheet already, comment out the first line
' and then change the 'Sheets("Copy")' to 'Sheets("_____")'
Worksheets.Add(after:=Worksheets(1)).Name = "Copy"
Set copyWS = Sheets("Copy")

origWS.Activate

Set dataValidation = origWS.Cells(1, 8) ' Cell H1 has your data validation.  Change this as necessary
rowToCopy = dataValidation.Value

'find the last column used, that isn't Col. H.  If it's column H, assume G is the last column
lastCol = origWS.Cells(1, 1).End(xlToRight).Column
If lastCol = 8 Then lastCol = lastCol - 1

With origWS
Set copyRange = .Range(.Cells(rowToCopy, 1), .Cells(rowToCopy, lastCol))
End With

'What's the next available row in the Copy WS?
If copyWS.UsedRange.Rows.Count = 1 And copyWS.Cells(1, 1).Value = "" Then
    copyWSRow = 1
Else
    copyWSRow = copyWS.UsedRange.Rows.Count + 1 ' count the used rows, and add one to make the next blank row the row to copy to
End If
'Set the destination Range

With copyWS
Set destRange = .Range(.Cells(copyWSRow, 1), .Cells(copyWSRow, lastCol))
End With

'Now, just copy the info over (technically, just set values equal)
destRange.Value = copyRange.Value

'Now, check to see if Column J has any info - if so, add to the line we just did in the Copy WS
If origWS.Cells(1, 10).Value <> "" Then
    jColValue = origWS.Cells(1, 10).Value
    copyWS.Cells(copyWSRow, 10).Value = jColValue
End If



 ' Add note that the row was copied
    origWS.Cells(rowToCopy,13).Value = "Already Copied"

    End Sub

A few notes: I have assumed that the right most column, with data you want to copy, will be Column G - since H has the row value.

Also, I have H1 as the cell that has your data validation - you might need to edit this as necessary.

I tested this with some data, in column A through G, with H1 and J1 being the data validation (where it outputs a number) and J1 as the "extra" information. If you want the 'extra info' to be on EACH row of the data, and not in one absolute spot, just change the jColValue formula to jColValue = origWS.Cells(rowToCopy,10).Value.

And of course, if this doesn't work or needs tweaking, just let me know.

Upvotes: 1

Related Questions