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

Leeward904

New Member
Joined
May 5, 2021
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
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:
VBA 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

    '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!
 
Last edited by a moderator:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,295
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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

New Member
Joined
May 5, 2021
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Nov 7, 2011
Messages
1,295
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
VBA Code:
'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

Well-known Member
Joined
Nov 7, 2011
Messages
1,295
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
I ask because you can probably use the following instead:

VBA Code:
'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
 
Solution

Leeward904

New Member
Joined
May 5, 2021
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,140,926
Messages
5,703,215
Members
421,282
Latest member
hogie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top