+ Reply to Thread
Results 1 to 3 of 3

bug in vba - move data from sheet1 to sheet 2

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    01-18-2016
    Location
    OBX NC
    MS-Off Ver
    Office 2013
    Posts
    153

    bug in vba - move data from sheet1 to sheet 2

    I am attempting to move data from sheet1 to sheet2 based on date. While it appears to be working, I get correct lines of out put, the rows are all scambled. Its ike they are randomly placed.

    Here is code I am using:

    Public Sub test()
    '*  I named cell containing the Base Date "BASE_DATE" to refer to it as named range
    Dim ws1     As Worksheet
    Dim ws2     As Worksheet
    Dim ws3 As Worksheet
    Dim ws4 As Worksheet
    Dim sheetname As String
    Dim xRow1   As Long
    Dim xRow2   As Long
    Dim lstRow1 As Long
    Dim lstRow2 As Long
    Dim bDate1   As Date
    Dim maxComm1 As Long
    Dim cell   As Range
    Dim rng1    As Range
    Dim rng2    As Range
    
    
    Dim counter As Long
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("test sheet")
    counter = 1
    ws1.Activate
    bDate1 = Int(ws1.Cells("76", "k").Value) ' normally I have time attached thus reason for INT
    lstRow1 = ws1.Range("A2:A71").End(xlDown).Row       '*  calculate the last filled row in column A
    For Each cell In ws1.Range("a2:a" & lstRow1)    '*  loop through each cell in filled range column A
        If ws1.Cells(cell.Row, "k").Value < Int(Range("BASE_DATE").Value) And ws1.Cells(cell.Row, "A").Value > 0 Then  '*  check if the a < base date and data in A value > 0
            'maxComm1 = 0                             '*  store 0 to the maxComm value
            For Each rng1 In ws1.Range("A" & cell.Row & ":A" & lstRow1) '*  loop though the comms column
                If ws1.Cells(rng1.Row, "A").Value = cell.Value And ws1.Cells(cell.Row, "k").Value <= bDate1 Then
      maxComm1 = ws1.Cells(cell.Row, "A").Value  '*  calculate the maxComm1 value
                    If ws1.Cells(cell.Row, "a").Value = maxComm1 Then xRow1 = rng1.Row               '*  if the comm value = the maxComm value then store that row number in xRow1
                End If
            Next rng1
            
            xRow2 = ws2.Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Row
            If counter = 1 Then ws2.Cells(xRow2, "c").Value = bDate1
            If maxComm1 > 0 Then '*  if the maxComm found is greater than 0 then populate Sheet2
            ws2.Activate
                  '*  find the first empty row in Sheet 2 in Column d
               
                ws2.Cells(xRow2, "d").Value = ws1.Cells(xRow1, "A").Value '*  stores the # in Sheet 2 column d row xRow 2 through I
    '            ws1.Activate
     '           ws1.Cells(xRow1, "A").Value = ""
     '           ws2.Activate
                ws2.Cells(xRow2, "e").Value = ws1.Cells(xRow1, "b").Value
    '            ws1.Activate
    '            ws1.Cells(xRow1, "B").Value = ""
    '            ws2.Activate
                ws2.Cells(xRow2, "f").Value = ws1.Cells(xRow1, "c").Value
    '            ws1.Activate
    '            ws1.Cells(xRow1, "c").Value = ""
     '           ws2.Activate
                ws2.Cells(xRow2, "g").Value = ws1.Cells(xRow1, "d").Value
    '            ws1.Activate
    '            ws1.Cells(xRow1, "d").Value = ""
     '           ws2.Activate
                ws2.Cells(xRow2, "h").Value = ws1.Cells(xRow1, "e").Value
     '           ws1.Activate
    '            ws1.Cells(xRow1, "E").Value = ""
                ws2.Cells(xRow2, "I").Value = ws1.Cells(xRow1, "f").Value
    '            ws1.Cells(xRow1, "f").Value = ""
                
            
            counter = counter + 1
            End If
             End If
            ' mmcomm1 = 0
    Next cell
    End Sub
    I have attached sheet with data from sheet 1, and example of sheet 2 and a CORRECTED sheet 2 of whats its supposed to look like, I also included a test page showing what my current results show.

    Any assistance would be a big help. More importantly tell me what the heck did I miss and why random rows.
    Attached Files Attached Files
    Last edited by jackf-nc; 08-07-2016 at 03:53 PM. Reason: Didnt know how to post code....Thx Mod for pointing it out!

  2. #2
    Valued Forum Contributor
    Join Date
    12-22-2015
    Location
    HK
    MS-Off Ver
    2010
    Posts
    532

    Re: bug in vba - move data from sheet1 to sheet 2

    Try this:

    Sub zz()
    Dim arr, Tmparr, bDate As Date, n&, Myr
    With Sheets(1)
    arr = .Range(.[a2], .[k1048576].End(xlUp))
    End With
    ReDim Tmparr(1 To UBound(arr), 1 To 6)
    bDate = [base_date]
    For i = 1 To UBound(arr)
        If arr(i, 1) > 0 And arr(i, 11) < bDate Then
            n = n + 1
            For j = 1 To 6
                Tmparr(n, j) = arr(i, j)
            Next
        End If
    Next
    With Sheets(2)
        Myr = .[d1048576].End(xlUp).Row + 1
        Range("c" & Myr) = bDate
        Range("d" & Myr).Resize(n, 6) = Tmparr
    End With
    End Sub

  3. #3
    Forum Contributor
    Join Date
    10-19-2012
    Location
    Omaha, Nebraska USA
    MS-Off Ver
    Excel 2010
    Posts
    249

    Re: bug in vba - move data from sheet1 to sheet 2

    This post had already been answered by the time I posted mine.
    Last edited by djbomaha; 08-07-2016 at 11:25 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. move data from one sheet1 to sheet 2 based on date
    By jackf-nc in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 07-09-2016, 01:46 PM
  2. move information in different tables in sheet1 to a calculation in sheet 2
    By Kim-Aleksander in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-11-2015, 05:03 AM
  3. move data from sheet1 to sheet2 based on date in column1 of sheet1
    By pcaldwell in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-10-2014, 03:20 PM
  4. Copy certain data from Sheet1 to Sheet 2 based on values in a column on Sheet1
    By theglitch in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-22-2013, 01:28 PM
  5. [SOLVED] Entering data in sheet1, having the data in sheet 2 returned to sheet1
    By Speedyjrr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-20-2012, 01:10 PM
  6. Move/copy data from sheet1 to list in sheet2?!
    By pallar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-24-2010, 02:49 PM
  7. Macro to move data from sheet1 to sheet2 when clicked
    By excel77 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-14-2009, 01:18 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1