0

I have raw CSV data for each EU country, that I run a macro on to format it, make a pivot, etc. The Excel files all start with the country code, i.e.: AT report 201901, FR Report 201901, etc.

I have another Excel workbook, saved on a network drive, called VATCONTROLS, with sheets per country, so AT,FR,BE,DE etc.

I'm looking for a macro that will look at the first two digits of the activeworkbooks name, and then copy/paste the corresponding sheet from the closed VATControls workbook and add it to the active workbook.

How would I go about this? This is just part of the code. Newsheet is worksheet for another part of the code.

Dim excel As excel.Application
Dim wsGET As String
Dim wb As excel.Workbook
Dim sht As excel.Worksheet      

NewSheet.Activate
Range("A1").Activate
Range("A1") = ActiveWorkbook.Name
Range("B1").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],2)"

wsGET = ActiveSheet.Range("b1")
Set wb = Workbooks.Open("C:\Users\extosldva\documents\vatcontrols.xlsx")
Set sht = wsGET

 sht.Activate
 sht.Range("A1:A3").Copy
 sht.Range("B1:B3").PasteSpecial Paste:=xlPasteValues

For Each Sheet In ActiveWorkbook.Worksheets
If Sheet.Name = wsGET Then
Sheet.Activate
End If
Next

If Not targetSheetFound Then
Set Sheet = Sheets.Add
Sheet.Name = targetSheetName
End If

I have tried a lot of things, and combined a few sample code snippets from various sources with various results. The code is a mess and needs to be cleaned up, as it is not consistent, but I'd like this part to work, and I'm afraid I am mixing up several methods to achieve one goal.

To clarify: I want to copy or add an entire sheet from a closed workbook, where the name of the sheet matches the value in cell b1, on a new sheet in the active workbook. The part of the code after {Set sht = wb.wsGET} was just to see if that part works.

0

@Dennis the following macro add a new sheet to Z.active workbook and copy country vat sheet from vatcontrol woorkbook

Sub Macro2()

Dim excel As excel.Application
Dim wsGET As String
Dim wb As excel.Workbook
Dim sht As excel.Worksheet
Dim ActvWB As String
Dim targetSheetFound As String

ActvWB = ActiveWorkbook.Name
Sheets.Add After:=Sheets(Sheets.Count)
Range("A1").Activate
Range("A1") = ActiveWorkbook.Name
Range("B1").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],2)"

wsGET = ActiveSheet.Range("B1")

Set wb = Workbooks.Open("C:\Users\extosldva\documents\vatcontrols.xlsx")
targetSheetFound = "Not Found"
For Each Sheet In ActiveWorkbook.Worksheets
If Sheet.Name = wsGET Then
targetSheetFound = "Found"
Sheets(wsGET).Copy After:=Workbooks(ActvWB).Sheets(Workbooks(ActvWB).Sheets.Count)
wb.Close
Exit For
End If
Next

Workbooks(ActvWB).Activate
If targetSheetFound = "Not Found" Then
Sheets.Add After:=Sheets(Sheets.Count)
Sheet.Name = wsGET
End If
Worksheets(wsGET).Activate

MsgBox "done"
End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.