Consolidating/Improving VBA

anichols

New Member
Joined
Mar 11, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Hello all,

I have this VBA code:
VBA Code:
    Sheets(1).Select
    Rows("1:1").Copy
    Sheets(4).Rows("1:1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(5).Rows("1:1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(6).Rows("1:1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(7).Rows("1:1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(8).Rows("1:1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(9).Rows("1:1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(10).Rows("1:1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(4).Cells.EntireColumn.AutoFit
    Sheets(5).Cells.EntireColumn.AutoFit
    Sheets(6).Cells.EntireColumn.AutoFit
    Sheets(7).Cells.EntireColumn.AutoFit
    Sheets(8).Cells.EntireColumn.AutoFit
    Sheets(9).Cells.EntireColumn.AutoFit
    Sheets(10).Cells.EntireColumn.AutoFit
This works great, no problems whatsoever, but I was curious, in the interest of becoming better with my code, is there a way to shorten that code as I am doing the same thing to the same range of sheets?

Many thanks
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,030
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
   Sheets(1).Rows(1).Copy
   Sheets(Array(4, 5, 6, 7, 8, 9, 10)).Select
   Selection.Rows(1).PasteSpecial xlPasteValues
   For i = 4 To 10
      Sheets(i).Columns.AutoFit
   Next i
   Sheets(1).Select
 

anichols

New Member
Joined
Mar 11, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
   Sheets(1).Rows(1).Copy
   Sheets(Array(4, 5, 6, 7, 8, 9, 10)).Select
   Selection.Rows(1).PasteSpecial xlPasteValues
   For i = 4 To 10
      Sheets(i).Columns.AutoFit
   Next i
   Sheets(1).Select
That's very nice and consolidated, however, it is pasting to row 2 and not row 1 on all the sheets. :)
 

anichols

New Member
Joined
Mar 11, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
This seemed to fix it though.

VBA Code:
   Sheets(1).Rows(1).Copy
   Sheets(Array(4, 5, 6, 7, 8, 9, 10)).Select
   Selection.Rows(0).PasteSpecial xlPasteValues
   For i = 4 To 10
      Sheets(i).Columns.AutoFit
   Next i
   Sheets(1).Select
 

Fluff

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

ADVERTISEMENT

That should fail as there is no such thing in Excel as row zero.
 

anichols

New Member
Joined
Mar 11, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
That should fail as there is no such thing in Excel as row zero.
So what is supposed to work in place? (1) and ("1:1") result in row 2 being filled in.
 

Fluff

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

ADVERTISEMENT

It should be 1 as I showed, using 0 should result in a RTE 1004 "Application-defined or object-defined error"
 

anichols

New Member
Joined
Mar 11, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
That's really odd, is it possible that there's some VBA setting that I'm unaware of?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,030
Office Version
  1. 365
Platform
  1. Windows
Not that I'm aware of.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,424
Messages
5,642,037
Members
417,251
Latest member
Dordrecht

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