Trials
02-07-2020, 07:09 AM
I have a source tab with three columns of data (Alpha, Beta, and Charlie) and each will be a range .
I am attempting to search through all tabs in my workbook and depending on the worksheet's name will paste one of these ranges to the end column "A" (each tab varies amount of rows).
Here's what I have so far.
Sub edit()
Dim ws As Worksheet
Dim data As Range
Dim units As Range
For Each ws In ActiveWorkbook.Worksheets
'Each sheet has different amount of rows in Column A where the data points are
'Search for sheets which contain Alpha
If ws.Name Like "Alpha-*" Then
Set ActiveSheet = ws
'copy range from the "Instructions" tab and paste it after the last row in destination tab
Worksheets("Instructions").Range("F3:F201").Copy _
Worksheets("ws.Name").Range("A5").End(xlDown).Offset(1, 0).Select
Set data = Worksheet.Range("F3:F201").Select
data.Copy
Set units = Range("A5").End(xlDown).Offset(1, 0).Select
units.PasteSpecial xlPasteValues
End If
'repeat with two more ranges searching for a specific tab names
'perhaps case select or another function would be more efficient?
If ws.Name Like "Beta-*" Then
Set ActiveSheet = ws
Worksheets("Instructions").Range("G3:G201").Copy _
Worksheets("ws.Name").Range("A5").End(xlDown).Offset(1, 0).Select
Set data = Worksheet.Range("G3:G201").Select
data.Copy
Set units = Range("A5").End(xlDown).Offset(1, 0).Select
units.PasteSpecial xlPasteValues
End If
If ws.Name Like "Charlie-*" Then
Set ActiveSheet = ws
Worksheets("Instructions").Range("H3:H201").Copy _
Worksheets("ws.Name").Range("A5").End(xlDown).Offset(1, 0).Select
Set data = Worksheet.Range("H3:H201").Select
data.Copy
Set units = Range("A5").End(xlDown).Offset(1, 0).Select
units.PasteSpecial xlPasteValues
End If
Next ws
End Sub
Please let me know if there is better selecting method. Also, here is the code I used to get started:
https://www.mrexcel.com/board/threads/if-worksheet-name-contains-vba.594090/
Thank you for any help.
I am attempting to search through all tabs in my workbook and depending on the worksheet's name will paste one of these ranges to the end column "A" (each tab varies amount of rows).
Here's what I have so far.
Sub edit()
Dim ws As Worksheet
Dim data As Range
Dim units As Range
For Each ws In ActiveWorkbook.Worksheets
'Each sheet has different amount of rows in Column A where the data points are
'Search for sheets which contain Alpha
If ws.Name Like "Alpha-*" Then
Set ActiveSheet = ws
'copy range from the "Instructions" tab and paste it after the last row in destination tab
Worksheets("Instructions").Range("F3:F201").Copy _
Worksheets("ws.Name").Range("A5").End(xlDown).Offset(1, 0).Select
Set data = Worksheet.Range("F3:F201").Select
data.Copy
Set units = Range("A5").End(xlDown).Offset(1, 0).Select
units.PasteSpecial xlPasteValues
End If
'repeat with two more ranges searching for a specific tab names
'perhaps case select or another function would be more efficient?
If ws.Name Like "Beta-*" Then
Set ActiveSheet = ws
Worksheets("Instructions").Range("G3:G201").Copy _
Worksheets("ws.Name").Range("A5").End(xlDown).Offset(1, 0).Select
Set data = Worksheet.Range("G3:G201").Select
data.Copy
Set units = Range("A5").End(xlDown).Offset(1, 0).Select
units.PasteSpecial xlPasteValues
End If
If ws.Name Like "Charlie-*" Then
Set ActiveSheet = ws
Worksheets("Instructions").Range("H3:H201").Copy _
Worksheets("ws.Name").Range("A5").End(xlDown).Offset(1, 0).Select
Set data = Worksheet.Range("H3:H201").Select
data.Copy
Set units = Range("A5").End(xlDown).Offset(1, 0).Select
units.PasteSpecial xlPasteValues
End If
Next ws
End Sub
Please let me know if there is better selecting method. Also, here is the code I used to get started:
https://www.mrexcel.com/board/threads/if-worksheet-name-contains-vba.594090/
Thank you for any help.