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
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies