0

I am new to VBA and I have encountered a problem to write a certain macro. I have retrieved data from a database on daily yield, ask price and bid price for around 150 bonds. All the yields, ask and bid prices are sorted in order in different sheets. I would like to get for each bond a new sheet with the corresponding yield, bid price and ask price. My yield is in sheet 2, Ask price in sheet 3 and bid price is in sheet 4. It should always copy two entire columns, so for example for the first bond, it should copy the first two columns (two columns because one is the yields and one with the dates) of sheet 2, the first 2 of sheet 3 and the first two columns of sheet 4 and put them next to each other in a new sheet, for the next bond it should copy the next two colums of each sheet and copy it in a new sheet and so on. Is there a possibility to write a macro to do this?

the following is a record of a macro I did manually for the first two bonds 

ActiveCell.Offset(0, -6).Columns("A:B").EntireColumn.Select
ActiveCell.Offset(0, -6).Range("A1").Activate
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Sheets("Sheet3").Select
ActiveCell.Columns("A:B").EntireColumn.Select
ActiveCell.Offset(1, 0).Range("A1").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet7").Select
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet4").Select
ActiveCell.Columns("A:B").EntireColumn.Select
ActiveCell.Offset(1, 0).Range("A1").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet7").Select
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
Sheets.Add After:=ActiveSheet
Sheets("Sheet2").Select
ActiveCell.Offset(0, 2).Columns("A:B").EntireColumn.Select
ActiveCell.Offset(0, 2).Range("A1").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet8").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
ActiveCell.Offset(0, 2).Range("A1:B1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Ask Close"
ActiveCell.Columns("A:B").EntireColumn.Select
ActiveCell.Activate
Selection.Copy
Sheets("Sheet8").Select
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet4").Select
ActiveCell.Offset(0, 2).Columns("A:B").EntireColumn.Select
ActiveCell.Offset(0, 2).Range("A1").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet8").Select
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Paulien Aerts
  • 1
  • 1
    For future reference, please please please, [avoid using `Select` and `Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and also, indent your code! – Samuel Hulla Apr 07 '19 at 19:11

1 Answers1

0

This should do what you want as long as your sheets all have the default names.

Option Explicit

Sub copyColtoSheet()
Dim pasteSheet As Worksheet
Dim copySheet As Worksheet
Dim i As Integer

'Create new sheet to paste column data to
With ThisWorkbook
    .Sheets.Add After:=.Sheets(.Sheets.Count)
    Set pasteSheet = .Worksheets("Sheet" & .Sheets.Count)
End With

'Copy Sheet columns to new sheet
Dim pasteColumn As Integer
pasteColumn = 1
For i = 2 To 4
    With pasteSheet
        Dim allRows As Integer
        Set copySheet = ThisWorkbook.Worksheets("Sheet" & i)
        allRows = copySheet.Cells(Rows.Count, 1).End(xlUp).Row
        .Range(.Range(.Cells(1, pasteColumn), .Cells(allRows, pasteColumn)), .Range(.Cells(1, pasteColumn + 1), .Cells(allRows, pasteColumn + 1))).Value = copySheet.Range("A:B").Value
        pasteColumn = pasteColumn + 2
    End With
Next i
End Sub

I have tested this code and it works.

TOTM
  • 107
  • 7
  • I don't think this is what OP wanted, but it's hard to tell exactly, because the question is unfortunately very badly worded :( – Samuel Hulla Apr 07 '19 at 19:13
  • Hey Steve, thank you for helping me! I making sense of it! Although it gives me a error code 9 at the following line: "Set pasteSheet = .Worksheets("Sheet" & .Sheets.Count)" – Paulien Aerts Apr 07 '19 at 19:55
  • Do your sheet names have the default value(Sheet1, Sheet2, etc) or do you have them named something else? – TOTM Apr 07 '19 at 20:41
  • I accidentally deleted everything and need to rebuild it. I think I have the solution figured out for you though. – TOTM Apr 07 '19 at 23:57
  • Thank you! Yes I have the default values for all my sheets. However it works for the first bond, but I think the mistake finds itself in the loop you created. There should be a loop for the columns in sheet 2,3 and 4. So for the first bond, it should take indeed range "A:B", but for the next bond and thus the next sheet, this should be "C:D" and the following for "E:F". For the sheet in which it should take the next columns and paste it to another sheet, they should remain the same (so always sheet 2,3,4). Now, it only creates the first bond and not all of them. – Paulien Aerts Apr 08 '19 at 09:10