Copying data from several hundred Excel workbooks to a master worksheet.
They are invoices that are not all in the same cell locations. The problem
is this: It copies the first several cells just fine. When I get to the
Tech1 name and Tech2 name it only copies the last 8 digits on the right.
The same code earlier copies the customer name and location just fine.

'---------------------------------------------------------------- Per Day
Row
For z = 11 To 30
strTemp1 = Cells(z, 1).Value
Cells(z, 1) = Right(strTemp1, 7)
If Right(strTemp1, 7) = "Per Day" Then
GoTo P_Day
End If
Next

P_Day:
P_Hour:

Set sourceRange = mybook.Worksheets(1).Cells(z, "B")
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "G").
_
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
'---------------------------------------------------------------- Total
Wages
z = z + 1
Set sourceRange = mybook.Worksheets(1).Cells(z, "B")
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "H").
_
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value

'---------------------------------------------------------------- Fee
z = z + 1
Set sourceRange = mybook.Worksheets(1).Cells(z, "B")
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "I").
_
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value

'---------------------------------------------------------------- Expenses
z = z + 1
Set sourceRange = mybook.Worksheets(1).Cells(z, "B")
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "J").
_
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value

'---------------------------------------------------------------- Total
Invoice Row Column
For z = 11 To 30
For x = 3 To 15
If Cells(z, x) = "INVOICE TOTAL" Then
GoTo T_Invoice
End If
Next
Next
T_Invoice:
x = x + 2
Set sourceRange = mybook.Worksheets(1).Cells(z, x)
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "K").
_
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
'---------------------------------------------------------------- Hands Row

For z = 5 To 15
If Cells(z, 1) = "HANDS" Then
GoTo Hands_R
End If
Next
Hands_R:
'---------------------------------------------------------------- Total
Column
z = z + 1
For x = 5 To 15
If Cells(z, x) = "Total" Then
GoTo T_1
End If
Next
'---------------------------------------------------------------- Tech1
T_1:
x = x + 1
z = z + 1
Set sourceRange = mybook.Worksheets(1).Cells(z, "A")
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "P").
_
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value ' Tech1

Set sourceRange = mybook.Worksheets(1).Cells(z, x)
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "Q").
_
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value ' T1
'---------------------------------------------------------------- Tech2
x = x + 1
z = z + 1
Set sourceRange = mybook.Worksheets(1).Cells(z, "A")
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "R").
_
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value ' Tech2

Set sourceRange = mybook.Worksheets(1).Cells(z, x)
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "S").
_
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value ' T2

This is the source Tech1 name
William Olsen

This is what I get in the destrange.
am Olsen

I found the trouble. I had several of these loops to locate the data I
needed. The line marked below <********* is not needed and removing it
fixed the problem.

'This code finds the Column for Invoice #.
For x = 1 To 12
strTemp1 = Cells(1, x).Value
Cells(1, x) = Left(strTemp1, 4) <********
If Left(strTemp1, 4) = "SITE" Then
GoTo Jump1
End If
Next
Jump1:


"Baine" wrote in message
...
Copying data from several hundred Excel workbooks to a master worksheet.
They are invoices that are not all in the same cell locations. The
problem is this: It copies the first several cells just fine. When I get
to the Tech1 name and Tech2 name it only copies the last 8 digits on the
right. The same code earlier copies the customer name and location just
fine.

'---------------------------------------------------------------- Per Day
Row
For z = 11 To 30
strTemp1 = Cells(z, 1).Value
Cells(z, 1) = Right(strTemp1, 7)
If Right(strTemp1, 7) = "Per Day" Then
GoTo P_Day
End If
Next

P_Day:
P_Hour:

Set sourceRange = mybook.Worksheets(1).Cells(z, "B")
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "G").
_
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
'---------------------------------------------------------------- Total
Wages
z = z + 1
Set sourceRange = mybook.Worksheets(1).Cells(z, "B")
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "H").
_
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value

'---------------------------------------------------------------- Fee
z = z + 1
Set sourceRange = mybook.Worksheets(1).Cells(z, "B")
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "I").
_
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value

'----------------------------------------------------------------
Expenses
z = z + 1
Set sourceRange = mybook.Worksheets(1).Cells(z, "B")
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "J").
_
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value

'---------------------------------------------------------------- Total
Invoice Row Column
For z = 11 To 30
For x = 3 To 15
If Cells(z, x) = "INVOICE TOTAL" Then
GoTo T_Invoice
End If
Next
Next
T_Invoice:
x = x + 2
Set sourceRange = mybook.Worksheets(1).Cells(z, x)
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "K").
_
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
'---------------------------------------------------------------- Hands
Row

For z = 5 To 15
If Cells(z, 1) = "HANDS" Then
GoTo Hands_R
End If
Next
Hands_R:
'---------------------------------------------------------------- Total
Column
z = z + 1
For x = 5 To 15
If Cells(z, x) = "Total" Then
GoTo T_1
End If
Next
'---------------------------------------------------------------- Tech1
T_1:
x = x + 1
z = z + 1
Set sourceRange = mybook.Worksheets(1).Cells(z, "A")
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "P").
_
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value ' Tech1

Set sourceRange = mybook.Worksheets(1).Cells(z, x)
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "Q").
_
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value ' T1
'---------------------------------------------------------------- Tech2
x = x + 1
z = z + 1
Set sourceRange = mybook.Worksheets(1).Cells(z, "A")
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "R").
_
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value ' Tech2

Set sourceRange = mybook.Worksheets(1).Cells(z, x)
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "S").
_
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value ' T2

This is the source Tech1 name
William Olsen

This is what I get in the destrange.
am Olsen