biffothebear
New Member
I have a slight problem my code if you could help me with it. The full code is below. 2 issues, the first is on the code not pasting the data in to correct row the dump sheet, it is ignoring the last row that contains data for the xl up and pasting the data over the top.
the second issue is that for Sheet FL the data is not pasting in the right rows for columns B & C. The xl up appears not to be looking for the last cell containing data, it is putting the data 29 rows the last cell containing data.
The strange thing is that this is working for columns F & G and I cannot see a difference why.
I have about 20 more sheets I have to do this code for which i can copy the code and change the name of the sheets.
Your valuable help will be very much appreciated.
thanks
I have found that in the rows that it is skipping the code is putting in a blank space, if that helps!
the results of my code are below: The bottom values (923.60 & 229.95) should line up to the descriptions but don't
the row item numbers for 11 & 12 should be inline with the bold descriptions which are the corresponding items but as you can see there are skipped rows due to my code putting in a blank space in those cells due to the formulas
96.7331/08/2018DOLEAS
106.7331/08/2018DOLEFL
NOAHFL
MR
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
11923.6031/08/2018
12229.954545531/08/2018
the second issue is that for Sheet FL the data is not pasting in the right rows for columns B & C. The xl up appears not to be looking for the last cell containing data, it is putting the data 29 rows the last cell containing data.
The strange thing is that this is working for columns F & G and I cannot see a difference why.
I have about 20 more sheets I have to do this code for which i can copy the code and change the name of the sheets.
Your valuable help will be very much appreciated.
thanks
>>> use CODE -tags <<<
Code:
Sub Macro2 Macro
'AS SHEET
Dim Lr As Long
With Sheets("AS")
Lr = .Range("I:I").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
.Range("I4:I" & Lr).Copy
Sheets("Dump").Range("B5000").End(xlUp).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
With Sheets("AS")
Lr = .Range("J:J").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
.Range("J4:J" & Lr).Copy
Sheets("Dump").Range("C5000").End(xlUp).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
With Sheets("AS")
Lr = .Range("M:M").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
.Range("M4:M" & Lr).Copy
Sheets("Dump").Range("F5000").End(xlUp).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
With Sheets("AS")
Lr = .Range("N:N").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
.Range("N4:N" & Lr).Copy
Sheets("Dump").Range("G5000").End(xlUp).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
'FL SHEET
With Sheets("FL")
Lr = .Range("I:I").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
.Range("I4:I" & Lr).Copy
Sheets("Dump").Range("B5000").End(xlUp).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
With Sheets("FL")
Lr = .Range("J:J").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
.Range("J4:J" & Lr).Copy
Sheets("Dump").Range("C5000").End(xlUp).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
With Sheets("FL")
Lr = .Range("M:M").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
.Range("M4:M" & Lr).Copy
Sheets("Dump").Range("F5000").End(xlUp).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
With Sheets("FL")
Lr = .Range("N:N").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
.Range("N4:N" & Lr).Copy
Sheets("Dump").Range("G5000").End(xlUp).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
End Sub
I have found that in the rows that it is skipping the code is putting in a blank space, if that helps!
the results of my code are below: The bottom values (923.60 & 229.95) should line up to the descriptions but don't
the row item numbers for 11 & 12 should be inline with the bold descriptions which are the corresponding items but as you can see there are skipped rows due to my code putting in a blank space in those cells due to the formulas
96.7331/08/2018DOLEAS
106.7331/08/2018DOLEFL
NOAHFL
MR
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
11923.6031/08/2018
12229.954545531/08/2018
Last edited by a moderator: