Copy After works only for the first time and works again after reopening the Access D (1 Viewer)

baba · Jun 21, 2015

The below code works fine only for the first time and when I try to rerun, it is not copying the sheets from source to destination. If I close the MsAccess DB and then reopen and execute it works again. Looks like some initialization problem. Can some one help me

Code:

Dim x, i As Integer x = 1
 Wkb1.Activate 
Dim CurSheet As String
  CurSheet = Wkb1.ActiveSheet.Name 
 Do While x < RecordCount 
Sheets(CurSheet).Activate ActiveSheet.Copy after:=Sheets(Sheets.Count)  
ActiveSheet.Name = "Sheet" & x  
x = x + 1 
Sheets(CurSheet).Activate Loop

JHB · Jun 21, 2015

It can't be the whole code you show here!
Sheets is a Excel thing and basically unknown for MS-Access, so place the variable associated with Excel in front of the line.

Code:

Do While x < RecordCount  
  [B][COLOR=Red]YourVariableName.[/COLOR][/B]Sheets(CurSheet).Activate 
  [B][COLOR=Red]YourVariableName.[/COLOR][/B]ActiveSheet.Copy after:=[B][COLOR=Red]YourVariableName.[/COLOR][/B]Sheets([B][COLOR=Red]YourVariableName.[/COLOR][/B]Sheets.Count) 
  [B][COLOR=Red]YourVariableName.[/COLOR][/B]ActiveSheet.Name = "Sheet" & x   
  x = x + 1  
  [B][COLOR=Red]YourVariableName.[/COLOR][/B]Sheets(CurSheet).Activate 
Loop

baba · Jun 21, 2015

Awesome. This works fine now. Thank you so much .

JHB · Jun 22, 2015

You're welcome, good luck.

Copy After works only for the first time and works again after reopening the Access D (1 Viewer)

baba

Registered User.

JHB

Have been here a while

baba

Registered User.

JHB

Have been here a while

Similar threads

Users who are viewing this thread