Home

Macro copying cells from an invoice sheet into a report sheet stopped working properly...

%3CLINGO-SUB%20id%3D%22lingo-sub-526827%22%20slang%3D%22en-US%22%3EMacro%20copying%20cells%20from%20an%20invoice%20sheet%20into%20a%20report%20sheet%20stopped%20working%20properly...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-526827%22%20slang%3D%22en-US%22%3E%3CP%3Eafter%20I%20reformatted%20some%20cells%20in%20the%20invoice%20sheet%20and%20added%20another%20cell%20to%20the%20report%20sheet.%20I%20am%20using%20the%20following%20code%20which%20was%20working%20fine%20before%20this%20change.%20The%20new%20added%20cell%20code%20appears%20in%20bold%20below.%20It%20will%20no%20longer%20paste%20in%20the%20next%20empty%20row%2C%20it%20repeatedly%20pastes%20into%20row%201%20only%20now.%3C%2FP%3E%3CP%3E(of%20course%20the%20rookie%20foolishly%20did%20the%20changes%20without%20backing%20up%20the%20spreadsheet)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20CreateReport()%3CBR%20%2F%3ESheets(%22Invoice%22).Select%3CBR%20%2F%3ERange(%22A8%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESheets(%22Report%22).Select%3CBR%20%2F%3Eerow%20%3D%20Sheet3.Cells(Rows.Count%2C%201).End(xlUp).Offset(1%2C%200).Row%3CBR%20%2F%3ECells(erow%2C%201).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3E%3CBR%20%2F%3ESheets(%22Invoice%22).Select%3CBR%20%2F%3ERange(%22B8%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESheets(%22Report%22).Select%3CBR%20%2F%3Eerow%20%3D%20Sheet3.Cells(Rows.Count%2C%201).End(xlUp).Offset(1%2C%200).Row%3CBR%20%2F%3ECells(erow%2C%202).Offset(-1%2C%200).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3E%3CBR%20%2F%3ESheets(%22Invoice%22).Select%3CBR%20%2F%3ERange(%22A10%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESheets(%22Report%22).Select%3CBR%20%2F%3Eerow%20%3D%20Sheet3.Cells(Rows.Count%2C%201).End(xlUp).Offset(1%2C%200).Row%3CBR%20%2F%3ECells(erow%2C%203).Offset(-1%2C%200).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3E%3CBR%20%2F%3ESheets(%22Invoice%22).Select%3CBR%20%2F%3ERange(%22B10%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESheets(%22Report%22).Select%3CBR%20%2F%3Eerow%20%3D%20Sheet3.Cells(Rows.Count%2C%201).End(xlUp).Offset(1%2C%200).Row%3CBR%20%2F%3ECells(erow%2C%204).Offset(-1%2C%200).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3E%3CBR%20%2F%3ESheets(%22Invoice%22).Select%3CBR%20%2F%3ERange(%22B12%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESheets(%22Report%22).Select%3CBR%20%2F%3Eerow%20%3D%20Sheet3.Cells(Rows.Count%2C%201).End(xlUp).Offset(1%2C%200).Row%3CBR%20%2F%3ECells(erow%2C%205).Offset(-1%2C%200).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3E%3CBR%20%2F%3ESheets(%22Invoice%22).Select%3CBR%20%2F%3ERange(%22C12%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESheets(%22Report%22).Select%3CBR%20%2F%3Eerow%20%3D%20Sheet3.Cells(Rows.Count%2C%201).End(xlUp).Offset(1%2C%200).Row%3CBR%20%2F%3ECells(erow%2C%206).Offset(-1%2C%200).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%3CBR%20%2F%3E%3CBR%20%2F%3ESheets(%22Invoice%22).Select%3CBR%20%2F%3ERange(%22F26%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESheets(%22Report%22).Select%3CBR%20%2F%3Eerow%20%3D%20Sheet3.Cells(Rows.Count%2C%201).End(xlUp).Offset(1%2C%200).Row%3CBR%20%2F%3ECells(erow%2C%207).Offset(-1%2C%200).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3ESheets(%22Invoice%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22E43%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESelection.Copy%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESheets(%22Report%22).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Eerow%20%3D%20Sheet3.Cells(Rows.Count%2C%201).End(xlUp).Offset(1%2C%200).Row%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ECells(erow%2C%208).Offset(-1%2C%200).Select%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3ESheets(%22Invoice%22).Select%3CBR%20%2F%3ERange(%22F44%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESheets(%22Report%22).Select%3CBR%20%2F%3Eerow%20%3D%20Sheet3.Cells(Rows.Count%2C%201).End(xlUp).Offset(1%2C%200).Row%3CBR%20%2F%3ECells(erow%2C%209).Offset(-1%2C%200).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%3CBR%20%2F%3E%3CBR%20%2F%3ESheets(%22Invoice%22).Select%3CBR%20%2F%3ERange(%22F45%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESheets(%22Report%22).Select%3CBR%20%2F%3Eerow%20%3D%20Sheet3.Cells(Rows.Count%2C%201).End(xlUp).Offset(1%2C%200).Row%3CBR%20%2F%3ECells(erow%2C%2010).Offset(-1%2C%200).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-526827%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
BrianGee1957
Occasional Visitor

after I reformatted some cells in the invoice sheet and added another cell to the report sheet. I am using the following code which was working fine before this change. The new added cell code appears in bold below. It will no longer paste in the next empty row, it repeatedly pastes into row 1 only now.

(of course the rookie foolishly did the changes without backing up the spreadsheet)

 

Sub CreateReport()
Sheets("Invoice").Select
Range("A8").Select
Selection.Copy
Sheets("Report").Select
erow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
ActiveSheet.Paste

Sheets("Invoice").Select
Range("B8").Select
Selection.Copy
Sheets("Report").Select
erow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 2).Offset(-1, 0).Select
ActiveSheet.Paste

Sheets("Invoice").Select
Range("A10").Select
Selection.Copy
Sheets("Report").Select
erow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 3).Offset(-1, 0).Select
ActiveSheet.Paste

Sheets("Invoice").Select
Range("B10").Select
Selection.Copy
Sheets("Report").Select
erow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 4).Offset(-1, 0).Select
ActiveSheet.Paste

Sheets("Invoice").Select
Range("B12").Select
Selection.Copy
Sheets("Report").Select
erow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 5).Offset(-1, 0).Select
ActiveSheet.Paste

Sheets("Invoice").Select
Range("C12").Select
Selection.Copy
Sheets("Report").Select
erow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 6).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues

Sheets("Invoice").Select
Range("F26").Select
Selection.Copy
Sheets("Report").Select
erow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 7).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues

Sheets("Invoice").Select
Range("E43").Select
Selection.Copy
Sheets("Report").Select
erow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 8).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues

Sheets("Invoice").Select
Range("F44").Select
Selection.Copy
Sheets("Report").Select
erow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 9).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues

Sheets("Invoice").Select
Range("F45").Select
Selection.Copy
Sheets("Report").Select
erow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 10).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
End Sub