Copy and Paste Macro?

[OpenIntro]

Let me explain what I am trying to do.

I have an Excel Spreadsheet that is being used to input a lot of data. Quite a bit of the information is the same each time I start a new row of data. I am looking for a way to automate this, so that certain columns already have the "same" information filled in upon the click of a button. Specifically, each time I click the button, I would like a certain 4 rows of data to be pasted in to the next empty row. So if I have data through row 4500, I want the rows to appear from 4501-4504.

The problem I am running into is how do I set up a macro that will paste into the next EMPTY row? I assume using some kind of count function, but not totally sure. Any ideas?

 

[OpenIntro]

The Aqua colored cells represent the copyRange, and the Brown colored cell represents to PasteToCell -- then the following code should do what you are intending to do ...

Sub yCopyRangeAndPasteToFirstEmptyRowinColumnA()
    Set PasteTocell = [a1].End(xlDown)(2)
    Range("copyRange").Copy PasteTocell
End Sub

I have defined the CopyRange and PasteToCell, and have assigned the above code to the button macro....but it does nothing. Any ideas?

 

[kskinne]

the 'PasteTocell' range is defined within the macro, and you do not manually define it as you do with the 'copyRange' range. I tested this macro and it works just as Yogi Anand said. Are you sure that you pasted this code into a new module? Also, instead of using your button to run this macro, try running it manually by going to the tools menu, macro, macros, selecting the macro from the list and pressing the 'run' button. If this doesn't work, post back.

kevin

 

[OpenIntro]

Ok, I don't have PasteToCell defined as anything, but I do have copyRange defined. I made sure it is in a new module, and I ran it from the Macro menu, but still, nothing is happening. Could you perhaps email me the file that you tested and let me test it on here? Maybe I will see something I didn't do!

 

[Yogi Anand]

Hi Openintro:

If Kevin has emailed you a spreadsheet showing how it works -- then you are all set -- otherwise send me A PM and I will email you a spreadsheet with the sample I have posted on the Bord.

 

[OpenIntro]

PM Sent....

 

[Yogi Anand]

Hi Eric:

I have emailed you the spreadsheet. Please look at that and post back if it works for you now -- otherwise explain a little further and let us take it from there. Good Luck!

 

[OpenIntro]

BINGO! After opening your file and going through it in a little more detail, the reason why I wasn't seeing anything was that there were BLANK SPACES early on in Column A. I didn't think there were....but column B contains NO blank spaces....

So upon changing: Set PasteTocell = [a1].End(xlDown)(2) to Set PasteTocell = [b1].End(xlDown)(2), it worked fine! Also, this line: Debug.Print PasteToCell.Address was not originally included in the code before....the final macro code is below. Thanks a lot for all of your help!!!!

Sub yCopyRangeAndPasteToFirstEmptyRowinColumnA() 
    Set PasteTocell = [a1].End(xlDown)(2) 
    Debug.Print PasteToCell.Address
    Range("copyRange").Copy PasteTocell 
End Sub

 

[Yogi Anand]

... Also, this line: Debug.Print PasteToCell.Address was not originally included in the code before....the final macro code is below. Thanks a lot for all of your help!!!!

Sub yCopyRangeAndPasteToFirstEmptyRowinColumnA() 
    Set PasteTocell = [a1].End(xlDown)(2) 
    Debug.Print PasteToCell.Address
    Range("copyRange").Copy PasteTocell 
End Sub

Hi Eric:

Debug.Print PasteToCell.Address -- is as you can see for debugging only (meaning making some intermediary checks) and is not needed for successful execution of the macro. So the macro will work fine (within the stipulated constraints) without that line included as in ...

Sub yCopyRangeAndPasteToFirstEmptyRowinColumnA() 
    Set PasteTocell = [a1].End(xlDown)(2) 
    Range("copyRange").Copy PasteTocell 
End Sub

I hope this helps.