Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

copy to the bottom repeatedly when running macro continuously

0

Hi

I look for  way  to  copy  to  the  bottom  repeatedly  

the  code  will  overwrite  for  old  data when  run   macro  more  one  time , but  what  I  want  keep  old  data  and  copy  under   old  data . should  not  replace  , just  continue  copy  to  the  bottom 

Sub ge_tdata()
 Dim ar
 With Application.FileDialog(msoFileDialogFilePicker)
   If .Show Then
      With GetObject(.SelectedItems(1))
         ar = .Sheets(5).Range("g2:i100").Value
         ThisWorkbook.Sheets("sheet1").Cells(2, 2).Resize(UBound(ar), UBound(ar, 2)) = ar
        .Close 0
      End With
   End If
 End With
End Sub
excel
MS Office Version: 2019
posted by: Malkal (rep: 22) on Wed Nov 30, 2022 at 4:42 pm
Answer
Discuss

Answers

0
Selected Answer

Malkal

You just need to calculate the last used row then copy after that. Changes and added comments  are in bold below:

Sub ge_tdata()
 Dim ar As Variant, lRw As Long

 ' get last row number in column B
 lRw = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Row

 With Application.FileDialog(msoFileDialogFilePicker)
   If .Show Then
      With GetObject(.SelectedItems(1))
         ar = .Sheets(5).Range("g2:i100").Value
         ' copy to bottom of row B
         ThisWorkbook.Sheets("sheet1").Cells(lRw + 1, 2).Resize(UBound(ar), UBound(ar, 2)) = ar
        .Close 0
      End With
   End If
 End With
End Sub

Hope this fixes things for you.

posted by: John_Ru (rep: 6142) on Thu Dec 1, 2022 at 3:34 am
Discuss

Discussion

thanks  very  much  John !
Malkal (rep: 22) Dec 1, '22 at 4:08 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login