Copy specific columns from closed workbook to new workbook (location columns are not the same)

[Leeward904]

Hello all!

I need to write some VBA to copy specific columns from closed source book to open workbook. I have most of it completed i am stuck on how to add columns to copy and location to paste. I would like for the data in the destination workbook erase the data in the respective columns before pasting new data.

the copy column to paste column are
source column - destination column
B - E
H - H
I - I
J - J
L - K
N - O
O - G

Here is where i am right now:

Dim wrkMyWorkBook As Workbook
   
    Workbooks.Open Filename:=(Sheets("Data").Range("C3").Value & "\" & "TEC-037 Shop Work Request Log WCD.xlsx")
   
    'Find the last used row in both sheets
    Dim wsCopy As Worksheet
    Dim wsDest As Worksheet
    Dim lCopyLastRow As Long
    Dim lDestLastRow As Long
   
    'Set variables for copy and destination sheets
  Set wsCopy = Workbooks("TEC-037 Shop Work Request Log WCD.xlsx").Worksheets(1)
  Set wsDest = Workbooks("WC Fabrication Tracker Template 2021.xlsm").Worksheets(1)
   
  '1. Find last used row in the copy range based on data in column A
  lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
   
    '2. Find first blank row in the destination range based on data in column A
    'Offset property moves down 1 row
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
   
    '3. Clear contents of existing data range
    wsDest.Range("E2:E" & lDestLastRow).ClearContents

    '4. Copy & Paste Data
    wsCopy.Range("B2:B" & lCopyLastRow).Copy
      wsDest.Range("E2").PasteSpecial (xlPasteValues)
   
   
'Close a Workbook
    Workbooks("TEC-037 Shop Work Request Log WCD.xlsx").Close savechanges:=False

Any help would be greatly appreciated!

 

[johnnyL]

Confused.

From the title it sounds like you have a workbook that you want to use/grab values from a closed workbook and save those values to a new workbook.

From your Original Post here (OP) it sounds like you have a workbook that you want to use/grab values from a closed workbook and save those values to the current workbook.

So is it 2 or 3 workbooks, for clarification please.

 

[Leeward904]

Confused.

From the title it sounds like you have a workbook that you want to use/grab values from a closed workbook and save those values to a new workbook.

From your Original Post here (OP) it sounds like you have a workbook that you want to use/grab values from a closed workbook and save those values to the current workbook.

So is it 2 or 3 workbooks, for clarification please.

My apology in the title confusion. I want to grab data from designated columns in the sourcebook "TEC-037 Shop Work Request Log WCD" and paste values in the designated columns in the designation book "WC Fabrication Tracker Template 2021". Since my post i have expanded the code to copy each column individually from sourcebook and copy to specific columns in destination.

Expanded code:
Dim wrkMyWorkBook As Workbook

Workbooks.Open Filename:=(Sheets("Data").Range("C3").Value & "\" & "TEC-037 Shop Work Request Log WCD.xlsx")

'Find the last used row in both sheets
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

'Set variables for copy and destination sheets
Set wsCopy = Workbooks("TEC-037 Shop Work Request Log WCD.xlsx").Worksheets(1)
Set wsDest = Workbooks("WC Fabrication Tracker Template 2021.xlsm").Worksheets(1)

'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

'3. Clear contents of existing data range
wsDest.Range("E2:E" & lDestLastRow).ClearContents
wsDest.Range("H2:H" & lDestLastRow).ClearContents
wsDest.Range("I2:I" & lDestLastRow).ClearContents
wsDest.Range("J2:J" & lDestLastRow).ClearContents
wsDest.Range("K2:K" & lDestLastRow).ClearContents
wsDest.Range("O2:O" & lDestLastRow).ClearContents
wsDest.Range("G2:G" & lDestLastRow).ClearContents

'4. Copy & Paste Data
wsCopy.Range("B2:B" & lCopyLastRow).Copy
wsDest.Range("E2").PasteSpecial (xlPasteValues)
wsCopy.Range("H2:H" & lCopyLastRow).Copy
wsDest.Range("H2").PasteSpecial (xlPasteValues)
wsCopy.Range("I2:I" & lCopyLastRow).Copy
wsDest.Range("I2").PasteSpecial (xlPasteValues)
wsCopy.Range("J2:J" & lCopyLastRow).Copy
wsDest.Range("J2").PasteSpecial (xlPasteValues)
wsCopy.Range("L2:L" & lCopyLastRow).Copy
wsDest.Range("K2").PasteSpecial (xlPasteValues)
wsCopy.Range("N2:N" & lCopyLastRow).Copy
wsDest.Range("O2").PasteSpecial (xlPasteValues)
ws.Copy.Range("O2:O" & lCopyLastRow).Copy
wsDest.Range("G2").PasteSpecial (xlPasteValues)

'Close a Workbook
Workbooks("TEC-037 Shop Work Request Log WCD.xlsx").Close savechanges:=False

Now i am looking for a way to consolidate the code instead of having to list each column individually?

 

[johnnyL]

'3. Clear contents of existing data range
wsDest.Range("E2:E" & lDestLastRow, "G2:K" & lDestLastRow, "O2:O" & lDestLastRow).ClearContents

Do you need to use .PasteSpecial?

 

[johnnyL]

I ask because you can probably use the following instead:

'4. Copy & Paste Data
wsDest.Range("E2:E" & lCopyLastRow).Value = wsCopy.Range("B2:B" & lCopyLastRow).Value
wsDest.Range("H2:J" & lCopyLastRow).Value = wsCopy.Range("H2:J" & lCopyLastRow).Value
wsDest.Range("K2:K" & lCopyLastRow).Value = wsCopy.Range("L2:L" & lCopyLastRow).Value
wsDest.Range("O2:O" & lCopyLastRow).Value = wsCopy.Range("N2:N" & lCopyLastRow).Value
wsDest.Range("G2:G" & lCopyLastRow).Value = wsCopy.Range("O2:O" & lCopyLastRow).Value

 

[Leeward904]

perfect! i was using paste special because i was just looking to take the value instead of paste source formatting.
you are a saint!!!

thank you for your help.