insert row

[bob]

the following copies 2 source books to destination sheets and repeats
macro coping to next line in destination.
how would i copy the source headings A1 to F2 at A1 the first time and
insert a row at end of data copy loop. then on the next macro loop copy
headings after inserted row.
sub copy()
With newWks
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
End With

For i = 1 To LastRow
With TestOldWks
Set destCell = .Range("a" & _
.Cells(.Rows.count, "C").End(xlUp).Row
+1)
oldWks.Rows(i).Copy _
Destination:=destCell
End With
With TestNewWks
Set destCell = .Range("a" & _
.Cells(.Rows.count, "C").End(xlUp).Row
+1)
newWks.Rows(i).Copy _
Destination:=destCell
End With
Next i
End Sub

thanks

 

[Dave Peterson]

I'm having a difficult time understanding what you really want.

You may have 4 separate worksheets: oldwks, newwks, testnewwks, and testoldwks.

I'm not sure what the two source worksheets should be and what worksheet the
headers come from.

And it kind of looks like you may be alternating rows from two different
worksheets--but I'm kind of confused. But if it's true, depending on the data,
you may be able to copy each (all at once) and do a sort when you're done.

Post back with a little more clarification and I'm sure you'll get better help.

 

[bob]

i will try to make it a bit more clear.

source books are old.xls, new.xls, destination book is test.xls. the
headers in sheet 1&2 of test book are from the range a1 to f2 of source
books.

a row in sheet 1 of new book is copied to sheet2 of test book if a
condition is met and the same row in sheet 1 of old book is copied to sheet
1 of test book . i have an if statement to copy a row from the sources if a
number in col C >=100(difference between new book & old book).

i want to add the headings from new book and old book to the sheet 2 & sheet
1 of test book each time macro is run and put in a blank row each time after
data.

test book sheet2 looks like this with the rows where difference in col C
(between new book and old book) >=100 and just keeps coping to a new row
when macro is run again. (test sheet 1 just contains matching row lines
from old book)
2 0 150
7 3 100
..........

i want test sheet2 to look like this and test sheet 1 is the same format
with matching row lines from old book.
header
2 0 150
7 3 100

header
3 0 174
4 1 106
7 5 250

header
1 3 134
3 10 400

how would i add the headers and blank line to destination sheets each time
compare macro runs

thanks

 

[Dave Peterson]

I think this does what you want, but test it against copies of your
workbooks--just in case.

Option Explicit
Sub testme01()

Dim testNewWks As Worksheet
Dim testOldWks As Worksheet

Dim oldWks As Worksheet
Dim newWks As Worksheet

Dim testNewDest As Range
Dim testOldDest As Range

Dim oldHeaderRng As Range
Dim newHeaderRng As Range

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Dim oldVal As Variant
Dim newVal As Variant

Dim HeadersCopied As Boolean

Set testNewWks = Workbooks("test.xls").Worksheets("sheet1")
Set testOldWks = Workbooks("test.xls").Worksheets("sheet2")
Set oldWks = Workbooks("old.xls").Worksheets("sheet1")
Set newWks = Workbooks("new.xls").Worksheets("sheet1")

Set oldHeaderRng = oldWks.Range("a1:f2")
Set newHeaderRng = newWks.Range("a1:f2")

HeadersCopied = False
With newWks
FirstRow = 3 'avoid the headers
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = FirstRow To LastRow
newVal = .Cells(iRow, "C").Value
oldVal = oldWks.Cells(iRow, "C").Value
If IsNumeric(newVal) Then
If IsNumeric(oldVal) Then
'do the compare
If newVal - 100 >= oldVal Then
If HeadersCopied = False Then
'copy headers
HeadersCopied = True
With testNewWks
'skip a row and paste it in column A.
'.offset(2,-2) skips a row and
'goes back 2 columns to A
Set testNewDest _
= .Cells(.Rows.Count, "C") _
.End(xlUp).Offset(2, -2)
newHeaderRng.Copy _
Destination:=testNewDest
'get ready for the next copied cell
Set testNewDest _
= testNewDest.Offset _
(newHeaderRng.Rows.Count, 0)
End With
With testOldWks
'follows the same rules as above
Set testOldDest _
= .Cells(.Rows.Count, "C") _
.End(xlUp).Offset(2, -2)
oldHeaderRng.Copy _
Destination:=testOldDest
Set testOldDest _
= testOldDest.Offset _
(oldHeaderRng.Rows.Count, 0)
End With
End If
'copy the real data
.Rows(iRow).Copy _
Destination:=testNewDest
'and get ready for the next
Set testNewDest = testNewDest.Offset(1, 0)

oldWks.Rows(iRow).Copy _
Destination:=testOldDest
Set testOldDest = testOldDest.Offset(1, 0)
End If
End If
End If
Next iRow
End With
End Sub