1

I am banging my head against the wall trying to figure out why my copy paste is not lining up. I thought I had the index figured out correctly, but I am still missing something. It is correct for the first case of data but then is off by one one for the second and two for the third. It was initially two and four rows respectively, but I figured out that I need to update my counter regardless of which statement is true.

My code as is follows:

Dim FirstItem As Integer
Dim SecondItem As Integer
Dim Offsetcount As Integer
Dim Rowoffset As Integer
Dim true_offset As Integer
Dim myNum As Integer

Sheets("Sheet2").Activate
ActiveSheet.Range("C2").Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Rowoffset = 0
true_offset = 1
myNum = (Range("C" & Rows.Count).End(xlUp).Row)
'MsgBox myNum

Do While myNum > 1

    true_offset = true_offset + 1

    If FirstItem = SecondItem Then
            Offsetcount = Offsetcount + 1
            Rowoffset = Rowoffset + 1
            SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
    Else
            Set myactivecell = ActiveCell

            Set myActiveWorksheet = ActiveSheet 'Do I need to declare this and the line below?
            Set myActiveWorkbook = ActiveWorkbook
            ActiveSheet.Range(ActiveSheet.Cells(true_offset - Rowoffset, 1), ActiveSheet.Cells(true_offset + 1, 1)).EntireRow.Select
            Selection.Copy
            Set new_workbook = Workbooks.Add
            ActiveSheet.Paste
            myActiveWorkbook.Activate
            myActiveWorksheet.Activate
            myactivecell.Activate

            ActiveCell.Offset(Offsetcount + 1, 0).Select
            If ActiveCell.Value = "" Then
                myNum = 0
            End If

            FirstItem = ActiveCell.Value
            SecondItem = ActiveCell.Offset(1, 0).Value
            Offsetcount = 1
            myNum = myNum - 1
            Rowoffset = 0

    End If

Loop

Here's a screen cap of some sample data:

https://i.stack.imgur.com/EsA3B.png

vba_n00b
  • 53
  • 5
  • Could you give a little more real-life information about what you're trying to accomplish? It's obviously a little more complicated than replicating worksheets. – Graham Jun 20 '17 at 02:11
  • Sure, I have got a bunch of rows with different items for different flight numbers and I want to separate each flight out. How can I post sample data? – vba_n00b Jun 20 '17 at 03:23
  • you can post the data as image. By editing your post with an attachment – Karpak Jun 20 '17 at 04:24
  • Ok, it won't let me embed, but I uploaded an image with some sample data. – vba_n00b Jun 20 '17 at 15:43

1 Answers1

0

Ok I will tell you the quick-fix for your current code. Remove the +1 from this statement:

ActiveCell.Offset(Offsetcount + 1, 0).Select
'                            ^^^^

That said, your code really needs complete refactoring. You complain "banging my head against the wall" but that's the normal outcome when you dont follow the rules of good practice in programming.

  • Don't program by "mimicking" the GUI, drop the select/activething stuff
  • Dim all your variables with no exception, and use Option Explicit
  • You have too many variables in your code, many of them seem to track the same thing.

I hope this helps.

A.S.H
  • 29,101
  • 5
  • 23
  • 50
  • 1
    Thanks for the help. It did solve it. I am trying to use best practices. Apparently it has been too long since my Undergrad CS classes. I have been trying to strip down and streamline the code as I go. Right now, I just want to get something functional and fine tune it from there. I am not sure how to perform the functions without copy/paste. Let me know of something I could read to learn. – vba_n00b Jun 20 '17 at 19:20
  • @vba_n00b I am glad to know that the issue at hand is solved. To get rid of the Select/Activate/ActiveThing stuff, [This SO thread](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) is an excellent place to start. – A.S.H Jun 20 '17 at 19:24