Copy multiple columns into separate new workbooks using VBA

Question

I've been able to code a macro that does the following:

copy column A into Column B with format "mm-dd-yyyy".
copy column A into Column C with format "h:mm".

Now, I need to do the following, but I don't know how...

Copy the newly created columns B,C, and each individual column to the right of the new columns into their own individual workbooks. I would like the workbooks to be named based on "current sheet title" and each individual row 2 name. As an example I have attached an image of the workbook data and what 1 sheets final product would look like.

I think the code would have to do the following: Create a range based column variable X and row 2. Then state that for each column X, if row 2 contains a value then copy column B, column C, and the current column X. Then paste the (3) columns (B,C, & Column Variable X) into a new workbook and name the workbook based on the "current sheet title + the cell name of current column X, row 2".

I appreciate anybody's help, please let me know if what I'm saying is confusing and I will try and clear it up.

Images:

Data: Data Image

Final Product 1: Final Product Image 1

Final Product 2:Final Product Image 2

Current Macro:

Sub Time_Series()

Dim CurrentSheet As Worksheet

 With CurrentSheet
      Range("A:A").Copy
      Range("B:C").Insert
      Range("B:B").NumberFormat = "mm-dd-yyyy;@"
      Range("C:C").NumberFormat = "h:MM;@"

 End With

End Sub

just an FYI formatting the whole column takes up a lot of file size I think, so you may want to define more specifically the range you want to set the format of e.g. Range(Range("B2"), Range("B2").End(xlDown)).NumberFormat = "mm-dd-yyyy;@", also formatting wont remove the unused date part so if ever you sort/group etc what you are seeing through the for at mask is only part of the actual cell contents. — JohnnieL
In approaching a solution to the problem, your logic seems solid, try to implement that. set ws = Thisworkbook.worksheets.add will create a new workbook for you and using logic like Range(Range("B2"), Range("B2").End(xlDown)).copy destination:= ws.range("a1") will effect the copy — JohnnieL
Is impossible for me to understand because what you are explaining does not match what the images show. Just one example, you talk about copy/paste the dates of column A into column B, but in the images I see you pasted them in column A in another worksheet. Do you know the difference between worksheet and workbook? .. Try to be careful in your explanation, every word matters if you want us to understand. Simplify and get all the bullshit out. — Gass
Just wanted to start by saying I appreciate everyone's time. Ideally I would like to be able to paste the data into a new workbook, not worksheet, because I'll be importing the individual workbooks into another program later. The first two steps create columns B & C. The goal is to then copy Columns B,C & Variable X into new individual workbooks for each variable X. Example Copy Column (B,C,D) into a new workbook and save the new workbook as "Current Sheet Title-DMA-1". The next example would copy Column (B,C,E) into a new workbook named "Current Sheet Title-DMA-2". And so on. — VBA Newby

Michael Beck Michael Beck · Accepted Answer · 2021-02-03T21:42:44

I think what you're talking about is copying data from worksheet to worksheet, correct? Workbooks refer to discreet Excel files. Workbooks contain 1 or more worksheets (those are the tabs at the bottom).

I would suggest that you just do your text formatting manually within the column of the worksheet. Highlight the columns you're after (you can click the column header to select the entire column), then right-click and choose format cells. You can format cells using a particular format by choosing the correct category. For instance, Date has a number of different options available. If none of them work for you, choose Custom and then type in the formatting that you want to use. Same deal for time.

If I understand correctly, you want to copy the two new columns into their own worksheet. If you've only got a set number of additional columns, you should just insert the corresponding sheet to the workbook manually. It can be done dynamically, but doing it manually allows you to name the sheets and is a lot less fiddly to work out which sheet is active.

Assuming you have a sheet called NewSheet and that the copy and insert commands work (I haven't tested them)...

Do your range select like you did in your example code...

CurrentSheet.Activate  'Make sure you've got the sheet in focus
CurrentSheet.Range("A:A").Copy  'Grabs your column data

And now, flip over to the other sheet.

NewSheet.Activate  'this brings the other sheet into focus
NewSheet.Range("A:A").Insert  'Inserts whatever you have in the clipboard from your previous copy

If you don't already have the sheets (tabs) created, you'll have to generate a new sheet and then use different references to get to them to do your edits. For example...

worksheets("NewSheet").range("A:A").Insert  'Where NewSheet is the name shown on the tab at the bottom of the Spreadsheet

Also, one thing to keep in mind, Excel's macro recorder is really great for recording things that you want to do. It's just a matter of knowing what you want to do, pressing the record button and then copying and pasting the code out. The macro recorder records the code it generates into a module. Then you can grab it and modify it however you need.

Copy multiple columns into separate new workbooks using VBA

1 Answers