If cell has data, move down 1 row and paste

[Glasgowsmile]

Good morning,

I have the following macro. I simply want it to look at A1 on the current worksheet, if that cell has data then move down to A2. Then the next time I important data it will look at A2 and paste in A3 etc.

Not quite sure how to do this myself, any help is appreciated!

Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
Dim crnt As Workbook
Dim source As Workbook

With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "CSV Files", "*.csv"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
         Workbooks.Open .SelectedItems(1)
         Set source = ActiveWorkbook
         
         Sheets("Day").Range("A22").Copy
         Sheet1.Range("A1").PasteSpecial xlPasteValues
      End If
End Sub

 

[Fluff]

Try replacing this

Sheet1.Range("A1").PasteSpecial xlPasteValues

with

Sheet1.Range("A"& Rows.Count).end(xlup).offset(1).PasteSpecial xlPasteValues

 

[Glasgowsmile]

Try replacing this

Sheet1.Range("A1").PasteSpecial xlPasteValues

with

Sheet1.Range("A"& Rows.Count).end(xlup).offset(1).PasteSpecial xlPasteValues

Wow, that's it? I really expected it to be more complicated.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Glasgowsmile]

Follow-up question/problem.

If I wanted to copy multiple cells and paste it across A2, B2, C2 and then move down to A3, B3, C3 etc each time - how can I accomplish that?

I tried to add a transpose thinking that would resolve it but it results in an error.

Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
Dim crnt As Workbook
Dim source As Workbook

With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Files", "*.xlsx"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
         Workbooks.Open .SelectedItems(1)
         Set source = ActiveWorkbook
         
         Sheets("Glance").Range("Z10, Z14").Copy
         Sheet1.Range("B", "C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues, Transpose:=True
      End If
End With
End Sub

 

[Fluff]

How about

         Sheets("Glance").Range("Z10:Z14").Copy
         Sheet1.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues, Transpose:=True

 

[Glasgowsmile]

How about

         Sheets("Glance").Range("Z10:Z14").Copy
         Sheet1.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues, Transpose:=True

Re-opening this as I'm running into a problem with code.

This code below is not transposing... and I don't understand why

Sheets("Sheet1").Range("G11, L11, Q11, S11").Copy
         Sheet2.Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues, Transpose:=True

however, this code which impacts a different sheet is tranposing

Sheets("Sheet1").Range("Z10, Z14, Z18, Z20").Copy
         Sheet1.Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues, Transpose:=True

 

[Fluff]

In what way doesn't it work?

 

[Glasgowsmile]

In what way doesn't it work?

It doesn't transpose, it just puts all the data from the cells in B3:B6 instead of B3, C3, D3, E3.

 

[Fluff]

That's exactly what it's meant to do, so it's working fine.