0

VBA noob here. I am trying to pull data from a table from sheet "Schedule" based on the value in column B, copying the values in column "C" and then pasting the results to the associated table on another sheet "Report1". The "Report1" has 5 Tables with headers (header rows can change) and I am trying to paste the results from "Schedule" to "Report1" in the specific tables. 

Sub CopyDataToReport()

Dim r As Integer
Dim s As Integer
Dim t As Integer
Dim u As Integer
Dim LRow As Long
Dim InfoSecCounter As Long
Dim HeaderRow_InfoSec As Long
Dim HeaderRow_BCDR As Long
Dim HeaderRow_PhysicalandDC As Long
Dim HeaderRow_SDLC As Long
Dim HeaderRow_ITOps As Long

Worksheets("Schedule").Activate
LRow = Worksheets("Schedule").range("B" & rows.Count).End(XLUP).Row

Worksheets("Report1").Activate
HeaderRow_InfoSec = Cells.Find(what:="InfoSec - 2019", _
                    After:=range("B1"), _
                    lookat:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
Worksheets("Schedule").Activate
For r = 2 To LRow
    If Worksheets("Schedule").range("B" & r).Value = "InfoSec" Then
        Worksheets("Schedule").range("C" & r).Copy
        Worksheets("Report1").Activate
        LRowReport = Worksheets("Report1").range("B" & r + HederRow_InfoSec).End(XLUP).Row
        Worksheets("Report1").range("B" & LRowReport + 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If
Next r

    'Populate BCDR Controls in BCDR Table
Worksheets("Report1").Activate
HeaderRow_BCDR = Cells.Find(what:="BCDR - 2019", _
                    After:=range("B1"), _
                    lookat:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
Worksheets("Schedule").Activate
For s = 2 To LRow
    If Worksheets("Schedule").range("B" & s).Value = "BCDR" Then
        Worksheets("Schedule").range("C" & s).Copy
        Worksheets("Report1").Activate
        LRowReport = Worksheets("Report1").range("B" & s + HeaderRow_BCDR).End(XLUP).Row
        Worksheets("Report1").range("B" & LRowReport + 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If
 Next s

    'Populate SDLC Controls in SDLC Table
Worksheets("Report1").Activate
HeaderRow_SDLC = Cells.Find(what:="SDLC - 2019", _
                    After:=range("G1"), _
                    lookat:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=True).Row
Worksheets("Schedule").Activate
For u = 2 To LRow
    If Worksheets("Schedule").range("B" & u).Value = "SDLC" Then
        Worksheets("Schedule").range("C" & u).Copy
        Worksheets("Report1").Activate
        LRowReport = Worksheets("Report1").range("G" & u + HeaderRow_SDLC).End(XLUP).Row
        Worksheets("Report1").range("G" & LRowReport + 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If
Next u
End Sub

Code is working fine for the first two tables, but as soon as move to the right, column "G" (where the 3rd table is) the code is pasting the values in column G but starting from row 37 when it should be row 4. Any Ideas why the "SDLC" code is pasting the rows in the wrong location?

Also, I am sure there is a more elegant why to write this code, but this is my first attempt.

Thank you!

BigBen
  • 46,229
  • 7
  • 24
  • 40
Roger
  • 1
  • 2
    One way to avoid looping would just be to use `Range.AutoFilter`. – BigBen May 19 '20 at 17:08
  • If you do continue with a loop make sure you read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba What is the value of `HeaderRow_SDLC` at that point? It is not looking just in column G in case you thought it was. – SJR May 19 '20 at 17:13
  • Instead of using `Cells` as the range object for the `Find` function, set a range object equal to the column you want to search in. As SJR pointed out, the `HeaderRow_SDLC` value obviously isn't being found in the place you expect it to be and therefore has the wrong value. You also might find this helpful: https://www.myonlinetraininghub.com/debugging-vba-code – Miqi180 May 19 '20 at 17:33

0 Answers0