I create inventory spreadsheets each day of the week. I have 3 productions
lines that each have a excel spreadsheet setup for their parts. The total
inventory from each line is copied into a master spreadsheet each day. The
individual prodution lines file name changes each day. Is there a way to
setup a macro to prompt for the file to pull information out of and then have
the info from the daily file copied into the master spreadsheet?

Thanks for any help!!

You can do this by writing VBA code.
If i understand your question correct a inputbox can do the work

"Louisville Cardinals" wrote:

I create inventory spreadsheets each day of the week. I have 3 productions
lines that each have a excel spreadsheet setup for their parts. The total
inventory from each line is copied into a master spreadsheet each day. The
individual prodution lines file name changes each day. Is there a way to
setup a macro to prompt for the file to pull information out of and then have
the info from the daily file copied into the master spreadsheet?

Thanks for any help!!

What would that code look like?

"MrT" wrote:

You can do this by writing VBA code.
If i understand your question correct a inputbox can do the work

"Louisville Cardinals" wrote:

I create inventory spreadsheets each day of the week. I have 3 productions
lines that each have a excel spreadsheet setup for their parts. The total
inventory from each line is copied into a master spreadsheet each day. The
individual prodution lines file name changes each day. Is there a way to
setup a macro to prompt for the file to pull information out of and then have
the info from the daily file copied into the master spreadsheet?

Thanks for any help!!

Plus I want the user to be able to select the file rather than having to type
it in.

"MrT" wrote:

You can do this by writing VBA code.
If i understand your question correct a inputbox can do the work

"Louisville Cardinals" wrote:

I create inventory spreadsheets each day of the week. I have 3 productions
lines that each have a excel spreadsheet setup for their parts. The total
inventory from each line is copied into a master spreadsheet each day. The
individual prodution lines file name changes each day. Is there a way to
setup a macro to prompt for the file to pull information out of and then have
the info from the daily file copied into the master spreadsheet?

Thanks for any help!!

Maybe something like:

Option Explicit
Sub testme()

Dim myFileName As Variant
Dim Wkbk As Workbook
Dim DestCell As Range

myFileName = Application.GetOpenFilename("Excel Files, *.xls")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Set Wkbk = Workbooks.Open(Filename:=myFileName)

With ThisWorkbook.Worksheets("Master")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Wkbk.Worksheets("sheet1").Range("A1:E1").Copy _
Destination:=DestCell

Wkbk.Close savechanges:=False

End Sub



Louisville Cardinals wrote:

What would that code look like?

"MrT" wrote:

You can do this by writing VBA code.
If i understand your question correct a inputbox can do the work

"Louisville Cardinals" wrote:

I create inventory spreadsheets each day of the week. I have 3 productions
lines that each have a excel spreadsheet setup for their parts. The total
inventory from each line is copied into a master spreadsheet each day. The
individual prodution lines file name changes each day. Is there a way to
setup a macro to prompt for the file to pull information out of and then have
the info from the daily file copied into the master spreadsheet?

Thanks for any help!!

--

Dave Peterson

You are the Man!!! Thanks for your help

"Dave Peterson" wrote:

Maybe something like:

Option Explicit
Sub testme()

Dim myFileName As Variant
Dim Wkbk As Workbook
Dim DestCell As Range

myFileName = Application.GetOpenFilename("Excel Files, *.xls")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Set Wkbk = Workbooks.Open(Filename:=myFileName)

With ThisWorkbook.Worksheets("Master")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Wkbk.Worksheets("sheet1").Range("A1:E1").Copy _
Destination:=DestCell

Wkbk.Close savechanges:=False

End Sub



Louisville Cardinals wrote:

What would that code look like?

"MrT" wrote:

You can do this by writing VBA code.
If i understand your question correct a inputbox can do the work

"Louisville Cardinals" wrote:

I create inventory spreadsheets each day of the week. I have 3 productions
lines that each have a excel spreadsheet setup for their parts. The total
inventory from each line is copied into a master spreadsheet each day. The
individual prodution lines file name changes each day. Is there a way to
setup a macro to prompt for the file to pull information out of and then have
the info from the daily file copied into the master spreadsheet?

Thanks for any help!!

--

Dave Peterson

Ok. Here is a new twist. I need to be able to copy the info from the daily
spreadsheets into the spreadsheet that activated the macro. I have the
following code so far but I am stuck. When I click on the command button I
am prompted for the file to pull the information out of but I need to switch
back to the file with the command button and paste the info that was copied.


NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),
*.xls", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
Worksheets("sheet3").Range("O4:O22").COPY

End If
End Sub



Thanks for your help....... again

I'd use something like:

Option Explicit
Sub CommandButton1_Click()

Dim myFileName As Variant
Dim Wkbk As Workbook
Dim DestCell As Range

Set ActSheet = activesheet

myFileName = Application.GetOpenFilename("Excel Files, *.xls")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Set Wkbk = Workbooks.Open(Filename:=myFileName)

With Me
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Wkbk.Worksheets("sheet3").Range("o4:o22").Copy _
Destination:=DestCell

Wkbk.Close savechanges:=False

End Sub

By opening, copying, then closing, you should be back where you started.

And I'm assuming you used a commandbutton from the control toolbar toolbox.
(The Me keyword refers to the sheet that owns that commandbutton.

DestCell is where the copied range is pasted. I used the first open cell in
column A (working from the bottom up.)

But you could set that destcell to anything you want.

Louisville Cardinals wrote:

Ok. Here is a new twist. I need to be able to copy the info from the daily
spreadsheets into the spreadsheet that activated the macro. I have the
following code so far but I am stuck. When I click on the command button I
am prompted for the file to pull the information out of but I need to switch
back to the file with the command button and paste the info that was copied.

NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),
*.xls", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
Worksheets("sheet3").Range("O4:O22").COPY

End If
End Sub

Thanks for your help....... again

--

Dave Peterson

I have tried this macro but with no luck. The macro does promt me as to the
file I want to open and copy but nothing happens after that. I have even
tried to manually past the information but it does not appear that anytihing
has been copied.(Paste or Paste Special is not an opton when I right click).
I tried to input cell "I5" in place of the "A" in the following line but
still nothing:

Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Thanks for all your help!!!

"Dave Peterson" wrote:

I'd use something like:

Option Explicit
Sub CommandButton1_Click()

Dim myFileName As Variant
Dim Wkbk As Workbook
Dim DestCell As Range

Set ActSheet = activesheet

myFileName = Application.GetOpenFilename("Excel Files, *.xls")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Set Wkbk = Workbooks.Open(Filename:=myFileName)

With Me
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Wkbk.Worksheets("sheet3").Range("o4:o22").Copy _
Destination:=DestCell

Wkbk.Close savechanges:=False

End Sub

By opening, copying, then closing, you should be back where you started.

And I'm assuming you used a commandbutton from the control toolbar toolbox.
(The Me keyword refers to the sheet that owns that commandbutton.

DestCell is where the copied range is pasted. I used the first open cell in
column A (working from the bottom up.)

But you could set that destcell to anything you want.

Louisville Cardinals wrote:

Ok. Here is a new twist. I need to be able to copy the info from the daily
spreadsheets into the spreadsheet that activated the macro. I have the
following code so far but I am stuck. When I click on the command button I
am prompted for the file to pull the information out of but I need to switch
back to the file with the command button and paste the info that was copied.

NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),
*.xls", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
Worksheets("sheet3").Range("O4:O22").COPY

End If
End Sub

Thanks for your help....... again

--

Dave Peterson

Figured out the macro so previous post can be ignored. How ever my new
problem is that the information that is being copied is the result of a
formula; therefore I get
the #REF! error. I need to just paste the values. What line do I need to
add or edit inorder to just paste the values? Once again thanks for all your
help!!
"Dave Peterson" wrote:

I'd use something like:

Option Explicit
Sub CommandButton1_Click()

Dim myFileName As Variant
Dim Wkbk As Workbook
Dim DestCell As Range

Set ActSheet = activesheet

myFileName = Application.GetOpenFilename("Excel Files, *.xls")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Set Wkbk = Workbooks.Open(Filename:=myFileName)

With Me
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Wkbk.Worksheets("sheet3").Range("o4:o22").Copy _
Destination:=DestCell

Wkbk.Close savechanges:=False

End Sub

By opening, copying, then closing, you should be back where you started.

And I'm assuming you used a commandbutton from the control toolbar toolbox.
(The Me keyword refers to the sheet that owns that commandbutton.

DestCell is where the copied range is pasted. I used the first open cell in
column A (working from the bottom up.)

But you could set that destcell to anything you want.

Louisville Cardinals wrote:

Ok. Here is a new twist. I need to be able to copy the info from the daily
spreadsheets into the spreadsheet that activated the macro. I have the
following code so far but I am stuck. When I click on the command button I
am prompted for the file to pull the information out of but I need to switch
back to the file with the command button and paste the info that was copied.

NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),
*.xls", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
Worksheets("sheet3").Range("O4:O22").COPY

End If
End Sub

Thanks for your help....... again

--

Dave Peterson

Wkbk.Worksheets("sheet3").Range("o4:o22").Copy _
Destination:=DestCell

Could become:
Wkbk.Worksheets("sheet3").Range("o4:o22").Copy
destcell.pastespecial paste:=xlpastevalues


Louisville Cardinals wrote:

Figured out the macro so previous post can be ignored. How ever my new
problem is that the information that is being copied is the result of a
formula; therefore I get
the #REF! error. I need to just paste the values. What line do I need to
add or edit inorder to just paste the values? Once again thanks for all your
help!!
"Dave Peterson" wrote:

I'd use something like:

Option Explicit
Sub CommandButton1_Click()

Dim myFileName As Variant
Dim Wkbk As Workbook
Dim DestCell As Range

Set ActSheet = activesheet

myFileName = Application.GetOpenFilename("Excel Files, *.xls")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Set Wkbk = Workbooks.Open(Filename:=myFileName)

With Me
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Wkbk.Worksheets("sheet3").Range("o4:o22").Copy _
Destination:=DestCell

Wkbk.Close savechanges:=False

End Sub

By opening, copying, then closing, you should be back where you started.

And I'm assuming you used a commandbutton from the control toolbar toolbox.
(The Me keyword refers to the sheet that owns that commandbutton.

DestCell is where the copied range is pasted. I used the first open cell in
column A (working from the bottom up.)

But you could set that destcell to anything you want.

Louisville Cardinals wrote:

Ok. Here is a new twist. I need to be able to copy the info from the daily
spreadsheets into the spreadsheet that activated the macro. I have the
following code so far but I am stuck. When I click on the command button I
am prompted for the file to pull the information out of but I need to switch
back to the file with the command button and paste the info that was copied.

NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),
*.xls", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
Worksheets("sheet3").Range("O4:O22").COPY

End If
End Sub

Thanks for your help....... again

--

Dave Peterson


--

Dave Peterson

THANKS A BUNCH!!!!!! Spreadsheet is doing everything I was hoping it could do.

"Dave Peterson" wrote:

Wkbk.Worksheets("sheet3").Range("o4:o22").Copy _
Destination:=DestCell

Could become:
Wkbk.Worksheets("sheet3").Range("o4:o22").Copy
destcell.pastespecial paste:=xlpastevalues


Louisville Cardinals wrote:

Figured out the macro so previous post can be ignored. How ever my new
problem is that the information that is being copied is the result of a
formula; therefore I get
the #REF! error. I need to just paste the values. What line do I need to
add or edit inorder to just paste the values? Once again thanks for all your
help!!
"Dave Peterson" wrote:

I'd use something like:

Option Explicit
Sub CommandButton1_Click()

Dim myFileName As Variant
Dim Wkbk As Workbook
Dim DestCell As Range

Set ActSheet = activesheet

myFileName = Application.GetOpenFilename("Excel Files, *.xls")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Set Wkbk = Workbooks.Open(Filename:=myFileName)

With Me
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Wkbk.Worksheets("sheet3").Range("o4:o22").Copy _
Destination:=DestCell

Wkbk.Close savechanges:=False

End Sub

By opening, copying, then closing, you should be back where you started.

And I'm assuming you used a commandbutton from the control toolbar toolbox.
(The Me keyword refers to the sheet that owns that commandbutton.

DestCell is where the copied range is pasted. I used the first open cell in
column A (working from the bottom up.)

But you could set that destcell to anything you want.

Louisville Cardinals wrote:

Ok. Here is a new twist. I need to be able to copy the info from the daily
spreadsheets into the spreadsheet that activated the macro. I have the
following code so far but I am stuck. When I click on the command button I
am prompted for the file to pull the information out of but I need to switch
back to the file with the command button and paste the info that was copied.

NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),
*.xls", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
Worksheets("sheet3").Range("O4:O22").COPY

End If
End Sub

Thanks for your help....... again

--

Dave Peterson


--

Dave Peterson