elmnas
09-07-2015, 04:21 AM
Hello guys,
I have made following code,
But I want to create an array for sheets(mysheet1,mysheet2,mysheet3,mysheet4 etc)
instead of use hardcoded code.
Sub GetColData()
For Each sht In ThisWorkbook.Worksheets
mysheetname = sht.Name
If sht.Name Like "mysheet1"Then
Sheets(mysheetname).Select
Columns("A:A").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("A:A").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("C:C").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("D:D").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("D:D").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("H:H").Select
ActiveSheet.Paste
End If
If sht.Name Like "mysheet2" Then
Sheets(mysheetname).Select
Columns("A:A").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("A:A").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("C:C").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("D:D").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("D:D").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("H:H").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("E:E").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("I:I").Select
ActiveSheet.Paste
End If
If sht.Name Like "mysheet3" Then
Sheets(mysheetname).Select
Columns("A:A").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("A:A").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("C:C").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("D:D").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("D:D").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("H:H").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("E:E").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("I:I").Select
ActiveSheet.Paste
End If
If sht.Name Like "mysheet4" Then
Sheets(mysheetname).Select
Columns("A:A").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("A:A").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("C:C").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("D:D").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("D:D").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("H:H").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("E:E").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("I:I").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("E:E").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("I:I").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("G:G").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("J:J").Select
ActiveSheet.Paste
End If
Next sht
End Sub
Could someone help me?
Thank you in advance
I have made following code,
But I want to create an array for sheets(mysheet1,mysheet2,mysheet3,mysheet4 etc)
instead of use hardcoded code.
Sub GetColData()
For Each sht In ThisWorkbook.Worksheets
mysheetname = sht.Name
If sht.Name Like "mysheet1"Then
Sheets(mysheetname).Select
Columns("A:A").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("A:A").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("C:C").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("D:D").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("D:D").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("H:H").Select
ActiveSheet.Paste
End If
If sht.Name Like "mysheet2" Then
Sheets(mysheetname).Select
Columns("A:A").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("A:A").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("C:C").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("D:D").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("D:D").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("H:H").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("E:E").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("I:I").Select
ActiveSheet.Paste
End If
If sht.Name Like "mysheet3" Then
Sheets(mysheetname).Select
Columns("A:A").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("A:A").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("C:C").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("D:D").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("D:D").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("H:H").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("E:E").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("I:I").Select
ActiveSheet.Paste
End If
If sht.Name Like "mysheet4" Then
Sheets(mysheetname).Select
Columns("A:A").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("A:A").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("C:C").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("D:D").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("D:D").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("H:H").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("E:E").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("I:I").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("E:E").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("I:I").Select
ActiveSheet.Paste
Sheets(mysheetname).Select
Columns("G:G").Select
Selection.Copy
Sheets("Target_sheet").Select
Columns("J:J").Select
ActiveSheet.Paste
End If
Next sht
End Sub
Could someone help me?
Thank you in advance