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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
That looks as though you are using structured tables, in which case you really should not have blank rows in them.
 
Upvote 0
Solution
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top