VBA: copy one column to another for various sheets (Loop)

[Tarek78]

Hi Everyone,

I have tried to copy my sheet using XL2BB.

I have a workbook with multiple sheets ("Beginning", "A", "B", "C", "End").

For all the sheets from Beginning until the end (I might include some new sheets from one month to another), I want to copy the column B to Column C.

Is that something we can do using a loop?

Thanks

Book1
	A	B	C
1	ACCOUNT	REVENUE	REVENUE
2	ABC	10
3	ABC	12
4	ABC	12
5	ABB	11
6	ADD	11
Beginning

 

[My Aswer Is This]

Try this:

Sub Copy_Range()
'Modified 6/25/2022  12:44:07 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
    For i = 1 To Sheets.Count
        Sheets(i).Columns(2).Copy Sheets(i).Columns(3)
    Next
    Application.CutCopyMode = False
    
Application.ScreenUpdating = True
End Sub

 

[Tarek78]

Thanks for your help. This would work.

However, what if I want to only copy all the sheets between « Beginning » and « End »?

 

[My Aswer Is This]

Thanks for your help. This would work.

However, what if I want to only copy all the sheets between « Beginning » and « End »

Thanks for your help. This would work.

However, what if I want to only copy all the sheets between « Beginning » and « End »?

You mean like 1 to 8 or 8 to 12 or like 8 to 9 Is that what you may want?

 

[Tarek78]

Hi,

Sorry if my last message wasn’t too clear.

Let’s say I have all theses sheets in June :

« Mapping », « Parameters », « Beginning », « A », « B », « C », « End », « project list »

I want to only copy everything between Beginning and End>> A, B and C

Let’s say in July I have the following sheets :

« Mapping », « Parameters », « Beginning », « A », « B », « C », « D » , « End », « project list », « Account List »

The result would be : A, B, C, D

 

[My Aswer Is This]

Try this:

You will get an Input Box asking to the first sheet number and the last sheet number

Sub Copy_Range()
'Modified  6/25/2022  7:23:05 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
   Dim ans As Long
   Dim anss As Long
   Dim n As Long
   n = Sheets.Count
   
   ans = InputBox("Enter first sheet number", "The Minimum number you can enter is " & 1, "")
   If ans < 1 Then MsgBox "You Entered " & ans & vbNewLine & " This is not a Proper number": Exit Sub
    
    anss = InputBox("Enter last sheet number", "The Maximum number you can enter is " & n, "")
    If anss > n Then MsgBox "You Entered " & anss & vbNewLine & " This is not a Proper number": Exit Sub
    
    For i = 1 To n
        Select Case i
            Case ans To anss
                Sheets(i).Columns(2).Copy Sheets(i).Columns(3)
        End Select
    Next
    
Application.ScreenUpdating = True
End Sub

 

[Tarek78]

Thanks !!! I will try this !

 

[My Aswer Is This]

Lets try this one it has error checking:

Sub Copy_Range()
'Modified  6/25/2022  7:56:25 AM  EDT
On Error GoTo M
Application.ScreenUpdating = False
Dim i As Long
   Dim ans As Long
   Dim anss As Long
   Dim n As Long
   n = Sheets.Count
   
   ans = InputBox("Enter first sheet number", "The Minimum number you can enter is " & 1, "")
   If ans < 1 Then MsgBox "You Entered " & ans & vbNewLine & " This is not a Proper number": Exit Sub
    
    anss = InputBox("Enter last sheet number", "The Maximum number you can enter is " & n, "")
    If anss > n Then MsgBox "You Entered " & anss & vbNewLine & " This is not a Proper number": Exit Sub
    
    For i = 1 To n
        Select Case i
            Case ans To anss
                Sheets(i).Columns(2).Copy Sheets(i).Columns(3)
        End Select
    Next
    
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "We had a Problem" & vbNewLine & "You may have entered a character when you should have entered a number" & vbNewLine & "Or some other problem"
End Sub

 

[My Aswer Is This]

Thanks !!! I will try this !

Try my second one it has error checking
I hate all this Grammer checking in this forum.

 

[My Aswer Is This]

Lets try this one it has error checking:

Sub Copy_Range()
'Modified  6/25/2022  7:56:25 AM  EDT
On Error GoTo M
Application.ScreenUpdating = False
Dim i As Long
   Dim ans As Long
   Dim anss As Long
   Dim n As Long
   n = Sheets.Count
  
   ans = InputBox("Enter first sheet number", "The Minimum number you can enter is " & 1, "")
   If ans < 1 Then MsgBox "You Entered " & ans & vbNewLine & " This is not a Proper number": Exit Sub
   
    anss = InputBox("Enter last sheet number", "The Maximum number you can enter is " & n, "")
    If anss > n Then MsgBox "You Entered " & anss & vbNewLine & " This is not a Proper number": Exit Sub
   
    For i = 1 To n
        Select Case i
            Case ans To anss
                Sheets(i).Columns(2).Copy Sheets(i).Columns(3)
        End Select
    Next
   
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "We had a Problem" & vbNewLine & "You may have entered a character when you should have entered a number" & vbNewLine & "Or some other problem"
End Sub

I'm assuming this worked.
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.