I have a VBA code that is currently running where it is coping cell (C2: C3) from all sheets and is pasting in the "Master" sheet. The issue that I have is that I want it to only copy the visible sheets as some of my hidden sheets have different data running my sheets. I also have an issue of it pasting in the next row and I want it to paste in the next column and can't figure it out :/.
Option Explicit
Sub Sample()
Dim wsInput As Worksheet, wsOutput As Worksheet
Dim rng As Range
Dim LRowO As Long, LRowI As Long
Set wsOutput = ThisWorkbook.Sheets("Master")
For Each wsInput In ThisWorkbook.Worksheets
If wsInput.Name <> wsOutput.Name Then
With wsInput
Set rng = .Range("C2:C3")
rng.Copy
With wsOutput
LRowO = .Range("A" & .Rows.Count).End(xlUp).Row + 1
.Range("A" & LRowO).PasteSpecial xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
End With
End If
Next wsInput
Exit Sub
End Sub
Sounds like you need to check if the worksheet is hidden and also keep track of what the next column would be and increment it each time you paste into it. Here is your code modified for both of those things
Option Explicit
Sub Sample()
Dim wsInput As Worksheet, wsOutput As Worksheet
Dim rng As Range
LRowI As Long
Dim nextCol as Long
Set wsOutput = ThisWorkbook.Sheets("Master")
nextCol = 1
For Each wsInput In ThisWorkbook.Worksheets
If wsInput.Name <> wsOutput.Name and wsInput.Visible = True Then
With wsInput
Set rng = .Range("C2:C3")
rng.Copy
With wsOutput
.Cells(1, nextCol).PasteSpecial xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
nextCol = nextCol + 1
End With
End With
End If
Next wsInput
Exit Sub
End Sub
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments