SOLVED

VBA help

%3CLINGO-SUB%20id%3D%22lingo-sub-2680601%22%20slang%3D%22en-US%22%3EVBA%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2680601%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20fairly%20simple%20macro%20that%20copies%20a%20form%20with%20data%20in%20it%20and%20pastes%20it%20to%20the%20next%20empty%20row.%20The%20issue%20I%20have%20is%20that%20it%20will%20only%20clear%20the%20original%20cells%20that%20I%20cleared%20data%20from.%20(Recorded%20Macro)%3C%2FP%3E%3CP%3EFor%20instance%20if%20I%20run%20the%20macro%20it%20copies%20cells%20A1%3AE5%20and%20then%20pastes%20those%20to%20A6%3AE10%2C%20then%20it%20clears%20the%20newly%20copied%20cells.%20If%20I%20run%20the%20macro%20a%20second%20time%20it%20pastes%20the%20data%20from%26nbsp%3BA1%3AE5%20to%20A11%3AE15%20and%20clears%20the%20data%20from%20cells%20A6%3AE10.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2680601%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2680622%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2680622%22%20slang%3D%22en-US%22%3ECould%20you%20post%20your%20macro%3F%20Sounds%20like%20an%20issue%20with%20using%20active%2Fselect%20in%20the%20macro%20recorder.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2680623%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2680623%22%20slang%3D%22en-US%22%3ESub%20New_PO()%3CBR%20%2F%3E'%3CBR%20%2F%3E'%20New_PO%20Macro%3CBR%20%2F%3E'%3CBR%20%2F%3E%3CBR%20%2F%3E'%3CBR%20%2F%3ERange(%22A4%3AP11%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3EActiveWindow.SmallScroll%20Down%3A%3D6%3CBR%20%2F%3ERange(%22A%22%20%26amp%3B%20Rows.Count).End(xlUp).Offset(1).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3ERange(%22D36%3AJ43%22).Select%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3ESelection.ClearContents%3CBR%20%2F%3ERange(%22L36%3AO43%22).Select%3CBR%20%2F%3ESelection.ClearContents%3CBR%20%2F%3ERange(%22D36%22).Select%3CBR%20%2F%3EEnd%20Sub%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2680777%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2680777%22%20slang%3D%22en-US%22%3EWhich%20cells%20are%20you%20trying%20to%20clear%3F%20The%20macro%20recorded%20is%20using%20hard%20coded%20references%20so%20it's%20not%20updating%20to%20factor%20in%20the%20extra%20pasted%20data.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2680824%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2680824%22%20slang%3D%22en-US%22%3EThe%20first%20time%20I%20run%20the%20macro%20it%20would%20be%20the%20two%20ranges%20that%20are%20in%20the%20macro.%20Every%20time%20after%20the%20first%20it%20needs%20to%20move%20down%20by%206%20rows.%3CBR%20%2F%3EWhat%20I%20have%20is%20a%20PO%20book%20that%20I%20need%20to%20easily%20be%20able%20to%20add%20the%20next%20section%20to.%20Each%20Section%20is%208%20rows%20by%2016%20columns.%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a fairly simple macro that copies a form with data in it and pastes it to the next empty row. The issue I have is that it will only clear the original cells that I cleared data from. (Recorded Macro)

For instance if I run the macro it copies cells A1:E5 and then pastes those to A6:E10, then it clears the newly copied cells. If I run the macro a second time it pastes the data from A1:E5 to A11:E15 and clears the data from cells A6:E10.

6 Replies
Could you post your macro? Sounds like an issue with using active/select in the macro recorder.
Sub New_PO()
'
' New_PO Macro
'

'
Range("A4:P11").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=6
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste
Range("D36:J43").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("L36:O43").Select
Selection.ClearContents
Range("D36").Select
End Sub
Which cells are you trying to clear? The macro recorded is using hard coded references so it's not updating to factor in the extra pasted data.
The first time I run the macro it would be the two ranges that are in the macro. Every time after the first it needs to move down by 6 rows.
What I have is a PO book that I need to easily be able to add the next section to. Each Section is 8 rows by 16 columns.
best response confirmed by Mark_J_W (Occasional Contributor)
Solution

@Mark_J_W 

See if this works (may have to adjust the " - 8" and " - 16" after the lastrows to fit your data set better)

Sub New_PO()

    Dim lastRow As Long
    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    
    With ws
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        .Range("A4:P11").Copy
        .Range("A" & lastRow).PasteSpecial Paste:=xlPasteValues
        .Range("D" & lastRow - 8 & ":J" & lastRow - 16).ClearContents
        .Range("L" & lastRow - 8 & ":O" & lastRow - 16).ClearContents
    End With
        
End Sub

 

Thank you I have it going now!