Macro to Copy Column, Open Existing Document, Paste & Save?

[Closed]
Report
Posts
6
Registration date
Wednesday April 8, 2015
Status
Member
Last seen
April 15, 2015
-
Posts
6
Registration date
Wednesday April 8, 2015
Status
Member
Last seen
April 15, 2015
-
Hello,

First of all, I'm essentially a VB novice - I've copied and applied a few very basic scripts, but nothing of this magnitude, so any and all help is greatly appreciated. If I am unclear or don't give enough information, please let me know. Thank you so much for being willing to help.

Here we go.

I am a wholesaler and we have many retail customers. More and more, our customers are requiring us to send them a file on a daily basis that informs them of the we have on hand of only the items we sell them. I am able to download an inventory file from our warehouse. Each day, when I download the file, I save it as "today.xlsx". I have a second file, that I call "inventory template" that I then open once the appropriate "today" file has been downloaded and opened. The "inventory template" is laid out something like this:
A B C(blank) D E F(blank) G H
Customer 1 Customer 2 Customer 3
Item 1 Qty Item 2 Qty Item 3 Qty
Item 3 Qty Item 1 Qty Item 4 Qty

And so on. The 'inventory template' file uses vlookups to pull the appropriate quantity for each of the items per customer. Now, we're using a system that will allow us to upload a file to transmit the quantities to each customer. The file must be a very specific format, and contains more information that item number and quantity. So, I'd need the macro to open a file, let's say "Customer 1.xlsx", Copy the items in Column A from A2 down until the end into Column F of "Customer 1" and then Copy B2 down into Column I of "Customer 1. Same thing for a file called "Customer 2" and columns D & E, Customer 3, columns G&H.

If it matters, the Customer 1, Customer 2 and Customer 3 Files will contain "Today" formulas to insert the current date in each row.

In a perfect world, the macro would then save the file "Customer 1" with the new data in it as "Customer1-mmddyyyy.csv", and so on with Customer 2, Customer 3. Is this even possible?

I can't imagine how complicated this is and truly appreciate any and all feedback I can get. Thank you in advance so much for your time.



2 replies

Posts
341
Registration date
Tuesday March 3, 2015
Status
Moderator
Last seen
July 3, 2015
68
you'll be amazed to know how much you can do with VB, but to be honest I almost fell asleep reading the explanation,
what I suggest is that you make a very light version of your excel file or even better you can make a small example for us with a fake customer and sales files, upload it to some sharing host, post it here, and then I can view it and start a combo about it with you.
Good luck.
Posts
6
Registration date
Wednesday April 8, 2015
Status
Member
Last seen
April 15, 2015

Thank you so much for replying. Sorry to bore you, I was trying to be thorough. Perhaps this is more clear, thanks for the suggestion. 5 files here:

https://drive.google.com/drive/folders/0B9RdeR30sXv1SF8yLXJ5cnBMeDQ?usp=sharing

"Today.xlsx" is a file that I would download from my warehouse's website. From there, in my perfect world, I'd start a macro that would:

1. Find and replace all spaces in column A with nothing (delete spaces).
2. Open the files "Inventory Template.xlsx", "Customer1Template.csv", "Customer2Template.csv" and "Customer3Template.csv"
3. In "Inventory Template", Copy Column C, and paste values in Column N of "Customer1Template", In "customer1template", select all, copy, paste values. Save file as "Customer1mmddyy.csv"
4. Repeat Step 3 for Column F of "Inventory template" into Column N of "Customer2Template" and Column I "Inventory Template" into "Customer3Template".
5. If possible, close all the files

All files (templates and outputs) can be in the same directory. I:Ideas\\Ecommerce\Inventories
Posts
6
Registration date
Wednesday April 8, 2015
Status
Member
Last seen
April 15, 2015

I got an email showing your reply (thank you), but it's not posted here for some reason. "Today.xlsx" is a file I download directly from my warehouse that shows ALL inventory at the warehouse, regardless of which customer buys it. Many customers can buy the same item.

Column A - Item Number
Column B - Item Description
Column C - Warehouse Location
Column D - Quantity

The only columns that I need to use are A&D, the rest just come with the report.
Posts
341
Registration date
Tuesday March 3, 2015
Status
Moderator
Last seen
July 3, 2015
68
I'm working on something that will probably sounds like I'm doing your homework !!!
but don't worry I like working out my little brain with some VB
OK,
I will be providing a full tutorial on how to transfer data between workbooks using a friendly User Interface, which we will build and populate it's contents accordingly.
this sounds like fun but it needs a day or two cause I just have a bit more free time.
here's a shot from the primitive UI populated with some data.
Posts
6
Registration date
Wednesday April 8, 2015
Status
Member
Last seen
April 15, 2015

sorry I just saw this. that's so awesome that you're taking free time for a stranger to figure this out. Thank you so much!
Posts
341
Registration date
Tuesday March 3, 2015
Status
Moderator
Last seen
July 3, 2015
68
Hello,
time to get some work done for you.
what I will provide now is basically all the major coding and planning for this situation, but you HAVE to adapt it your needs.
this will grab data from Today workbook, insert it as wanted in the inventory workbook, put it in the customer workbook, save as customer-mm-dd-yyyy.csv.
OK, let's get started,
the main thing is that you need to have the files in the same folder in-order for this to work.
  • the inventory file will be an empty workbook and will be filled as needed by you, all the codes and user UI will be stored in it, so it's going to be an ".xlsm" file.

The user UI will look like this

download these two files: UserForm1.frm, UserForm1.frx from here and put them in the same folder then open inventory workbook, press Alt+F11 to open Visual Basic Editor and follow the pictures to import the user form to your workbook:
right click and choose as shown:

import the UI


now you need to insert a module that will contain the macro to show the UI:

the code to show the UI:
Sub ShowMe()
UserForm1.show
End Sub

Add customer items to ComboBox1, and Add items to the List box from Today.xlsx when UserForm1 initialize

Option Explicit
Private Sub UserForm_Initialize()
'Add customer items to ComboBox1
    Dim m As Long
    m = 1
    With ComboBox1
    .Clear
    Do While Cells(1, m).Value <> ""
    .AddItem (Sheet1.Cells(1, m).Value)
    m = m + 3
    Loop
    End With
'=========================================================
' 1- Open the source workbook as ReadOnly
' 2- extract the needed data
' 3- close the source workbook without making any changes
' 4- populate the extracted data into the listbox.
'=========================================================
Dim ListItemsR As Variant, i As Integer, ListItemsC As Variant
Dim SourceWB As Workbook
Dim j As Long
j = 1
    With ListBox1
        .Clear
        Application.ScreenUpdating = False
        Set SourceWB = Workbooks.Open(ThisWorkbook.Path & "\Today.xlsx", False, True)
        Do While SourceWB.Worksheets(1).Cells(j, 1).Value <> ""
        j = j + 1
        Loop
        ListItemsR = SourceWB.Worksheets(1).Range(Cells(1, 1), Cells(j - 1, 1)).Value
        ListItemsC = SourceWB.Worksheets(1).Range(Cells(1, 4), Cells(j - 1, 4)).Value
        SourceWB.Close False
        Set SourceWB = Nothing
        ListItemsR = Application.WorksheetFunction.Transpose(ListItemsR)
        ListItemsC = Application.WorksheetFunction.Transpose(ListItemsC)
        For i = 1 To UBound(ListItemsR)
            .AddItem ListItemsR(i)
            .List(i - 1, 1) = ListItemsC(i)
        Next i
        Application.ScreenUpdating = True
    End With
End Sub

Inserting selected items in Inventory workbook from the list box

Private Sub Go_BTN_Click()
Range("b:b").Value = ""
Range("c:c").Value = ""
      Dim i, j, m, d As Integer
      i = 0
      j = 1
      m = 1
Do While Cells(1, m + 3).Value <> ""
If Cells(1, m).Value = ComboBox1.Value Then
more:
      Do
        With ListBox1
        If ListBox1.Selected(i) Then
              Cells(j, m + 1).Value = .List(i, 0)
              Cells(j, m + 2).Value = .List(i, 1)
              j = j + 1
          End If
        End With
      i = i + 1
      Loop Until i = ListBox1.ListCount
      ComboBox1.Value = ""
      ListBox1.MultiSelect = fmMultiSelectSingle
      ListBox1.MultiSelect = fmMultiSelectMulti
      Exit Sub
End If
m = m + 3
Loop
GoTo more
End Sub

exporting data

Private Sub Export_BTN_Click()
 '========================================================================
    Dim SRS As Workbook, CSTMR As Workbook, FilePath As String          '=
    FilePath = ThisWorkbook.Path & "\Customer1Template.csv"             '=
    Set SRS = Workbooks("InventoryTemplate.xlsm")                       '= define Workbooks
    Workbooks.Open (FilePath)                                           '= open Customer1Template
    Set CSTMR = Workbooks("Customer1Template.csv")                      '= minimize it
    CSTMR.Windows.Application.WindowState = xlMinimized                 '=
 '========================================================================
    Dim j As Integer                                                    '=
    j = 1                                                               '=
    Do Until CSTMR.Sheets("Customer1Template").Cells(j, 11).Value = ""  '= define search range in
    j = j + 1                                                           '= Customer1Template.csv
    Loop                                                                '=
    j = j - 1                                                           '=
 '========================================================================
 Dim i As Integer, Fitch As Range, SRCHrng As Range, Cell As Range
 i = 1
 Application.ScreenUpdating = False
 Do Until SRS.Sheets("sheet1").Cells(i, 2).Value = ""
    Set Fitch = SRS.Sheets("sheet1").Cells(i, 2)
    Set SRCHrng = CSTMR.Sheets("Customer1Template").Range(Cells(1, 11).Address, Cells(j, 11).Address)
    Set Cell = SRCHrng.Find(Fitch)
 If Not Cell Is Nothing Then
  CSTMR.Sheets("Customer1Template").Cells(Cell.Row, Cell.Column + 3).Value _
    = SRS.Sheets("sheet1").Cells(i, 3).Value
 End If
 i = i + 1
 Loop
 Application.DisplayAlerts = False
 With CSTMR
 .SaveAs ThisWorkbook.Path & "\Customer1Template-" & Format(Date, "mm-dd-yyyy") & ".csv"
 .Close
 End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Other code are self-explained

Private Sub Clear_BTN_Click()
    ListBox1.MultiSelect = fmMultiSelectSingle
    ListBox1.MultiSelect = fmMultiSelectMulti
End Sub

Private Sub CLRdata_Click()
Range("b:b").Value = ""
End Sub

Private Sub ComboBox1_Change()

    If ComboBox1.Value = "" Then
        Go_BTN.Enabled = False
    Else
        Go_BTN.Enabled = True
    End If
    
End Sub

Private Sub SelectAll_Btn_Click()
Dim r As Integer
For r = 0 To ListBox1.ListCount - 1
        ListBox1.Selected(r) = True
    Next r
End Sub
Posts
6
Registration date
Wednesday April 8, 2015
Status
Member
Last seen
April 15, 2015

OH my gosh! Thank you so much! I cant imagine how much work went into that!

I've obviously not yet put it together yet, but I will let you know how it goes.

Thank you thank you thank you.