Copying and pasting a specific range in all worksheets but two.

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
234
Office Version
  1. 365
Platform
  1. Windows
Hi folks,

Situation: I have a specific range---$F$7:$F$13---that I need to copy from all worksheets except two worksheets: sh.name "MACRO" and sh.name "ExtraData". As I don't know how many worksheets I might have to copy $F$7:$F$13 from, I found this macro to faciliate the copy/paste.

Code:
Public Sub m()     Dim lRow As Long
     Dim sh As Worksheet
     Dim shArc As Worksheet
     Set shArc = ThisWorkbook.Worksheets("ExtraData")
     For Each sh In ThisWorkbook.Worksheets
         Select Case sh.Name
         [COLOR=#0000ff][B]    Case Is <> "MACRO"[/B][/COLOR]
                 lRow = shArc.Range("A" & Rows.Count).End(xlUp).Row + 1
                 sh.Range("$F$7:$F$13").Copy
                 shArc.Range("A" & lRow).PasteSpecial
         End Select
     Next
     Application.CutCopyMode = False
     Set shArc = Nothing
     Set sh = Nothing
 End Sub

Would anybody be able to help me modify Case Is <> "MACRO" in the above coding so that it excludes both sh.name "MACRO" and sh.name "ExtraData" from the copy function?

Kind regards,

Doug
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Public Sub m()
   Dim lRow As Long
   Dim sh As Worksheet
   Dim shArc As Worksheet
   Set shArc = ThisWorkbook.Worksheets("ExtraData")
   For Each sh In ThisWorkbook.Worksheets
      If sh.Name <> "MACRO" And sh.Name <> "ExtraData" Then
         lRow = shArc.Range("A" & Rows.Count).End(xlUp).Row + 1
         sh.Range("$F$7:$F$13").Copy
         shArc.Range("A" & lRow).PasteSpecial
      End If
   Next
   Application.CutCopyMode = False
   Set shArc = Nothing
   Set sh = Nothing
 End Sub
 

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
234
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Public Sub m()
   Dim lRow As Long
   Dim sh As Worksheet
   Dim shArc As Worksheet
   Set shArc = ThisWorkbook.Worksheets("ExtraData")
   For Each sh In ThisWorkbook.Worksheets
      If sh.Name <> "MACRO" And sh.Name <> "ExtraData" Then
         lRow = shArc.Range("A" & Rows.Count).End(xlUp).Row + 1
         sh.Range("$F$7:$F$13").Copy
         shArc.Range("A" & lRow).PasteSpecial
      End If
   Next
   Application.CutCopyMode = False
   Set shArc = Nothing
   Set sh = Nothing
 End Sub

Thanks for responding Fluff,
I also thought of this as I have another bit of similar vba. The issue with this work around is that for some reason, it only copies cell F11 and pastes it into A2:A8, A16:A24, and A30:36 of sh.name "ExtraData". I have to admit, when this happened, I knew I was stumped...
Any ideas?

Kind regards,

Doug.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
Do you have any merged cells?
If not try
Code:
      If sh.Name <> "MACRO" And sh.Name <> "ExtraData" Then
         lRow = shArc.Range("A" & Rows.Count).End(xlUp).Row + 1
         sh.Range("F7:F13").Copy shArc.Range("A" & lRow)
      End If
 

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
234
Office Version
  1. 365
Platform
  1. Windows
Hi Fluff,

I got rid of merged cells and it works now.

Many thanks,

Doug.
 

Fluff

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

Watch MrExcel Video

Forum statistics

Threads
1,129,923
Messages
5,639,024
Members
417,063
Latest member
ShijinMathew

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