I am very very new to VBA and have not been able to find a solution to the problem that I am running into.
I have written a macro to take the last line of data from a folder of workbooks and put them into an active workbook. But now I am trying to add in a user specificed workbook as the destination for this copied data.
'Need help setting the user input string as the name of the workbook
Dim inputData As Variant
Dim userBook As Workbook
Dim destBook As Workbook
'Get name of final destination Workbook
inputData = InputBox("Enter the name of the destination workbook")
'Set the user input as place holder workbook variable
Set userBook = inputData
'Set your master workbook as the placeholder workbook
Set destBook = userBook
I think I either 1) need a way to change the type that the variable inputData is storing originally but am unsure which type would work for my purposes, or 2) only run the macro in the destination workbook.
The whole macro is here as well in case there is some other interference that I missed when trying to make changes.
Sub Copying()
'
' Copying Macro
'
Dim sourceBook As Workbook
Dim sourceSheet As Worksheet
Dim destSheet As Worksheet
Dim CopyLastRow As Long
Dim fileName As String
Dim sheetName As String
Dim folderPath As String
Dim lastRow As Long
'Need help setting the user input string as the name of the workbook
Dim inputData As Variant
Dim userBook As Workbook
Dim destBook As Workbook
'Get name of final destination Workbook
inputData = InputBox("Enter the name of the destination workbook")
'Set the user input as place holder workbook variable
Set userBook = inputData
'Set your master workbook as the placeholder workbook
Set destBook = userBook
'Set the master "Data" worksheet
Set destSheet = destBook.Sheets("Sheet1")
'Specify the folder path where your source workbooks are located
folderPath = "C:\Users\USKAALV1\Documents\FlexLogger\data\"
'Get the file name from the folder
fileName = Dir(folderPath & "*.csv")
Do While fileName <> ""
'Get the sheet name from the file name
sheetName = Left(fileName, Len(fileName) - 4)
'Open the workbook
Set sourceBook = Workbooks.Open(folderPath & fileName)
'Set the source worksheet
Set sourceSheet = sourceBook.Sheets(sheetName)
'Find the last used row in the source worksheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
'Copy range from source worksheet
sourceSheet.Range("A" & lastRow & ":D" & lastRow).Copy
'Find the last row in the master "Data" sheet
lastRow = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row
'Paste into the master "Data" worksheet
destSheet.Cells(lastRow + 1, 1).PasteSpecial xlPasteValues
'Clear clipboard
Application.CutCopyMode = False
'Close the source workbook without saving
sourceBook.Close SaveChanges:=False
'Cycle to the next file in the folder
fileName = Dir
Loop
I assume the destination workbook is located in the same folder as ThisWorkbook (where the code is stored).
GetWK() is used to get the dest. workbook.
Option Explicit
Sub Copying()
Dim sourceBook As Workbook
Dim sourceSheet As Worksheet
Dim destSheet As Worksheet
Dim CopyLastRow As Long
Dim fileName As String
Dim sheetName As String
Dim folderPath As String
Dim lastRow As Long
'Need help setting the user input string as the name of the workbook
Dim inputData As String
Dim userBook As Workbook
Dim destBook As Workbook
'Get name of final destination Workbook
inputData = InputBox("Enter the name of the destination workbook")
If Len(inputData) = 0 Then
MsgBox "Please input the source file."
Exit Sub
End If
'Set your master workbook as the placeholder workbook
Set destBook = GetWK(inputData) ' ***
'Set the master "Data" worksheet
Set destSheet = destBook.Sheets("Sheet1")
'Specify the folder path where your source workbooks are located
folderPath = "C:\Users\USKAALV1\Documents\FlexLogger\data\"
'Get the file name from the folder
fileName = Dir(folderPath & "*.csv")
Do While fileName <> ""
'Get the sheet name from the file name
sheetName = Left(fileName, Len(fileName) - 4)
'Open the workbook
Set sourceBook = Workbooks.Open(folderPath & fileName)
'Set the source worksheet
Set sourceSheet = sourceBook.Sheets(sheetName)
'Find the last used row in the source worksheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
'Copy range from source worksheet
sourceSheet.Range("A" & lastRow & ":D" & lastRow).Copy
'Find the last row in the master "Data" sheet
lastRow = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row
If lastRow = 2 And Len(destSheet.Cells(1, 1)) = 0 Then lastRow = 1 ' ** blank sheet
'Paste into the master "Data" worksheet
destSheet.Cells(lastRow + 1, 1).PasteSpecial xlPasteValues
'Clear clipboard
Application.CutCopyMode = False
'Close the source workbook without saving
sourceBook.Close SaveChanges:=False
'Cycle to the next file in the folder
fileName = Dir
Loop
End Sub
Function GetWK(sWB As String) As Workbook
Dim wk As Workbook
Const EXT_NAME = ".xlsx" ' modify as needed
If LCase(Right(sWB, Len(EXT_NAME))) <> EXT_NAME Then
sWB = sWB & EXT_NAME ' add extension name
End If
' check if the dest. workbook is opened in Excel
For Each wk In Application.Workbooks
If UCase(wk.Name) = UCase(sWB) Then
Set GetWK = wk
Exit Function
End If
Next
If GetWK Is Nothing Then
Dim sFile As String: sFile = ThisWorkbook.Path & "\" & sWB
If Len(Dir(sFile)) = 0 Then
Set wk = Application.Workbooks.Add ' create a new Excel file
wk.SaveAs sFile
Else ' open existing file
Set wk = Application.Workbooks.Open(sFile)
End If
Set GetWK = wk
End If
End Function