Button to populate cells

[JON_ROCKS]

Hi guru's

I need to be able to create a button that when i click it it populates cells with generic data from the blank rota sheet.

Ideally i want it to only generate each block of cells and have a button for each group of bordered cells.

Is this possible and as each week another tab is added to the bottom would it be possible to make the button update the specific sheet im working on at the time?

attached is an example copy of the excel document so far.

I look forward to hearing from you all :D

[vba_php]

Jon,

per you: I need to be able to create a button that when i click it it populates cells with generic data from the blank rota sheet.

that's very vague. you really should give specifics. you mean you want a button to produce a new sheet that looks exactly like the sheets ""0908"" and ""1608"", every month as a new worksheet?

per you: would it be possible to make the button update the specific sheet im working on at the time

of course. but it's still a little vague.

[JON_ROCKS]

I thought it was quite straight forward but i guess i can elaborate a little more?

I want to copy from for example the Blank rota tab b18:h19 and past it in the current worksheet at the click of a button on the active sheet using vba, as the tabs go up each week i want to be able to copy generic data from blank rota to the newest tab i selected.

is this possible?

[JON_ROCKS]

Ok so this code works if i want it copying to the specific tab 2308 but i want it work on every tab automatically without having to change it each time.


Sub copy_range()

Dim FirstSheet As Worksheet
Set FirstSheet = ActiveWorkbook.Worksheets("BLANK ROTA")
Dim SecondSheet As Worksheet
Set SecondSheet = ActiveWorkbook.Worksheets("2308")

FirstSheet.Range("B18:H19 ").Copy
SecondSheet.Range("B18:H19").PasteSpecial Paste:=xlValues 'Only paste values

End Sub

[vba_php]

I thought it was quite straight forward but i guess i can elaborate a little more?

I want to copy from for example the Blank rota tab b18:h19 and past it in the current worksheet at the click of a button on the active sheet using vba, as the tabs go up each week i want to be able to copy generic data from blank rota to the newest tab i selected.

is this possible?

of course. your file has plenty of modules and code already in it. did you write all of that? all you need to do here is use the RANGE() object in code. select the range, copy it and select the cell on the worksheets of choice and paste in it in your desired place. when you insert a NEW tab, regardless of where it appears on the bottom of the screen in the list of worksheets, it automatically becomes the upper bound index number of the worksheets collection, I believe. thus, to point to it, this code should work:

Please Login or Register  to view this content.

keep in mind, VBA is not the greatest language in the world, certainly not like Python, so some interchangeable code (e.g. - statements that can be written more than one way) sometimes don't work the same. you can thank microsoft for that. (sigh)

[vba_php]

looks like you already worked it out. so, you can use what I just said regarding bounds to accomplish your goal

[JON_ROCKS]

of course. your file has plenty of modules and code already in it. did you write all of that? all you need to do here is use the RANGE() object in code. select the range, copy it and select the cell on the worksheets of choice and paste in it in your desired place. when you insert a NEW tab, regardless of where it appears on the bottom of the screen in the list of worksheets, it automatically becomes the upper bound index number of the worksheets collection, I believe. thus, to point to it, this code should work:

Please Login or Register  to view this content.

keep in mind, VBA is not the greatest language in the world, certainly not like Python, so some interchangeable code (e.g. - statements that can be written more than one way) sometimes don't work the same. you can thank microsoft for that. (sigh)

Hi,

Alot of them but not all , most were adapted from things ive found in google,

I am fairly new to this so just forming a basic understanding,


How would i impliment your suggestion into the code i posted above to make it work please?

[vba_php]

try this Jon.

Please Login or Register  to view this content.

should work.

[JON_ROCKS]

i get run time error 9 subscript out of range error?

[vba_php]

no kidding? hmmmmm....i wonder if I forgot a line!:

Please Login or Register  to view this content.

that work?

[JON_ROCKS]

no luck mate i now get error 424 object required

[vba_php]

well....maybe it's a little too early. I've checked numerous ways on my end and it works fine. can you upload the file with the new code? and, I'm not a professional excel developer so there's possibly something I'm missing? I don't know.

[JON_ROCKS]

ive just pasted it into the attached spreadsheet in the first post into a new module

[vba_php]

Jon,

I looked at every module and I don't see the routine name I posted for you anywhere in there. are you sure you included it? I don't see anything similar to what I did either. where is the code at? what module? name of it please?

[JON_ROCKS]

hi i havent re uploaded it since doing it mate but i am using a copy of that document

[vba_php]

hi i havent re uploaded it since doing it mate but i am using a copy of that document

and that means? that means it should be there in the original in your first post? if so, I already told you I looked at it and my example is not in there. sorry....does it have a different name?

[JON_ROCKS]

ok added this check module 31

[vba_php]

you had the new worksheet named wrong you goofy guy! LOL. plus, the macro recorder spits out more than just this argument in the pasteSpecial method (which is what you had. not sure if you need all the rest of the args that excel made for me):

Please Login or Register  to view this content.

the entire things that works here is:

Please Login or Register  to view this content.

[JON_ROCKS]

DOH!! yep working perfect now thanks for your help!

[vba_php]

Well it don't only took 50 million posts John. Glad to hear that it worked! Good luck to you on your project. And sorry about the spelling of your name artificial intelligence on phones is really terrible! LOL

[JON_ROCKS]

SOOO.... one last question, can a click to confirm popup box be added to this code so it doesnt accidentally over write all the work?

[vba_php]

SOOO.... one last question, can a click to confirm popup box be added to this code so it doesnt accidentally over write all the work?

of course it can. all visual basic languages and its extensions (VBA, VB 2.0 - 6.0, VBS, VB.NET) have the message box object inside of them. so, for this particular flavor, it is simple:

Please Login or Register  to view this content.