How to paste values in column when it is empty and paste values in column to the right when it is no-CodePudding

I am currently working on Excel VBA.

I have 2 tabs. The 1st Tab name is "Data"(Data gets pulled from server) and 2nd Tab name is "Months".

I would like to copy the Column entries from the "Data" tab over to the specific Column in the "Months" tab. But when the new month data comes I would like to do the same method but offset the copy range each time when it is not empty by 1.

Example below.

Data Tab

Column A
45
65
78
99

The above 4 rows of data for Column A change each month.

Below is the Months Tab to paste it in.

    A              B             C             D             E            F         G
2022/01/31  |  2022/02/28 | 2022/03/31  |  2022/04/30 | 2022/05/31  | Total YTD  |
 26         |             |             |             |             |    26      |
 74         |             |             |             |             |    74      |
 87         |             |             |             |             |    87      |
 98         |             |             |             |             |    98      |

I would like to paste Column A from Data tab numbers into Column B from Months Tab but the code should start with Column A, if Column A is empty then paste the data otherwise move to Column B, if column B is empty, paste the data. If Column B is not empty, move over to Column C and so forth.

This is my code thus far, but it only pastes the data in column G. Any suggestions or help would be appreciated.

 Sub Copy_total()
 '
 ' Copy_total Macro
 '
 ' Keyboard Shortcut: Ctrl q
 '
 Application.ScreenUpdating = False
 Dim copySheet As Worksheet
 Dim pasteSheet As Worksheet

 Set copySheet = Worksheets("Data")
 Set pasteSheet = Worksheets("Months")

    copySheet.Range("A2:A5").Copy
       If pasteSheet.Cells(2, 1) = "" Then
           pasteSheet.Cells(2, 1).PasteSpecial xlPasteValues
       Else
          pasteSheet.Cells(2, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial 
           xlPasteValues
       End If

 Application.CutCopyMode = False
 Application.ScreenUpdating = True
 End Sub

CodePudding user response：

Try something like:

 Sub Copy_total()
 '
 ' Copy_total Macro
 '
 ' Keyboard Shortcut: Ctrl q
 '
    Application.ScreenUpdating = False
    Dim sourceSheet As Worksheet
    Dim destSheet As Worksheet
    Dim destCells

    Set sourceSheet = Worksheets("Data")
    Set destSheet = Worksheets("Months")

    sourceSheet.Range("A2:A5").Copy
    Set destCells = destSheet.Cells(2, 1)

    ' Skips cells already filled.
    Do While destCells <> ""
        Set destCells = destCells.Offset(0, 1)
    Loop

    destCells.PasteSpecial xlPasteValues

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub