Pasting a column to the next blank column in a different sheet

    Hi, I am new to using macros in excel. This is my first posting in here too, forgive me if it's a bit hard to follow.


    I am making up a workbook for my soccer team to record and tally players player points throughout the season. In the workbook each game has its own sheet that calculates the total points each player received for that game. This total is recorded in J33:J52. What i would like is to have a macro that copies J33:J52 of the active sheet, then pastes it into the next blank column of a summary sheet.


    So after i've entered all the points for round 1, i would click on the macro button and it would paste J33:J52 of round 1 into A1:21 say, of the summary sheet. Then after i've added in the points for round 2 in the round 2 sheet, it would automatically see that the next blank column in the summary sheet is B1:B21 and paste J33:J52 of the round2 sheet there and so on for each round.


    Hope this makes sense.. In essence i just don't know the code for selecting a range that is the next blank column in a sheet.


    Thanks heaps!


    Amy

    Re: Pasting a column to the next blank column in a different sheet


    Try this out:


    Code
    Sub test()
Dim NextCol As Long
    If Sheets("Summary Sheet").Range("A1").Value = "" Then
        NextCol = 1
    Else
        NextCol = Sheets("Summary Sheet").Cells(1, Columns.Count).End(xlToLeft).Column + 1
    End If
        ActiveSheet.Range("J33:J52").Copy Sheets("Summary Sheet").Cells(1, NextCol)
End Sub

    Re: Pasting a column to the next blank column in a different sheet


    Hi Amy,


    I am not VBA expert, but if your problem is not solved by now, provide the sample excel file, I think the same can be achieved without VBA as well.

    Re: Pasting a column to the next blank column in a different sheet


    Hi Singh r


    A simple lookup would of been sufficient if the workbook was set up correctly. However we can't make the assumption that the problem is not solved just because there is no answer. Most posters on forums in my opinion either forget to say thankyou or respond in some way. The guy asked from some vb and that is what he got. There was not enough detail for anything else. One that. I would suggest the same solution but a little less words. Good in XL2003 and all smaller XL 07 files. It does not cater for dropping data in the first column of the Summary sheet but works nicely after this point.


    Take care


    Smallman


    Code
    Sub PastetoLast()
  ActiveSheet.Range("J33:J52").Copy Sheets("Summary").Range("IV1").End(xlToLeft).Offset(, 1)
End Sub

    Re: Pasting a column to the next blank column in a different sheet


    Thanks guys!


    Sorry it was late at night Aus time when i posted this and only just got back on.


    The reason i asked for VB code was because i don't want to have to create all the sheets for each game in advance. Just gets a bit messy when you have washed out games etc. I've had it set up this way in the past. I thought this would make things a bit more elegant (plus having recently discovered VB.. i like having little plays with things!).


    Jproffer -



    Thank you. I tried your code and


    1. I've realised that to excel the cells i'm trying to copy over contain formulas (to calculate the total points the player has received). So when it pastes, quite rightly it pastes the formula, where as i would just like it to paste the value.


    2. it works for pasting round 1 to the summary sheet, but when i try and run the same macro for the round 2 sheet it gives me "Run-time error '13': Type mismatch"


    Smallman -

    Thank you this seems to work as i'd like it, i'm just having the same trouble with it pasting across the formula in the cell where as i realise i would just like it to past the value. Can this be rectified by adding in a paste special line to the code or something? I've played around recording a macro for paste special then look up the code, so may be able to nut this one out myself. Any help's always appreciated though ;)


    Cheers guys!


    Amy

    Re: Pasting a column to the next blank column in a different sheet


    Change Smallmans Macro to;


    Code
    Sub PastetoLast()


    ActiveSheet.Range("J33:J52").Copy
    Sheets("Summary").Range("IV1").End(xlToLeft).Offset(, 1).PasteSpecial xlValues
    Application.CutCopyMode = False
    
End Sub

    Re: Pasting a column to the next blank column in a different sheet


    Thanks that worked great!


    Could you tell me aswell how i could do the same thing, pasting in as a link to the original cells? I'm thinking i might like to do it this way incase the original data needs to be updated for any reason.


    Cheers,


    Amy

    Re: Pasting a column to the next blank column in a different sheet


    Also this might be a little confusing for me to try and explain..


    But is there a way that i can name the active sheet in my macro so i can refer to it later, even after it stops becoming the active sheet?


    I would like to do this so i don't have to do two seperate macros to record points and goals. Obviously when i initially tried combining the two, the active sheet had become the summary sheet where things were getting pasted before it would copy over the goals as well. I'd like to refer to the active sheet and not hard code in the name of the sheet so the macro still works for future games on different sheets without me having to get in and change the coding.


    Cheers,


    Amy

    Re: Pasting a column to the next blank column in a different sheet


    Try

    Code
    Sub PastetoLast()


    ActiveSheet.Range("J33:J52").Copy
    Application.Goto Sheets("Summary").Range("IV1").End(xlToLeft).Offset(, 1)
    ActiveSheet.Paste Link:=True
    Application.CutCopyMode = False
    
End Sub

    Re: Pasting a column to the next blank column in a different sheet


    Quote

    But is there a way that i can name the active sheet in my macro so i can refer to it later, even after it stops becoming the active sheet?

    See: Sheet/Worksheet CodeNames

    Re: Pasting a column to the next blank column in a different sheet


    Thanks this is similar to something i tried before.


    Previously it seemed to be working fine, then i have no idea what i changed (i'm amost adamant i changed nothing) it stopped working and would give me the following error:


    Run-time error '1004':
    Microsoft Office Excel cannot paste the data.


    I get this message when i try to run your script as well. Any thoughts?


    Cheers,


    Amy

    Re: Pasting a column to the next blank column in a different sheet


    Thanks i've had a bit of a read through this. My understanding is this guides you on a robust way to refer to a sheet, that keeps working even if you change the tab name.


    in short...


    what i would like is for the macro to record a name for the sheet it is currently on, so i can keep referring to this sheet even when the active sheet changes, and for this same macro to work across any number of sheets, each time noting the sheet it is currently on.


    in long..


    With what i'm making up i have a sheet for round 1, in this sheet i have a button i click that records the player points for round 1 onto the summary sheet. currently the sheet is referred to as 'activesheet' in the code.


    i don't have any other sheets created for future games. when it comes time for round 2 i just copy the round 1 sheet and rename the tab round 2. because the macro has 'activesheet' coded into it, the button works correctly for this sheet and by clicking it in the round 2 sheet the round 2 info is copied into the summary sheet.


    my understanding is that in the macro, after it has copied the player points across to the summary sheet, the summary sheet becomes the 'active sheet', so if i would like to copy anything else from the round sheet over, i can't refer to the round sheet as 'activeshet'. but if i hard code in 'round 1' as the sheet name, then i can't just keep copying the sheet for use in future rounds without also having to get in and change the sheet name in the macro for it to stay current.


    to get around this i have created 2 buttons, one you click to record player points to the summary sheet, the other you click to record player goals to the summary sheet. i thought there might be a way around this though, by say, at the start of the macro doing something like naming the active sheet so it has a solid reference i can call on later (even when the active sheet has changed) but still gives me the flexibility to just keep copying a single sheet for future rounds.


    for eg. can you put 'round = activesheet' at the start of the code or something


    hope that makes sense!

    Re: Pasting a column to the next blank column in a different sheet


    Hi Amyz09


    Would you mind doing yourself and everyone a favour and posting your workbook. I would love to see your progress and how this model is set out. That would allow for a reasonably swift resolution for your problems. PS I am in Oz too!!!


    Take care


    Smallman

    Re: Pasting a column to the next blank column in a different sheet


    Hii Smallman


    Nothing against VB buddy, just there was no answer so I though if I could help someway. And I am really thankful to this forum & all you MVPs. Earlier someone like you helped me with their VB knowledge & still I am using those codes & it saves me a lot of time.

    Re: Pasting a column to the next blank column in a different sheet


    Hi Singhr


    No worries. I am sure you will get so many chances to repay the kindness of strangers. I am pleased that you find value in this forum, multitudes do. Great place to learn as well. Keep reading and pushing yourself and your knowledge will improve beyond your expectations. :)


    Take Care


    Smallman

    Re: Pasting a column to the next blank column in a different sheet


    Thread closed because question asked in a new thread.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login