VBA code to copy to a specific sheet

[dpaton05]

I am trying to make some way to transfer specific entries to a monthly sheet, that is not depending on the date. I want to select the row then choose a month from a combo, txtDirectMonth and type a year into txtDirectYear then press a button. I then want the row to transfer to the relevant monthly sheet. There are sheets, July 2018 to June 2019. What code could I use?

 

[DanteAmor]

Nop

 

[dpaton05]

My supervisor has just told me that he wants to have the rows to get put in, not only the right month but also in the correct financial year workbook. The workbooks will all be open and I just want every row to be put in the correct month as per your other other code, but also put in the correct financial year workbook.

This tool will be a stand alone tool, with no monthly tabs on it, but it will send information across to the corresponding monthly sheet in the financial year workbook for the row. When entries are written in the table, some may be from different financial years.



Just to recap and some additional information:

• The table is called tblCosting
• The correct code you helped me write that moves the entries to the right month is:

• Sub cmdCopy()
  
  Dim wsDst As Worksheet
  Dim wsSrc As Worksheet
  Dim tblrow As ListRow
  Dim Combo As String
  Dim sht As Worksheet
  Dim tbl As ListObject
  Dim lastrow As Long
  
      Application.ScreenUpdating = False
      
      'assign values to variables
      Set sht = Worksheets("Home")
      
      With sht
  
          Set tbl = .ListObjects("tblCosting")
          
          
          
          For Each tblrow In tbl.ListRows
              Combo = Format(tblrow.Range.Cells(1, 25), "mmmm yyyy")
              lastrow = Worksheets(Combo).Cells(Rows.Count,  "A").End(xlUp).Row + 1                                    'number of  first empty row in column A of Combo
  
              Set wsDst = Sheets(Combo)
              
              With wsDst
                  'This copies the first 10 columns, i.e. A:J, of the  current row of the table to column A in the destination sheet.
                  tblrow.Range.Resize(, 10).copy
                  .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
                  'This should go to the 15th column in the current row,  i.e. column O, and copy that column and the next 2 columns, i.e. O:Q, to  column K on the destination sheet.
                  tblrow.Range.Offset(, 14).Resize(, 3).copy
                  .Range("K" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
                  'Similarly this should copy columns AD:AF from the table to column N on the destination sheet.
                  tblrow.Range.Offset(, 29).Resize(, 3).copy
                  .Range("N" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
                  
              End With
              
          Next tblrow
          
          Call SortDates
          
      End With
      
      Application.CutCopyMode = False
  
      Application.ScreenUpdating = True
      
  End Sub

• The financial year goes from July-June the following year
• The workbook name of each financial year document will be, for instance, "2019 - 2020 NPSS Work Allocation Sheet"
• I have worked out the formulas to arrive at the correct name of the workbook to place it in.
• The column that has this formula for the row is AJ
• The output of the formula in this column is the above text, "2019 - 2020 NPSS Work Allocation Sheet"

These documents will already be created and open. I just need help changing the above code so that each record in the table will go into the correct financial year document.

I know it is not much coding to do but I am just not sure how to do it.

Any help would be greatly appreciated.
Thanks,
Dave

 

[dpaton05]

Can someone please have a look at my previous question from post #12 ?

 

[dpaton05]

I will start with one thing at a time.

Choosing the right month and year to put the entries in.

• tblCosting starts in column A and has 36 columns
• Data is entered up to column J, the rest is calculations on entered data
• tblCosting has 1-x rows.
• When all rows for a required job have been entered, a button is pressed and they are transferred to the correct document and monthly sheet, depending on the date.
• Each document is for an Australian financial year (July-June)
• Sheet names are the month name
• Document names are for instance, "2019 - 2020 NPSS Work Allocation Sheet" for the 2019-2020 financial year.

Here is the start of my copy code someone wrote for me and I tried to modify to include being able to transfer to a new document. When I try and run it, I get subscript out of range and I have coloured the line that is highlighted red. What have I done wrong?

Sub cmdCopy()

Dim wsDst As Worksheet
Dim wsSrc As Worksheet
Dim tblrow As ListRow
Dim Combo As String
Dim sht As Worksheet
Dim tbl As ListObject
Dim lastrow As Long
Dim DocYearName As String


    Application.ScreenUpdating = False
    
    'assign values to variables
    Set sht = Worksheets("Home")
    
    With sht

        Set tbl = .ListObjects("tblCosting")
        
        
        
        For Each tblrow In tbl.ListRows
            Combo = tblrow.Range.Cells(1, 23).Value
            lastrow = Worksheets(Combo).Cells(Rows.Count, "A").End(xlUp).Row + 1                                    'number of first empty row in column A of Combo
            DocYearName = Worksheets("home").Cells(1, 36).Value
      [COLOR=#ff0000]      
            Set wsDst = Workbooks(DocYearName).Worksheets(Combo)[/COLOR]
          
                With wsDst
                    'This copies the first 10 columns, i.e. A:J, of the current row of the table to column A in the destination sheet.
                    tblrow.Range.Resize(, 10).copy
                    .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
                    'This should go to the 15th column in the current row, i.e. column O, and copy that column and the next 2 columns, i.e. O:Q, to column K on the destination sheet.
                    tblrow.Range.Offset(, 14).Resize(, 3).copy
                    .Range("K" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
                    'Similarly this should copy columns AD:AF from the table to column N on the destination sheet.
                    tblrow.Range.Offset(, 29).Resize(, 3).copy
                    .Range("N" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
                    
                End With

 

[dpaton05]

I worked it out, I had a blank row and that is why it wouldn't work.

 

[dpaton05]

Hi Dante,

Could you comment on this thread please?

Thanks,
Dave

https://www.mrexcel.com/forum/excel-questions/1089760-transferring-between-workbooks.html