Solved Macro to copy from worksheets, and then paste in diff book

February 6, 2017 at 06:28:02
Specs: Windows 7
Hi All,

I currently have a macro set up for creating a payment csv file which creates a brand new file which is then uploaded.

The code currently looks like this:
Sheets("Client Name").Select
Range("E150:A3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Supplier Import1.csv").Activate
Range("A1").Select
ActiveSheet.Paste
Windows("Payroll Import Spreadsheet.xlsm").Activate
Sheets("Client Name").Select
Range("k150:g3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Supplier Import1.csv").Activate
Range("A300").Select
ActiveSheet.Paste
Windows("Payroll Import Spreadsheet.xlsm").Activate

This is repeated for all the clients within the workbook.

This works fine in it's current state however I am creating another macro that makes it easier to add new clients when needed.

My question is, is there an easier way to write the macro so that it goes through each sheet and copies the rows as needed but doesn't require the clients name, meaning the whole code doesn't need changing when new client names are added?

Each of the clients data is pasted into the new sheet with about 200 rows between them and then the following code is on the end to delete the blank rows and bring all the data together (this is just so that each client could have enough space if the data was large for all of them):

With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Columns("A:E").SpecialCells(xlBlanks).EntireRow.Delete
Columns("A:A").Select
Selection.NumberFormat = "000000"
Columns("B:B").Select
Selection.NumberFormat = "00000000"
Range("A1").Select

So any new code would need to be along the same lines.

Any takers?

Thanks in advance.

message edited by JenksNo1


See More: Macro to copy from worksheets, and then paste in diff book

Report •

#1
February 6, 2017 at 12:37:53
✔ Best Answer
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link. Thanks!

Second, while I obviously don't have access to your workbooks to test my code with, I believe that your code be written a little more efficiently. In most cases, VBA can work directly with objects without Selecting them. I think that these 2 snippets will do the same thing as the code you posted above:

This snippet should take care of the Copy/Paste into the csv file

Sub CopyData()
'Copy/Paste A3:E150
  Workbooks("Payroll Import Spreadsheet.xlsm").Sheets("Client Name") _
             .Range("A3:E150").Copy
   Windows("Supplier Import1.csv").Activate
    Range("A1").Select
     ActiveSheet.Paste
'Copy/Paste G3:K150
  Workbooks("Payroll Import Spreadsheet.xlsm").Sheets("Client Name") _
             .Range("G3:K150").Copy
    Range("A300").Select
     ActiveSheet.Paste
'Activate Payroll workbook
   Windows("Payroll Import Spreadsheet.xlsm").Activate
End Sub

This snippet should take care of the formatting. I should note that I left the use of "Selection" in place for the interior coloring and borders only because I don't know what Range you are formatting. If you know the Range, or if the code can determine the range itself, then you won't need to use Selection,. You will note that I didn't Select columns A or B before applying the NumberFormat, I formatted them "directly".

Sub FormatCells()
   With Selection.Interior
     .PatternColorIndex = xlAutomatic
     .ThemeColor = xlThemeColorDark1
     .TintAndShade = 0
     .PatternTintAndShade = 0
   End With
   Selection.Borders.LineStyle = xlNone
     Columns("A:E").SpecialCells(xlBlanks).EntireRow.Delete
     Columns("A:A").NumberFormat = "000000"
     Columns("B:B").NumberFormat = "00000000"
   Range("A1").Select
End Sub

re: "Each of the clients data is pasted into the new sheet with about 200 rows between them and then the following code is on the end to delete the blank rows and bring all the data together (this is just so that each client could have enough space if the data was large for all of them):"

I'm a little confused by that. In your original code you Copy/Paste A3:E150 and then G3:K150, but you leave a block of empty rows between the two sets of pasted data. It looks to me like all of the data is for the same client (Client Name). However, the paragraph I quoted above says that you leave blank rows between each client which seems to indicate that the 2 sets of pasted data are from 2 different clients.

Could you please clarify that for me? The reason I ask is that I may have a way of pasting the data directly where it belongs without having to delete blank rows afterward. (VBA is capable of determining the length of a block of data to copy as well as determining the next empty row after a block of data into which the paste should occur.)

Last question:

Am I correct in assuming that "Payroll Import Spreadsheet.xlsm" contains multiple sheets, one per client, and that you want to copy ranges from all sheets, one at a time and paste them into a single .csv file? If that is true, what ranges should be copied?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question