Run macro on current worksheet

Shoop

New Member
Joined
Nov 13, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
We build bids by dividing a job into areas which we price on individual sheets. Some jobs have more areas than others so we copy the template sheet ("Area 1" in this example), and rename as needed.

Do you have recommendation on how to correct the macro so it copies cells from the sheet where the form control button "Process Area" was pressed?

Range("C4:C9").Select
Selection.Copy
Sheets("Slabs and Tops").Select
Range("B2").Select
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Area 1").Select
Range("C4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Job Summary").Select
Range("C14").Select
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Area 1").Select
Range("J8:J10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Job Summary").Select
Range("D14").Select
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Area 1").Select
End Sub

1607710880676.png
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

JB2020

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
How about something like this?

VBA Code:
Sub CopyValues()

    Range("C4:C9").Copy
    Sheets("Slabs and Tops").Cells(Rows.Count, 2).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   
    Range("C4").Copy
    Sheets("Job Summary").Cells(Rows.Count, 3).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   
    Range("J8:J10").Copy
    Sheets("Job Summary").Cells(Rows.Count, 4).End(xlUp).Offset(1).PasteSpecial xlPasteValues

End Sub
 

Shoop

New Member
Joined
Nov 13, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Thank you, I am going to give it a try over the weekend, we just wrapped our holiday (socially distanced) party and we're bailing for the week.
Thanks again.
 

Shoop

New Member
Joined
Nov 13, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hello JB, I am not yet able to get the desired results but this is certainly due to my limited understanding on how to edit in VBA. I am wondering if you can help a little more.
Can you tell me which part of the VBA code I need to replace with your code or can I change - Sheets ("Area 1").select - to something referring to the active sheet?

The macro is run by a form control button on the template worksheet or "Area 1". When I copy this worksheet to bid other areas, the first part of the macro does what is intended and copies the range C4:C9 from the current worksheet (Area 2, Area 3, ...), but the second and third parts of the macro always looks to "Area 1".

Thank you.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
Sub Process_Area()
   With Sheets("Slabs and Tops")
      Range("C4:C9").Copy
      .Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues, , , True
   End With
   With Sheets("Job Summary")
      .Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Range("C4").Value
      Range("J8:J10").Copy
      .Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues, , , True
   End With
    Application.CutCopyMode = False
End Sub
 

Shoop

New Member
Joined
Nov 13, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Thanks Fluff, working good, I ran from (3) separate work sheets (with values 1,2,3 to check), just need to adjust so it pastes starting on row 3.
1608238299646.png


And these values all starting on row 15, and so on.
1608238191351.png


Suggestions?

Thank you again!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

That looks as though you are using structured tables, in which case you really should not have blank rows in them.
 
Solution

Shoop

New Member
Joined
Nov 13, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
You are a Wizard, Thank you!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,264
Messages
5,635,148
Members
416,844
Latest member
ryanangus496

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top