vbanoob1234
New Member
- Joined
- Aug 8, 2016
- Messages
- 26
Hi all,
Does anyone know how to copy and paste cells dynamically from multiple worksheets to one master sheet in a workbook.
Here is my problem:
On every worksheet, there is a word called "Payable". I want to copy the info below it (for only column A and B) to another sheet.
Data sample for Account worksheets
Column A Column B
other data above that I don't need to copy and paste to master worksheet
Payable
Class A $100
Class B $50
blank row
other data above that I don't need to copy and paste to master worksheetResults that I would like to achieve on Master worksheet.
Accounts Data (col. A) Data (col. B)
Worksheet Name(1) Class A $100
Worksheet Name(1) Class B $ 50
Worksheet Name(2) Class D $500
Worksheet Name(2) Class F $550
Worksheet Name(2) Class A $600
This is what I got so far. I used the offset function, to find the word "Payable". I need help doing the copying from shift end down, and shift end left to the master sheet.
Second issue: on the master worksheet, how do I get the macro to fill in Column A with the worksheet name X numbers of times, based on how many rows I copied.
Dim master As Worksheet
Dim account As Worksheet
Dim payabletotal As Range
With account.Range("A1:A" & account.Range("A" & account.Rows.Count).End(xlUp).Row)
Set payabletotal = .Find("Payable:", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not payabletotal Is Nothing Then c.Offset(1, ) = payabletotal.Offset
Else
MsgBox "Worksheet for account " & c.Value & " doesn't exist"
End If
Next
Loop
Application.DisplayAlerts = True
End Sub
Does anyone know how to copy and paste cells dynamically from multiple worksheets to one master sheet in a workbook.
Here is my problem:
On every worksheet, there is a word called "Payable". I want to copy the info below it (for only column A and B) to another sheet.
Data sample for Account worksheets
Column A Column B
other data above that I don't need to copy and paste to master worksheet
Payable
Class A $100
Class B $50
blank row
other data above that I don't need to copy and paste to master worksheetResults that I would like to achieve on Master worksheet.
Accounts Data (col. A) Data (col. B)
Worksheet Name(1) Class A $100
Worksheet Name(1) Class B $ 50
Worksheet Name(2) Class D $500
Worksheet Name(2) Class F $550
Worksheet Name(2) Class A $600
This is what I got so far. I used the offset function, to find the word "Payable". I need help doing the copying from shift end down, and shift end left to the master sheet.
Second issue: on the master worksheet, how do I get the macro to fill in Column A with the worksheet name X numbers of times, based on how many rows I copied.
Dim master As Worksheet
Dim account As Worksheet
Dim payabletotal As Range
With account.Range("A1:A" & account.Range("A" & account.Rows.Count).End(xlUp).Row)
Set payabletotal = .Find("Payable:", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not payabletotal Is Nothing Then c.Offset(1, ) = payabletotal.Offset
Else
MsgBox "Worksheet for account " & c.Value & " doesn't exist"
End If
Next
Loop
Application.DisplayAlerts = True
End Sub
Last edited:
