Copy paste information from one worksheet into a new workbook

[Luis_B]

Hi guys, I would like to create a new workbook containing the information that I have in a particular worksheet. So basically, I would like to create a VBA that copies the information from Worksheet A in workbook A and creates a brand new workbook B with the information copied from worksheet A. I hope that makes sense.

Any help would be greatly appreciate it

Thank you,

 

[Joe4]

The easiest way is probably to open Workbook A and simply do a "SaveAs", which will save an exact copy of it under a different file name.
If you use the Macro Recorder, you can get most of the VBA code you need to do that.
The only thing you probably might have to edit is the file name, if you want it to be different each time.
We can help you make that part of the code more dynamic, if you need, if you lay out the rules for us.

 

[Luis_B]

The easiest way is probably to open Workbook A and simply do a "SaveAs", which will save an exact copy of it under a different file name.
If you use the Macro Recorder, you can get most of the VBA code you need to do that.
The only thing you probably might have to edit is the file name, if you want it to be different each time.
We can help you make that part of the code more dynamic, if you need, if you lay out the rules for us.

Thank you Joe.

I used the code below and it is working fine so far. I do need to delete the first column in the new Workbook. Would you be able to guide me with that?

I tried Columns("A").delete but it is deleting the column in the workbook where I have the VBA. I do not know how to refer to the new workbook so that it deletes the first column on it.

Thank you,

Dim newWB As Workbook, currentWB As Workbook
Dim newS As Worksheet, currentS As Worksheet

'Copy the data you need
Set currentWB = ThisWorkbook
Set currentS = currentWB.Sheets("Worksite 4")
currentS.Range("A1:M5000").Copy

'Create a new file that will receive the data
Set newWB = Workbooks.Add
With newWB
Set newS = newWB.Sheets("Sheet1")
newS.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'Save in CSV
Application.DisplayAlerts = False
.SaveAs Filename:="C:\DOWNLOADS\Worksite 4.csv", FileFormat:=xlCSV
Application.DisplayAlerts = True

 

[Joe4]

I tried Columns("A").delete but it is deleting the column in the workbook where I have the VBA. I do not know how to refer to the new workbook so that it deletes the first column on it.

I do not see your attempt to delete the column in your code.
Note that you already set your new workbook to the "newWB" workbook variable, and sheet to the "newS" worksheet variable.
So those are the variables you would use to refer to this sheet in your new workbook.

 

[Luis_B]

I do not see your attempt to delete the column in your code.
Note that you already set your new workbook to the "newWB" workbook variable, and sheet to the "newS" worksheet variable.
So those are the variables you would use to refer to this sheet in your new workbook.

Hi Joe,

I was looking at something like this. The actual name of the sheet in the new workbook is Worksite 1


Dim newWB As Workbook, currentWB As Workbook
Dim newS As Worksheet, currentS As Worksheet

'Copy the data you need
Set currentWB = ThisWorkbook
Set currentS = currentWB.Sheets("Worksite")
currentS.Range("A1:M5000").Copy


'Create a new file that will receive the data
Set newWB = Workbooks.Add
With newWB
Set newS = newWB.Sheets("Sheet1")
newS.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Workbooks("newWB").Worksheets("Worksite 1").Columns("A").Delete

'Save in CSV
Application.DisplayAlerts = False
.SaveAs Filename:="C:\DOWNLOADS\Worksite 1.csv", FileFormat:=xlCSV
Application.DisplayAlerts = True

 

[Joe4]

Note that you do not have a "Worksite 1" sheet in your new workbook.
Just because you copy all the data over there doesn't change the name of the sheet.
Also, within the "With" statement, since you are referring to the "newWB", you need to put a period in front of all the Sheet references (newS) to denote that you are referring to a sheet in "newWB".

Try this (untested):

Dim newWB As Workbook, currentWB As Workbook
Dim newS As Worksheet, currentS As Worksheet

'Copy the data you need
Set currentWB = ThisWorkbook
Set currentS = currentWB.Sheets("Worksite")
currentS.Range("A1:M5000").Copy

'Create a new file that will receive the data
Set newWB = Workbooks.Add
With newWB
    Set newS = newWB.Sheets("Sheet1")
    .newS.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

    .newS.Columns("A").Delete

'   Save in CSV
    Application.DisplayAlerts = False
    .SaveAs Filename:="C:\DOWNLOADS\Worksite 1.csv", FileFormat:=xlCSV
    Application.DisplayAlerts = True

End With