Copy specific Columns from one workbook to Another - VBA Excel

[josephmary]

Need help in adding a macro to copy specific columns from one workbook to another. Just that part of the code where the copy and pasting happens. See image below. Thank you

 

[Legacy 143009]

What is the letter of date column in Sheet1?

 

[Legacy 143009]

I have one more question,

What happens if Sheet1 has 20 records for "13-May" and Sheet2 has 15 records for "13-May"?
Which ones must be chosen?

 

[josephmary]

Need help in adding a macro to copy specific columns from one workbook to another. Just that part of the code where the copy and pasting happens. See image below. Thank you

View attachment 101172

Column G

 

[josephmary]

I have one more question,

What happens if Sheet1 has 20 records for "13-May" and Sheet2 has 15 records for "13-May"?
Which ones must be chosen?

apologies i wasn't clear enough. i just want everything to be overwritten on sheet 2

 

[Legacy 143009]

This must work for you

Sub test()
  Dim columnG As Variant, columnOR As Variant, columnZ As Variant
  With Worksheets("Sheet1")
  columnG = Intersect(.UsedRange, .Range("G:G"))
  columnOR = Intersect(.UsedRange, .Range("O:R"))
  columnZ = Intersect(.UsedRange, .Range("Z:Z"))
  End With
  
  With Worksheets("Sheet2")
  Intersect(.UsedRange, .Range("C:I")).ClearContents
  .Range("C2").Resize(UBound(columnG, 1), 1).Value = columnG
  .Range("D2").Resize(UBound(columnOR, 1), UBound(columnOR, 2)).Value = columnOR
  .Range("I2").Resize(UBound(columnZ, 1), 1).Value = columnZ
  End With
End Sub

 

[josephmary]

Thank you. I just added some other codes as I decided to copy it from another workbook

 

[Legacy 143009]

Then something like this may work. But it needs both workbooks to be open:

Sub test()
  Dim columnG As Variant, columnOR As Variant, columnZ As Variant
  With Workbooks("SourceWorkbook.xlsx")
  .Active
  With .Worksheets("Sheet1")
  .Active
  columnG = Intersect(.UsedRange, .Range("G:G"))
  columnOR = Intersect(.UsedRange, .Range("O:R"))
  columnZ = Intersect(.UsedRange, .Range("Z:Z"))
  End With
  End With
 
  With Workbooks("DestinationWorkbook.xlsx")
  .Active
  With .Worksheets("Sheet1")
  Intersect(.UsedRange, .Range("C:I")).ClearContents
  .Range("C2").Resize(UBound(columnG, 1), 1).Value = columnG
  .Range("D2").Resize(UBound(columnOR, 1), UBound(columnOR, 2)).Value = columnOR
  .Range("I2").Resize(UBound(columnZ, 1), 1).Value = columnZ
  End With
  End With
End Sub

Please pay attention to file extensions. You have to specify either .xlsx or .xlsm

 

[josephmary]

Then something like this may work. But it needs both workbooks to be open:

Sub test()
  Dim columnG As Variant, columnOR As Variant, columnZ As Variant
  With Workbooks("SourceWorkbook.xlsx")
  .Active
  With .Worksheets("Sheet1")
  .Active
  columnG = Intersect(.UsedRange, .Range("G:G"))
  columnOR = Intersect(.UsedRange, .Range("O:R"))
  columnZ = Intersect(.UsedRange, .Range("Z:Z"))
  End With
  End With
 
  With Workbooks("DestinationWorkbook.xlsx")
  .Active
  With .Worksheets("Sheet1")
  Intersect(.UsedRange, .Range("C:I")).ClearContents
  .Range("C2").Resize(UBound(columnG, 1), 1).Value = columnG
  .Range("D2").Resize(UBound(columnOR, 1), UBound(columnOR, 2)).Value = columnOR
  .Range("I2").Resize(UBound(columnZ, 1), 1).Value = columnZ
  End With
  End With
End Sub

Please pay attention to file extensions. You have to specify either .xlsx or .xlsm

Thanks so much! appreciate it!

 

[Legacy 143009]

You are welcome