CODEWITHSUNDEEP
excelvba
Just learning Steve
Just learning Steve

Reputation: 3

Unable to copy data from column to another workbook

I am very new to VBA, trying to write some code that will copy all data except for header from a column in source workbook and paste in a different column with header in another workbook.

I can currently get it to copy the first cell only. I have read through so many things but everything I try is resulting in errors 

Dim details As Variant, mydata As Workbook

Details = ThisWorkbook.Worksheets("sheet1").Range("N")

Set mydata = Workbooks.Open("C:\desktop\book2.xlsx")

RowCount = mydata.Worksheets("Template").Range("B2").CurrentRegion.Rows.Count
mydata.Worksheets("template").Range("B" & RowCount) = YourRef

Upvotes: 0

Views: 38

Answers (1)

user11655492
user11655492

Reputation: 151

Details = ThisWorkbook.Worksheets("sheet1").Range("N")

There is no Range("N") in VBA. You might mean Range("N:N") but if you want to offset one row down to exclude the header row, you cannot use a full column reference. Essentially, the one row offset would be attempting to push the full column off of the bottom of the worksheet.

with ThisWorkbook.Worksheets("sheet1")
  Details = .range(.cells(2, "N"), .cells(.rows.count, "N").end(xlup)).value
end with

The above collect the values from column N starting in row 2 down to the last value in column N,

You need to locate the last cell with data in column B of Worksheets("Template") then offset one row down and resize your target according to the size of the variant array you used to collect the source data.

Dim details As Variant, mydata As Workbook

with ThisWorkbook.Worksheets("sheet1")
  Details = .range(.cells(2, "N"), .cells(.rows.count, "N").end(xlup)).value
end with

Set mydata = Workbooks.Open("C:\desktop\book2.xlsx")

with mydata.Worksheets("template")
  .cells(.rows.count, "B").end(xlup).offset(1, 0).resize(ubound(details, 1), ubound(details, 2)) = Details
end with

Upvotes: 1

Related Questions