VBA macro to copy values from column B,C,M in reference sheet to column A,B,C in another sheet

[alexcon]

Hi, I'd like to create a button macro which copies the values from column B,C,M from the reference sheet into column A,B,C of another sheet called "Pivot Staging".

If possible would be useful if the "Pivot Staging" sheet was cleared every time the button is clicked so fresh data can fill it from the reference sheet.

 

[My Aswer Is This]

You said:
I'd like to create a button macro which copies the values from column B,C,M from the reference sheet
Is the sheet named "reference"

 

[alexcon]

Sorry- yes to clarify in this case the reference sheet is also called "reference".

 

[My Aswer Is This]

Try this:

Sub Selecting()
Sheets("reference").Activate
Sheets("Pivot Staging").Cells.Clear
Sheets("reference").Range("B1,C1,M1").EntireColumn.Copy Sheets("Pivot Staging").Range("A1")
End Sub

 

[alexcon]

Works perfect thank you!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Works perfect thank you!

 

[Rick Rothstein]

Try this:

Sub Selecting()
Sheets("reference").Activate
Sheets("Pivot Staging").Cells.Clear
Sheets("reference").Range("B1,C1,M1").EntireColumn.Copy Sheets("Pivot Staging").Range("A1")
End Sub

Since you have specified the sheet references directly, there is no need to activate the "reference" worksheet. Also, again, since you are copying entire columns, there is no real need to clear the "Pivot Staging" cells as the data in Columns A:C will be overwritten in their entirety. Given that, your macro can be reduced to this...

[table="width: 500"]
[tr]
	[td]Sub Selecting()
  Sheets("reference").Range("B1,C1,M1").EntireColumn.Copy Sheets("Pivot Staging").Range("A1")
End Sub[/td]
[/tr]
[/table]

That macro, in turn, can be "simplified" to this...

Sub Selecting()
  [Reference!B:C,Reference!M:M].Copy ['Pivot Staging'!A1]
End Sub

 

[alexcon]

Since you have specified the sheet references directly, there is no need to activate the "reference" worksheet. Also, again, since you are copying entire columns, there is no real need to clear the "Pivot Staging" cells as the data in Columns A:C will be overwritten in their entirety. Given that, your macro can be reduced to this...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Selecting()
  Sheets("reference").Range("B1,C1,M1").EntireColumn.Copy Sheets("Pivot Staging").Range("A1")
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

That macro, in turn, can be "simplified" to this...

Sub Selecting()
  [Reference!B:C,Reference!M:M].Copy ['Pivot Staging'!A1]
End Sub

Hi there, sorry this has taken a while. The stuff we're working with has changed slightly.

Column M from the reference sheet is now hidden, so when it copies to the new sheet, it stays hidden.

Are you able to make it copy and unhide column M when it copies to column C in the new sheet?

 

[Rick Rothstein]

Are you able to make it copy and unhide column M when it copies to column C in the new sheet?

Add this line of code after the one inside the macro...

Columns("M").Hidden = False