SOLVED

VBA Code to write cell values to another sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-3419341%22%20slang%3D%22en-US%22%3EVBA%20Code%20to%20write%20cell%20values%20to%20another%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3419341%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20below%20VBA%20code%20that%20takes%20cell%20values%20from%20rows%20B5%3AU5%2C%20and%20then%20writes%20a%20copy%20of%20that%20data%20to%20another%20set%20of%20columns%20to%20store%20a%20'history'%20(these%20figures%20then%20get%20updated%20from%20a%20query%20when%20new%20data%20arrives).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20now%20want%20to%20modify%20this%20so%20it%20writes%20the%20data%20to%20another%20sheet%20in%20the%20excel%20file%20to%20tidy%20things%20up%2C%20and%20then%20I%20can%20have%20a%20%22data%22%20worksheet%20I%20can%20then%20use.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EPublic%20Sub%20ButtonOnClick()%0A%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20Me.Range(%22W%22%20%26amp%3B%20Me.Rows.Count).End(xlUp).Offset(1).Resize(%2C%2020).Value%20%3D%20Me.Range(%22B5%3AU5%22).Value%0A%20%20%20%20Application.EnableEvents%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20would%20I%20modify%20this%20to%20no%20longer%20paste%20this%20data%20in%20W%3AAQ%2C%20but%20instead%20pastes%20it%20to%20AnotherWorksheet!%3A%20A%3AU%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20of%20the%20solutions%20I%20have%20tried%20to%20implement%20have%20given%20me%20errors%20so%20far.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3419341%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3419642%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Code%20to%20write%20cell%20values%20to%20another%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3419642%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1398189%22%20target%3D%22_blank%22%3E%40OJBridger%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EPublic%20Sub%20ButtonOnClick()%0A%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20With%20Worksheets(%22History%22)%0A%20%20%20%20%20%20%20%20.Range(%22A%22%20%26amp%3B%20.Rows.Count).End(xlUp).Offset(1).Resize(%2C%2020).Value%20%3D%20Me.Range(%22B5%3AU5%22).Value%0A%20%20%20%20End%20With%0A%20%20%20%20Application.EnableEvents%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi!

 

I have the below VBA code that takes cell values from rows B5:U5, and then writes a copy of that data to another set of columns to store a 'history' (these figures then get updated from a query when new data arrives).

 

I now want to modify this so it writes the data to another sheet in the excel file to tidy things up, and then I can have a "data" worksheet I can then use.

 

 

 

Public Sub ButtonOnClick()
    Application.EnableEvents = False
    Me.Range("W" & Me.Rows.Count).End(xlUp).Offset(1).Resize(, 20).Value = Me.Range("B5:U5").Value
    Application.EnableEvents = True
End Sub

 

 

 

How would I modify this to no longer paste this data in W:AQ, but instead pastes it to AnotherWorksheet!: A:U?

 

All of the solutions I have tried to implement have given me errors so far.

 

Many thanks in advance! 

1 Reply
best response confirmed by OJBridger (New Contributor)
Solution

@OJBridger 

Try

Public Sub ButtonOnClick()
    Application.EnableEvents = False
    With Worksheets("History")
        .Range("A" & .Rows.Count).End(xlUp).Offset(1).Resize(, 20).Value = Me.Range("B5:U5").Value
    End With
    Application.EnableEvents = True
End Sub