Copy Paste sheet data

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
1,047
Office Version
  1. 365
  2. 2021
  3. 2019
I have 2 workbook, need to copy data from all sheets expect 1 sheet "Expenses - Over All" and paste rest of the sheets from workbook2 to workbook 1.
In workbook 1 it should compile the data.

I did tried very possible way but clearly no result., just need to loop the sheets and copy paste it in one file

this code will work for 1 sheet.
VBA Code:
Sub ExpenseDataCopy()
   
   Dim fPath As String
   Dim fName As String
   Dim fName1 As String
   Dim WB As Workbook
   Dim WB1 As Workbook
   
   Dim ctr As String
   
Set WB1 = ActiveWorkbook
Sheets("DownloadFiles1").Select
   
   Dim lastRow As Long
   lastRow = Cells(Rows.Count, "A").End(xlUp).Row

Dim I As Long
    For I = 2 To lastRow
fName1 = Cells(I, "J").Value
   
   Sheets("DownloadFiles").Unprotect "Qtech456$!"
   Sheets("Client Wise Revenue Tracking").Unprotect "Qtech456$!"
   Sheets("Manpower Costing").Unprotect "Qtech456$!"
   
  
        On Error Resume Next
        fPath = Application.ActiveWorkbook.Path & "\" & "Income & Expenditure Downloaded Files"
        fName = fPath & "\" & fName1 & ".xlsx"
        Set WB = Workbooks.Open(fName) 'It has multipal sheets
        
  With WB
        .Sheets("Expenses - Bizinso").Select
         Dim lastRow1 As Long
         lastRow1 = Cells(Rows.Count, "C").End(xlUp).Row
   
   Range("C3:AA" & lastRow1).SpecialCells(xlCellTypeVisible).Copy
   WB1.Activate
   Sheets("Expenses").Select
    Range("C5").Select
    lMaxRows = Cells(Rows.Count, "C").End(xlUp).Row
    Range("C" & lMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    'xlPasteFormats
    Application.CutCopyMode = False
    Range("A4").Select
  
  
  
  
  
  End With
  
  Next I

End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top