Always one row won't complete

[rogerbowering]

Hi

I have a dataset from a relational database that I ma using Excel VBA to format from one worksheet to another

I take the raw export data and depending on whether it's header or child record data I format it a certain way in the worksheet

I've two worksheets 'Export' where the Database dataset is exported to and 'Report' where I'm copying formatted data into using VBA

So....I loop through and reformat the cells

As I go through the 'child' data the cells always copy over EXCEPT the last record!!! And I can't figure why. My VBA is crude but every 'child' record gets copied from one sheet to the other using:

Please Login or Register  to view this content.

When I'm debugging I can watch the last child record being processed and the data is going into the variables ok but it isn't going in to the cells in the 'Report' sheet. It's always the last child record, so I have a beautifully formatted Report missing one child record in every record set

Grrrrrrrrrrrrrrrrrrr

[Mordred]

Hi roger,

Firstly, Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Secondly, Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

You will get help really quick if you do all this.

[rogerbowering]

Sub ZurichDevLog()
Dim CaseID As Integer, Priority As Integer
Dim Topic As String, CaseStatus As String, SubTopic As String, Description As String
Dim Updater As String, notes As String, HistStatus As String, ActionWith As String
Dim DateRaised As Date, ModifiedDate As Date
Dim HistDate As Date, CompletionDate As Date
Dim MainHead As String, SubHead As String

'Import
Workbooks.Open Filename:= _
"C:\Users\roger.bowering\Documents\Dropbox\Work\MYI\Zurich Development\Excel Export.xlsx"
With ActiveWindow
.Top = 38.5
.Left = 202
End With
Windows("Zurich Development Log.xlsm").Activate
Sheets("Export").Select
Cells.Select
Selection.ClearContents
Windows("Excel Export.xlsx").Activate
Cells.Select
Selection.Copy
Windows("Zurich Development Log.xlsm").Activate
ActiveSheet.Paste

'Rearrange Columns
Sheets("Export").Select
Columns("B:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("F:F").Select
Selection.Cut
Columns("B:B").Select
ActiveSheet.Paste
Columns("H:H").Select
Selection.Cut
Columns("C:C").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=2
Columns("K:K").Select
Selection.Cut
ActiveWindow.LargeScroll ToRight:=-1
Columns("D:D").Select
ActiveSheet.Paste
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("H:H").Select
Selection.Cut
Columns("E:E").Select
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=1
Columns("N:N").Select
Selection.Cut
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.LargeScroll ToRight:=-1
Columns("G:G").Select
ActiveWindow.SmallScroll ToRight:=5
Columns("M:M").Select
Selection.Cut
Columns("G:G").Select
ActiveSheet.Paste
Columns("H:M").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=-3
Columns("G:G").Select
Selection.Cut
Columns("E:E").Select
ActiveSheet.Paste
Columns("H:H").Select
Selection.Cut
Columns("G:G").Select
ActiveSheet.Paste
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=4
Columns("K:K").Select
Selection.Delete Shift:=xlToLeft

'Clear Main Sheet
Sheets("Report").Select
Cells.Select
Selection.ClearContents
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With

'Get and format Data
hdr = 0: Row = 2: Column = 1: RptRow = 1
Sheets("Export").Select
Varble = Cells(Row, 9)
While Cells(Row, 8) > ""
If Cells(Row, 1) > 0 Then
hdr = 1
CaseID = Cells(Row, 1)
Priority = Cells(Row, 6)
Topic = Cells(Row, 2)
CaseStatus = Cells(Row, 5)
SubTopic = Cells(Row, 3)
Description = Cells(Row, 4)
ModifiedDate = Cells(Row, 7)
HistDate = Cells(Row, 8)
Updater = Cells(Row, 9)
notes = Cells(Row, 10)
HistStatus = Cells(Row, 11)
ActionWith = Cells(Row, 12)
CompletionDate = Cells(Row, 13)
Sheets("Headings").Select
Range("A1:H1").Select
Selection.Copy
Sheets("Report").Select
RangeCalc = "A" & RTrim(RptRow)
'Range("RangeCalc").Select
Cells(RptRow, 1).Select
ActiveSheet.Paste
RptRow = RptRow + 1
Cells(RptRow, 1) = CaseID
Cells(RptRow, 6) = Priority
Cells(RptRow, 2) = Topic
Cells(RptRow, 5) = CaseStatus
Cells(RptRow, 3) = SubTopic
Cells(RptRow, 4) = Description
Cells(RptRow, 7) = ModifiedDate
RptRow = RptRow + 1
Sheets("Headings").Select
Range("A2:H2").Select
Selection.Copy
Sheets("Report").Select
RangeCalc = "A" & RTrim(RptRow)
Cells(RptRow, 1).Select
ActiveSheet.Paste
RptRow = RptRow + 1
Cells(RptRow, 2) = HistDate
Cells(RptRow, 3) = Updater
Cells(RptRow, 4) = notes
Cells(RptRow, 5) = HistStatus
Cells(RptRow, 6) = ActionWith
Cells(RptRow, 7) = CompletionDate

Else
Sheets("Export").Select
HistDate = Cells(Row, 8)
Updater = Cells(Row, 9)
notes = Cells(Row, 10)
HistStatus = Cells(Row, 11)
ActionWith = Cells(Row, 12)
CompletionDate = Cells(Row, 13)
'RptRow = RptRow + 1
'Sheets("Headings").Select
'Range("A2:H2").Select
'Selection.Copy
'Sheets("Report").Select
'RangeCalc = "A" & RTrim(RptRow)
'Cells(RptRow, 1).Select
'ActiveSheet.Paste
Sheets("Report").Select
RptRow = RptRow + 1
Cells(RptRow, 2) = HistDate
Cells(RptRow, 3) = Updater
Cells(RptRow, 4) = notes
Cells(RptRow, 5) = HistStatus
Cells(RptRow, 6) = ActionWith
Cells(RptRow, 7) = CompletionDate
End If
Row = Row + 1
Sheets("Export").Select
Wend


'Format Document
Sheets("Report").Select
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("K2").Select
ActiveWindow.SmallScroll Down:=-6
Columns("G:G").Select
Selection.NumberFormat = "m/d/yyyy"
ActiveWindow.SmallScroll Down:=-24
Columns("H:H").ColumnWidth = 23
Range("H1").Select
ActiveCell.FormulaR1C1 = "Comments"
Range("H2").Select
Columns("H:H").ColumnWidth = 29.43




End Sub

[rogerbowering]

Sorry, how I put code tags around code?

I'm using a VBA editor in Excel and the code seems just like text?

[Mordred]

I provided you with the "how to" in my last post. While you do that, I'll look at your workbook.

[FDibbins]

roger, to use code tags, see my notes below