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

 

 

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies