Placing code into a command Button 1

[scottie1]

Hi All

I was give some code from Tony Jollans and it works perfect but when I try to put the code into a Command Button I've drawn on to the spreadsheet it doesn't work properly. The original question ws how to open spreadsheets and place a value in column A:A to whatever value I have on my template spreadsheet and then save the document that I've just opened as A1 and close it again. Here is the code.

For Each c In Range("A2", Range("A65535").End(xlUp))
Workbooks.Open c
With ActiveWorkbook.ActiveSheet.Cells(1, 1)
.Formula = c.Offset(, 1)
.AutoFill Range("A2", Range("B65535").End(xlUp).Offset(0, -1))
End With
ActiveWorkbook.Close True
Next

The error message I get is run time error 1004, Autofill method of range class fails.

It has to be a sort of an autofill as each spreadsheet has different amounts of rows.

 

[SkipVought]

scottie,

The FIRST statements in your command button click event needs are

Worksheet("YourSheetName").Activate
[A1].Select
'the remainder of your code....

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[scottie1]

Hi there SkipVought

I still not sure what you mean its giving me a compile error could you spell it out for me

 

[SkipVought]

When you hit the command button it is selected, not any worksheet element.

Exactly what is your button code? Please post.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[scottie1]

Hi Skip

Here is my exact code and name of spreadsheet that i want the command button in.
Private Sub CommandButton1_Click()
'
' Macro1 Macro
' Macro recorded 06/04/04 by Adam Mitchell
'
For Each c In Range("A1", Range("A65535").End(xlUp))
Workbooks.Open c
With ActiveWorkbook.ActiveSheet.Cells(1, 1)
.Formula = c.Offset(, 1)
.AutoFill Range("A1", Range("B65535").End(xlUp).Offset(0, -1))
End With
ActiveWorkbook.Close True
Next

End Sub


Name of spreadsheet is Dog

 

[SkipVought]

Sorry I did not look closely at your procedure before.

What are you trying to do? I don't think this procedure does what you want. Please explain.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[scottie1]

I was given some code from Tony Jollans and it works perfect when I run it on an Icon on the toolbars it works but when I transfer this code into an embedded button on the spreadsheet it doesn't. The original question was how to open saved spreadsheets and place a value in column A:A to whatever value I have on my template spreadsheet (with the Command button on it) and then close it again. And keep running until it has completed all Values I have In column A:A on my template.

On your template you would have column A:A with the file path and name of where the files are and in column B:B you would have the value you want putting into each of these files you had previously stored. what the code does is open each file individually put whats in column B:B in and then auto fill the A:A and close the file and then proceed to the next file.

 

[SkipVought]

Private Sub CommandButton1_Click()
'
' Macro1 Macro
' Macro recorded 06/04/04 by Adam Mitchell
'
    For Each c In Range("A1", Range("A65535").End(xlUp))
        Workbooks.Open (c.Value)
        With ActiveSheet
            .Cells(1, 1).Formula = c.Offset(, 1).Value
            .Cells(1, 1).AutoFill .Range(.[A1], .Cells(.Cells.Rows.Count, 1).End(xlUp))
        End With
        ActiveWorkbook.Close True
    Next

End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TonyJollans]

Hi scottie1,

The code I gave you possibly isn't the easiest to understand, but what you posted (to start with) isn't quite the same! The AutoFill Range needs to include the source cell, which is A1 (it's not immediately obvious but it is in the With ( ...Cells(1,1)).

Your second posting appears to be better, but the reason it fails is because when it's behind a command button, it's in a different code module (the one belonging to the sheet) and implicit qualifiers are different. One easy way to sort it would be to leave the module where it was (in a normal code module) but make it Public, and your Command Button Click event, just call it. Another, perhaps better, way would be to change the AutoFill line to ..

[blue].AutoFill [highlight].[/highlight]Range("A1", [highlight].[/highlight]Range("B65535").End(xlUp).Offset(0, -1))
[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[scottie1]

Hi Tony

You are a genius.

Skip
Thanks for trying it may of been easier for you if I'd given you a bit more of the backround (previous mail).

Cheers guys

 

[xlbo]

Chaps - could this not be the old "TakeFocusOnClick" error....
Scottie - have a look at the properties for the commandbutton - make sure the "TakeFocusOnClick" property is set to FALSE

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[SkipVought]

...which is the first suggestion I made ;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884