0

I am trying to write the macros but having difficulties. Every month I get a file for which I have to perform specific tasks of copying data and paste as values and to delete specific sheets. But the main part is as every month the file name will be changing so I am trying to put these macros into another blank workbook which will open the particular workbook and does the task and after completing the tasks it will save this file as a new excel file. Nomefile is a cell which contains the name of the workbook which needs to be opened.

Sub PasteSpecial_Values()

nomefile = (dati(j, 1) & "\" & dati(j, 2))
Workbooks.Open Filename:=nomefile

Worksheets("SC Global Overview").Range("A1:F80").Copy
Worksheets("SC Global Overview").Range("A1:F80").PasteSpecial Paste:=xlPasteValues

Worksheets("GL_EU").Range("A1:J100").Copy
Worksheets("GL_EU").Range("A1:J100").PasteSpecial Paste:=xlPasteValues

Worksheets("GL_APAC").Range("A1:J100").Copy
Worksheets("GL_APAC").Range("A1:J100").PasteSpecial Paste:=xlPasteValues

Worksheets("GL_SAM & NAM").Range("A1:J100").Copy
Worksheets("GL_SAM & NAM").Range("A1:J100").PasteSpecial Paste:=xlPasteValues

Worksheets("SC Europe Overview").Range("A1:F80").Copy
Worksheets("SC Europe Overview").Range("A1:F80").PasteSpecial Paste:=xlPasteValues

Worksheets("REG_EU").Range("A1:J100").Copy
Worksheets("REG_EU").Range("A1:J100").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

End Sub
Sub SheetKiller()
Dim s As Worksheet, t As String
Dim i As Long, K As Long
K = Sheets.Count

For i = K To 1 Step -1
    t = Sheets(i).Name
    If t = "Check combinations" Or t = "Measures" Or t = "GL_Target" Or t = "Parameters" Or t = "Data" Or t = "Pivot data initiative" Or t = "Pivot data substream" Or t = "Pivot data" Or t = "Pivot check names" Or t = "Pivot check region" Then
        Application.DisplayAlerts = False
            Sheets(i).Delete
        Application.DisplayAlerts = True
    End If
Next i

ThisWorkbook.SaveAs Filename:=WaveReporting, Format:=xlOpenXMLWorkbook

End Sub

Can someone please help and it will be good if we can convert it to one macro rather than 2?

Regards.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
Abid Zain Alam
  • 1
  • 1
  • What's the question? Your code looks okay (I assume `dati()` is non-English for `Cells()`, but is a valid function), do you just want to run `SheetKiller` after the copying is done? (FYI You can ski `.Copy`/`.Paste` by simply setting the ranges equal to eachother, e.g. `Range([Destination Range]).Value = Range([origin data range]).Value` so one example from above, `Worksheets("SC Global Overview").Range("A1:F80").Value = Worksheets("SC Global Overview").Range("A1:F80")..Value` But do note you never declare `WaveReporting` in the `SubKiller` sub. – BruceWayne Apr 01 '19 at 16:37
  • Is this the code you are writing to open a new workbook and copy the ranges from each worksheet into the new workbook? – GMalc Apr 01 '19 at 16:49
  • if you want to start with a blank workbook, where are you getting the `nomefile` name from? – GMalc Apr 01 '19 at 16:55
  • Are the 6 worksheets, "SC Global Overview", "GL_EU", etc. the only worksheets you want to copy from the monthly workbook to the blank workbook? – GMalc Apr 01 '19 at 16:58
  • Both the source workbook and blank workbook are in the same folders and I have given a path in a workbook in which I have written down the macros. Now when I am running it, it gives an subscript error I dont know why. Yes these are the worksheets which I want to copy to blank workbook. – Abid Zain Alam Apr 02 '19 at 06:29
  • [This answer](https://stackoverflow.com/a/19352099/8769365) should help you copying the data. And [this answer](https://stackoverflow.com/a/40344224/8769365) should help delete your workbook. – Nacorid Apr 02 '19 at 06:59
  • Possible duplicate of [Copy from one workbook and paste into another](https://stackoverflow.com/questions/19351832/copy-from-one-workbook-and-paste-into-another) – Nacorid Apr 02 '19 at 07:23

0 Answers0