VBA copy and paste multiple sheets from one workbook to another workbook

[jawaharprm]

Hi Friends,

Please help on VBA need to copy and paste multiple sheets from one workbook to another workbook
example
i have 2 Excels Workbook in same folder named as Download.xlsb& MSDATA.xlsb
Download.xlsb have 4 Sheets these sheets to copy & pasted in MSDATA.xlsb with 4 Sheets with sheets name.


Thanks
Jawahar R

 

[jawaharprm]

Hi Worked on this below code to move 1 sheet but getting error in marked red

>>> use code - tags <<<

Sub copy_paste()
Dim i As Long
Dim lcurrow As Long
Dim lrow As Long
Dim wb As Workbook
For i = 1 To 1 Step 1
Set wb = Workbooks.Open("C:\Jawahar Works\New folder\Multiple Sheets Promo MSD\PROM MDF _W.xlsb")
With wb.Sheets("MARA")
    If i = 1 Then
    lrow = 1
    Else
    lrow = 2
    End If

Do Until .Range("A" & lrow).Value = vbNullString

lcurrow = lcurrow + 1

'   >>>> errors >>>>
For n = 0 To 3 Step 1
me.range("A" & lcurrow).offset(columnoffset:=n).value= .range("A & lrow).offset(columnoffset:=n).value
'   <<<< errors <<<<
Next n
lrow = lwor + 1
Loop
End With
wb.Close.true
Next i
Set wb = Nothing

End Sub

 

[anishms]

The code goes into workbook "MSDATA.xlsb" and rename the sheet names "Annex 1A", "Annex 2A" with your sheet names in "Download.xlsb"

Sub vba_copy_sheet()
    Dim mywb   As Workbook

    Application.ScreenUpdating = False
    
    Set mywb = Workbooks.Open(ThisWorkbook.Path & Application.PathSeparator & "Download.xlsb")
    
    mywb.Sheets(Array("Annex 1A", "Annex 2A")).Copy After:=ThisWorkbook.Sheets(Worksheets.Count)
    mywb.Close SaveChanges:=False
    
    Application.ScreenUpdating = True
End Sub

 

[jawaharprm]

Hi Worked on this below code to move 1 sheet but getting error in marked red

>>> use code - tags <<<

Sub copy_paste()
Dim i As Long
Dim lcurrow As Long
Dim lrow As Long
Dim wb As Workbook
For i = 1 To 1 Step 1
Set wb = Workbooks.Open("C:\Jawahar Works\New folder\Multiple Sheets Promo MSD\PROM MDF _W.xlsb")
With wb.Sheets("MARA")
    If i = 1 Then
    lrow = 1
    Else
    lrow = 2
    End If

Do Until .Range("A" & lrow).Value = vbNullString

lcurrow = lcurrow + 1

'   >>>> errors >>>>
For n = 0 To 3 Step 1
me.range("A" & lcurrow).offset(columnoffset:=n).value= .range("A & lrow).offset(columnoffset:=n).value
'   <<<< errors <<<<
Next n
lrow = lwor + 1
Loop
End With
wb.Close.true
Next i
Set wb = Nothing

End Sub

Hi Sir,

thanks for your time i working file...