[Solved] Import Dataset to template in the background

[bino1121]

I have a large data set that changes in size that I am trying to import into a template sheet in a different workbook. So the goal is to get the data set workbook to copy and paste from A1 down to a variable sized range (Could change from day to day) and when it imports into the template sheet it should start from A3 and continue down. The code below is what I am currently using I typed it out so if there are typos the actual code on Excel runs just not the way I would like.

So far I have:

Sub DataSet

Dim Wb1 as Worksheet
DIm Wb2 as Worksheet
Dim CopyLastRow
DIm DestLastRow

Application.ScreenUpdating = False

Workbooks.Open ("C:\Users\xxxx\Desktop\Dataset.xls")

Set Wb1 = Workbooks("Dataset.xls").Sheets("Dataset sheet one")
Set Wb2 = Workbooks("Template.xlsx").Sheets("Open sheet")

CopyLastRow = Wb1.Cekks(Wb1.Rows.Count, "A".End(xlUp).Row

DestLastRow = Wb2.Cells(Wb1.Rows.Count, "A". End(xlUp). Row + 1

Wb1.Range("A3:AS" & CopyLastRow).Copy Wb2.Range("A" & DestLastRow)
Workbooks(Dataset.xls").Close SaveChanges:= False

End Sub

 

[Yousuf Shovon]

I have a large data set that changes in size that I am trying to import into a template sheet in a different workbook. So the goal is to get the data set workbook to copy and paste from A1 down to a variable sized range (Could change from day to day) and when it imports into the template sheet it should start from A3 and continue down. The code below is what I am currently using I typed it out so if there are typos the actual code on Excel runs just not the way I would like.

So far I have:

Sub DataSet

Dim Wb1 as Worksheet
DIm Wb2 as Worksheet
Dim CopyLastRow
DIm DestLastRow

Application.ScreenUpdating = False

Workbooks.Open ("C:\Users\xxxx\Desktop\Dataset.xls")

Set Wb1 = Workbooks("Dataset.xls").Sheets("Dataset sheet one")
Set Wb2 = Workbooks("Template.xlsx").Sheets("Open sheet")

CopyLastRow = Wb1.Cekks(Wb1.Rows.Count, "A".End(xlUp).Row

DestLastRow = Wb2.Cells(Wb1.Rows.Count, "A". End(xlUp). Row + 1

Wb1.Range("A3:AS" & CopyLastRow).Copy Wb2.Range("A" & DestLastRow)
Workbooks(Dataset.xls").Close SaveChanges:= False

End Sub

Dear Bino,

Good to hear from you again. Also, thanks for the detailed explanation of the problem. As I understand, you wish for a dynamic range of the source workbook to import the data into another template workbook which will have a variable-sized range too. So, here is how I modified the code:

Sub DataSet()

Dim Wb1 As Worksheet
Dim Wb2 As Worksheet
Dim CopyLastRow As Long
Dim DestLastRow As Long

Application.ScreenUpdating = False

Workbooks.Open ("C:\Users\xxxx\Desktop\Dataset.xls")

Set Wb1 = Workbooks("Dataset.xls").Sheets("Dataset sheet one")
Set Wb2 = Workbooks("Template.xlsx").Sheets("Open sheet")

CopyLastRow = Wb1.Range("A" & Wb1.Rows.Count).End(xlUp).Row 'Get last row of data dynamically
DestLastRow = Wb2.Range("A" & Wb2.Rows.Count).End(xlUp).Row + 1

Wb1.Range("A1:AS" & CopyLastRow).Copy Wb2.Range("A3")

Workbooks("Dataset.xls").Close SaveChanges:=False

Application.ScreenUpdating = True

End Sub

Further, if you want to import data from Google sheet, follow this article https://www.exceldemy.com/import-data-from-google-sheets-to-excel-vba/

I hope this works for you. Thanks for staying with us.

Regards,
Yousuf Shovon