Run a macro to VBA automated function

Status
Not open for further replies.

Unexpc

Active Member
Joined
Nov 12, 2020
Messages
309
Office Version
  1. 2019
Platform
  1. Windows
Hi guys
I have this code from a professional member of mrexcel

Code:
Sub MyCopyRange()

    Dim nr As Long
    Dim i As Long
        Application.ScreenUpdating = False

'   Set initial value of next row
    nr = 34
    
'   Copy range
    For i = 2 To 30840 Step 1
'   Copy Range based Cell B66  & B98
      If Sheets("sheet1").Range("B" & (nr - 2) * i + 2).Value <> "" Then
      
        Sheets("sheet2").Range("A" & nr * (i - 1) + 7 & ":H" & nr * i + 6).Copy Sheets("sheet2").Range("A" & nr * i + 7)
'       Add 34 to next row

      Else
         Exit Sub
      End If
     Next i

    Application.ScreenUpdating = True
    
End Sub

I want 2 things if anyone can added
1:this is work by run a macro, i want this is will be automated
2:i want sheet2 in this code will be hidden
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,792
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
1. Are your Sheet2 before code run is hide, and after running code you want Sheet2 hide Again.
2. You can Use WorkSheet Change Event on Sheet1. Then Every time you change data in Sheet1, The code run, But your file coming up slowly.
3. Also You can use changing data in specific range. But I don't think I can do it, but can try??
 

Unexpc

Active Member
Joined
Nov 12, 2020
Messages
309
Office Version
  1. 2019
Platform
  1. Windows
1. Are your Sheet2 before code run is hide, and after running code you want Sheet2 hide Again.
2. You can Use WorkSheet Change Event on Sheet1. Then Every time you change data in Sheet1, The code run, But your file coming up slowly.
3. Also You can use changing data in specific range. But I don't think I can do it, but can try??
1.Yes, before and after, sheet be hidden
2.i want when B66 And next B98 and next 32 rows... fill in sheet 1 in this code, thats doing automatically copy and paste
3. Yes that is i want
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
Duplicate Make automatic function formula

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,129,527
Messages
5,636,834
Members
416,945
Latest member
Himu

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