paste data to another workbook

[Afarag]

Hello,

i need a help in paste a range from sheet to another one in a different workbook
i have a workbook called "Main" want to copy a schedule range from a sheet called "Preview" in columns ("A:F") to another workbook called "Database" in an active sheet called "Adherence"
but i need to copy this range to the last row and paste it to the last row of another worksheet
due to the data range "Preview" sheet is variable i need to copy this range till the last indexed row

eg: let's say if i move the data for first time from ("A5:F10") i need it paste in (Adherence) sheet in range ("A2:F7"), the second migration if the range will be from ("A5:F20") in the "Preview" sheet, this data will paste after the last index cell in (Adherence) sheet = will pasted in range ("A8:F23") etc...
to use this migrated data in a pivot table in one range

thanks a lot,

 

[Luke M]

Assuming that you're running the code from main, this should work. Note that you need to properly setup the folder location for "Database".

Sub ExportData()
Dim impWB As Workbook
Dim destWB As Workbook
Dim impWS As Worksheet
Dim destWS As Worksheet
Dim sourceRng As Range
Dim lastRow As Long
Dim filePath As String
Dim shName As String

'Where is desintation workbook?
filePath = "C:\Users\Downloads\Database.xlsx"
'Name of destinarion sheet?
shName = "Adherence"

'Setup our locations
Set impWB = ThisWorkbook
Set impWS = impWB.Worksheets("Preview")


Application.ScreenUpdating = False
With impWS
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set sourceRng = .Range("A5:F" & lastRow)
End With

Set destWB = Workbooks.Open(filePath)
Set destWS = destWB.Worksheets(shName)

'Copy the info over
With destWS
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    sourceRng.Copy
    .Cells(lastRow + 1, "A").PasteSpecial xlPasteValues
End With
'Close destination workbook and save changes
destWB.Close SaveChanges:=True

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

 

[Afarag]

Assuming that you're running the code from main, this should work. Note that you need to properly setup the folder location for "Database".

Sub ExportData()
Dim impWB As Workbook
Dim destWB As Workbook
Dim impWS As Worksheet
Dim destWS As Worksheet
Dim sourceRng As Range
Dim lastRow As Long
Dim filePath As String
Dim shName As String

'Where is desintation workbook?
filePath = "C:\Users\Downloads\Database.xlsx"
'Name of destinarion sheet?
shName = "Adherence"

'Setup our locations
Set impWB = ThisWorkbook
Set impWS = impWB.Worksheets("Preview")


Application.ScreenUpdating = False
With impWS
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set sourceRng = .Range("A5:F" & lastRow)
End With

Set destWB = Workbooks.Open(filePath)
Set destWS = destWB.Worksheets(shName)

'Copy the info over
With destWS
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    sourceRng.Copy
    .Cells(lastRow + 1, "A").PasteSpecial xlPasteValues
End With
'Close destination workbook and save changes
destWB.Close SaveChanges:=True

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

Hi @Luke M

thank you for replying,
unfortunetly i face an error when activating the above code
in highlighting this line with yellow color

Set destWB = Workbooks.Open(filePath)

the destination for the "Database" workbook is
\\10.11.240.10\ds-wfm\Adora\Bus Test\database.xlsx

thanks a lot,

 

[Afarag]

Hi @Luke M

thank you for replying,
unfortunetly i face an error when activating the above code
in highlighting this line with yellow color

Set destWB = Workbooks.Open(filePath)

the destination for the "Database" workbook is
\\10.11.240.10\ds-wfm\Adora\Bus Test\database.xlsx

thanks a lot,

Dear @Luke M

it's go well
thanks a lot,