Category Archives: Excel VBA

VBA – How to copy row in Excel from one workbook to another?

Task expected:

I have a workbook with worksheets. For simplicity, let’s say my workbook has a worksheet. And in my worksheet which is called “Sheet1”, there is data in cells A1 to A4.

What I want my VBA code to do is:

  1. Copy row 1 (or specifically cells A1 to A4) of Workbook ‘A’ into Range variable ‘myRange’
  2. Create a new workbook, let’s call this one Workbook ‘B’
  3. Give Workbook ‘B’s default “sheet1” a new name to “Test Name”
  4. Select Workbook ‘B’
  5. Paste ‘myRange’ into first row of ‘Workbook B’
  6. Save ‘Workbook B’ with name “Test Book” and a timestamp enclosed in square brackets. The file must also be of the file extension “xls”
  7. Close ‘Workbook B’ and return to ‘Workbook A’

Do you understand what the code trying to do?

Sub Test()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim i As Integer
i = 6

Dim SpecifiedDate As String
SpecifiedDate = Cells(4, ActiveCell.Column)
SpecifiedDate = Format(SpecifiedDate, "mm/dd/yyyy")
SpecifiedDate = Replace(SpecifiedDate, "/", "-")

Dim TWB As Workbook
Set TWB = ThisWorkbook

Col = ActiveCell.Column

'CFSPPSUM Data Transfer

Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFSPPSUM - " & SpecifiedDate & ".xls")

While TWB.Worksheets("Import-Data").Cells(i, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(i, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(i, 3).Value, _
Workbooks("CFSPPSUM - " & SpecifiedDate & ".xls").Worksheets("Output Data Sheet").Range("$E$9:$AA$100"), 23, False)

i = i + 1
Wend
Cells(6, ActiveCell.Column).Select

Windows("CFSPPSUM - " & SpecifiedDate & ".xls").Close (False)

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub
Sub Test_Final_Build()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If ActiveCell.Row = 6 Then

Dim SpecifiedDate As String
SpecifiedDate = Cells(4, ActiveCell.Column)
SpecifiedDate = Format(SpecifiedDate, "mm/dd/yyyy")
SpecifiedDate = Replace(SpecifiedDate, "/", "-")

Dim TWB As Workbook
Set TWB = ThisWorkbook

Col = ActiveCell.Column

Dim NSPLoc As String, NSPL As Integer
NSPLoc = Application.WorksheetFunction.Match("NSP SUM", Range("B1:B200"), 0)
NSPL = NSPLoc
Dim D831020Loc As String, D831020L As Integer
D831020Loc = Application.WorksheetFunction.Match("Dept. 831020", Range("B1:B200"), 0)
D831020L = D831020Loc
Dim SPPISUMLoc As String, SPPISUML As Integer, NSPISUMLoc As String, NSPISUML As Integer
SPPISUMLoc = Application.WorksheetFunction.Match("SPP I-SUM", Range("C1:C200"), 0)
SPPISUML = SPPISUMLoc
NSPISUMLoc = Application.WorksheetFunction.Match("NSP I-SUM", Range("C1:C200"), 0)
NSPISUML = NSPISUMLoc

Dim i As Integer, j As Integer, k As Integer, l As Integer, m As Integer, n As Integer, o As Integer, p As Integer, q As Integer, r As Integer, s As Integer, t As Integer, u As Integer
i = 6   'SPP Sum Location
j = 6   'NSP Sum Location
k = 6
l = 6
m = 6
n = 6
o = 6
p = 6
q = 6
r = 6
s = 6
t = 6
u = 6


    'CFNSPSUM Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFNSPSUM - " & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(j, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(j, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(j, 3).Value, _
    Workbooks("CFNSPSUM - " & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$11:$BI$150"), 57, False)
    j = j + 1
Wend
    Windows("CFNSPSUM - " & SpecifiedDate & ".xls").Activate     'Sum Total Check Figure Import
        Sheets("Sheet1").Select
            Range("BI106").Copy
    TWB.Sheets("Import-Data").Activate
        Cells(NSPISUML, ActiveCell.Column).PasteSpecial xlPasteValues
Windows("CFNSPSUM - " & SpecifiedDate & ".xls").Close (False)


    'CFSPPSUM Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFSPPSUM - " & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(i, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(i, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(i, 3).Value, _
    Workbooks("CFSPPSUM - " & SpecifiedDate & ".xls").Worksheets("Output Data Sheet").Range("$E$9:$AA$100"), 23, False)
    i = i + 1
Wend
Windows("CFSPPSUM - " & SpecifiedDate & ".xls").Activate        'Sum Total Check figure import
    Sheets("Output Data Sheet").Select
        Range("AA80").Copy
    TWB.Sheets("Import-Data").Activate
        Cells(SPPISUML, ActiveCell.Column).PasteSpecial xlPasteValues
Windows("CFSPPSUM - " & SpecifiedDate & ".xls").Close (False)


    'Dept. 831020 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "Dept. 831020 - " & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(k, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(k, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(k, 3).Value, _
    Workbooks("Dept. 831020 - " & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$10:$AY$100"), 47, False)
    k = k + 1
Wend
Windows("Dept. 831020 - " & SpecifiedDate & ".xls").Close (False)


    'Dept. 831021 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "Dept. 831021 - " & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(l, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(l, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(l, 3).Value, _
    Workbooks("Dept. 831021 - " & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$10:$AY$100"), 47, False)
    l = l + 1
Wend
Windows("Dept. 831021 - " & SpecifiedDate & ".xls").Close (False)


    'Dept. 831022 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "Dept. 831022 - " & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(m, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(m, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(m, 3).Value, _
    Workbooks("Dept. 831022 - " & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$10:$AY$100"), 47, False)
    m = m + 1
Wend
Windows("Dept. 831022 - " & SpecifiedDate & ".xls").Close (False)


    'Dept. 831023 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "Dept. 831023 - " & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(n, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(n, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(n, 3).Value, _
    Workbooks("Dept. 831023 - " & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$10:$AY$100"), 47, False)
    n = n + 1
Wend
Windows("Dept. 831023 - " & SpecifiedDate & ".xls").Close (False)


    'CFNSPS01 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFNSPS01 - PROVOST BAYH-DOLE RESTRICTED S" & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(o, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(o, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(o, 3).Value, _
    Workbooks("CFNSPS01 - PROVOST BAYH-DOLE RESTRICTED S" & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$11:$BI$200"), 57, False)
    o = o + 1
Wend
Windows("CFNSPS01 - PROVOST BAYH-DOLE RESTRICTED S" & SpecifiedDate & ".xls").Close (False)


    'CFNSPS02 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS     Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFNSPS02 - N16 SCIENCE HIRE INITIATIVE" & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(p, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(p, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(p, 3).Value, _
    Workbooks("CFNSPS02 - N16 SCIENCE HIRE INITIATIVE" & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$11:$BI$200"), 57, False)
    p = p + 1
Wend
Windows("CFNSPS02 - N16 SCIENCE HIRE INITIATIVE" & SpecifiedDate & ".xls").Close (False)


    'CFNSPS03 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFNSPS03 - MICHELANGELO GRIGNI START-UP F" & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(q, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(q, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(q, 3).Value, _
    Workbooks("CFNSPS03 - MICHELANGELO GRIGNI START-UP F" & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$11:$BI$200"), 57, False)
    q = q + 1
Wend
Windows("CFNSPS03 - MICHELANGELO GRIGNI START-UP F" & SpecifiedDate & ".xls").Close (False)


    'CFNSPS04 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFNSPS04 - CLS J Taylor" & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(r, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(r, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(r, 3).Value, _
    Workbooks("CFNSPS04 - CLS J Taylor" & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$11:$BI$200"), 57, False)
    r = r + 1
Wend
Windows("CFNSPS04 - CLS J Taylor" & SpecifiedDate & ".xls").Close (False)


    'CFNSPS05 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFNSPS05 - Equipment - Taylor" & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(s, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(s, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(s, 3).Value, _
    Workbooks("CFNSPS05 - Equipment - Taylor" & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$11:$BI$200"), 57, False)
    s = s + 1
Wend
Windows("CFNSPS05 - Equipment - Taylor" & SpecifiedDate & ".xls").Close (False)


    'CFNSPS06 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFNSPS06 - SOM Equipment" & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(t, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(t, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(t, 3).Value, _
    Workbooks("CFNSPS06 - SOM Equipment" & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$11:$BI$200"), 57, False)
    t = t + 1
Wend
Windows("CFNSPS06 - SOM Equipment" & SpecifiedDate & ".xls").Close (False)


    'CFNSPS07 Data Transfer
Application.Workbooks.Open ("C:\Users\Chris Treeman\My Documents\Dept MathCS Financials\Accounting Files\Dept. Fund Tracking\Daily Compass Report Files\" & "CFNSPS07 - JACKSON MATH AND SCIENCE FUND" & SpecifiedDate & ".xls")
While TWB.Worksheets("Import-Data").Cells(u, 3).Value <> "END"
On Error Resume Next
    TWB.Worksheets("Import-Data").Cells(u, Col) = Application.WorksheetFunction.VLookup(TWB.Worksheets("Import-Data").Cells(u, 3).Value, _
    Workbooks("CFNSPS07 - JACKSON MATH AND SCIENCE FUND" & SpecifiedDate & ".xls").Worksheets("Sheet1").Range("$E$11:$BI$200"), 57, False)
    u = u + 1
Wend
Windows("CFNSPS07 - JACKSON MATH AND SCIENCE FUND" & SpecifiedDate & ".xls").Close (False)

TWB.Sheets("Import-Data").Activate
Cells(6, CLoc).Select

TWB.Sheets("Dept. NSPs & SPPs").Activate
Application.Calculation = xlCalculationAutomatic
Columns(ActiveCell.Column).AutoFit

Else
    MsgBox ("Please select the correct cell for Import")
End If

Application.ScreenUpdating = True

End Sub

https://stackoverflow.com/questions/35135116/vba-excel-to-mac-file-path?rq=1