Cleaner Macro

[Harley78]

I was typing a macro for copying every other column Starting with column E till the end, and got to thinking, how I can do this simpler than

Sheets("Configuration1").Select
Range_("E1,G1,I1,K1,M1,O1,Q1,S1,U1,W1,Y1,AA1,AC1,AE1,AG1,AI1,AK1,AM1,AO1,AQ1.......").Select
Selection.Copy
Sheets("Configuration Pricing BTC").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("A1").Select

I am sure its possible, but just don't know how.

 

[BobUmlas]

To select E1, G1, thru IU1:
Dim Rg as Range
Set Rg = range("E1")
For i=7 to 255 step 2
Set Rg = Union(rg,cells(1,i))
Next

'now Rg is all of them. You can select and/or copy this range:
Rg.Select
or
Rg.Copy

 

[Harley78]

Thanks Bob,

Thanks so much for the Help Bob

Dim Rg As Range
Dim i As Integer

Sheets("Configuration1").Select I put this in the code and is the only problem... it isn't going to this work sheet to copy every other column. and then pasting it in Sheet "Configuration Pricing BTC"

Set Rg = Range("E1")
For i = 7 To 255 Step 2
Set Rg = Union(Rg, Cells(1, i))
Rg.Copy
Sheets("Configuration Pricing BTC").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("A1").Select

Next

 

[BobUmlas]

You need to move the "next" up before the line:
Sheets("Configuration Pricing BTC").Select

 

[Harley78]

I am so sorry Bob,

Even with that change, it isn't going to worksheet that is labeled " Configuration1" to copy A7........

Private Sub CommandButton1_Click()

Dim Rg As Range
Dim i As Integer
Sheets("Configuration1").Select
Set Rg = Range("E1")
For i = 7 To 255 Step 2
Set Rg = Union(Rg, Cells(1, i))
Rg.Copy
Next
Sheets("Configuration Pricing BTC").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("A1").Select
End Sub

 

[BobUmlas]

Sorry - "next" was still misplaced; should be as in this code. However, I can't imagine why Configuration1 sheet is not being selected. Are you using a regular button (as opposed to an ActiveX control toolbox button)? This shouldn't be a Private Sub, but a simple routine in a normal Module. Subs in sheet modules are harder to work with for accessing DIFFERENT sheets, and that MAY be your problem. Put this in a regular module, and change the name; something like:
Sub Simple()
Dim Rg As Range
Dim i As Integer
Sheets("Configuration1").Select
Set Rg = Range("E1")
For i = 7 To 255 Step 2
Set Rg = Union(Rg, Cells(1, i))
Next
Rg.Copy
Sheets("Configuration Pricing BTC").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteAllRange("A1").Select
End Sub

Of course, you'll have to use a Forms button to run Simple.
HTH

 

[Harley78]

I am using a regular command Button on my toolbar....thats it. I'll give it a shot and many thanks for your patience.

 

[Harley78]

Hey Bob,

Ok, As you stated, just make it a macro and select the play macro and it worked, with an addition to the last funtion (Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True). I just like using Buttons, since they are normally just one click.

Thanks for your help Bob

 

[Harley78]

Ok, I got the Command button to work..

I just Add " Application.Run "BM_PRICE_TABLE_ALL.xls!Simple " worked like a charm. I never have done that before,, hmmm I kinda like that....

Now to add the other 6 Tabs.

Thanks again so much