]Hi there. I am seeking some urgent advice from all of you VBA gurus…
*
I have a large spreadsheet written in excel but am struggling with my last macro.
*
I have done lots of formulas in this workbook but cannot get the last VBA to work properly.
*
I have tried Loop macros, Offset macros and ranges but cannot get it to copy next number down.
*
I have simplified the workbook (see below) and will change the code to suit the proper workbook.
*
Basically I need a macro, which looks down column “C” for a membership number, Copies the Membership number in C4 into Cell F4 (which then generates a specific data field) range H4:L46 Say.
*
Copy that data field into another workbook, (say Book2), renames the sheet with the membership number originally copied from (Book 1, Cell C4)
*
Then looks to see if Cell C5 has a membership number in. If it has it takes this number, Copies it then pastes it into Cell F4 again (which then updates the data field with new details)
*
Copies that data field into workbook (Book2) again into a new Sheet and renames that sheet with the copied membership number.
*
Basically I want it to run down column C and every time it has a number filled in, it copies that number and pastes it into Cell F4 (which generates my data field) then copy that data field into a new workbook (Book2) and renames the sheets respectively. And stops when there are no more new membership numbers.
*
And to finish it off…….. Say I get more members (which generates more membership numbers) or I make changes to one of the memberships already copied and run the macro again.
*
I would like it to UPDATE the original coped sheet in Book2 with the new data, rather than ignoring it because it already exists…
*
Any help would be appreciated…
*
Thanks again
*
Please see below for code
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 07/06/2011 by scott forsyth
'
Dim Rng As Range, cell As Range
Dim LR As Long
LR = Sheets("Fees").Range("C" & Rows.Count).End(xlUp).Row
Set Rng = Sheets("Fees").Range("C4:C" & LR)
For Each cell In Rng
If Not Evaluate("ISREF('" & cell & "'!C4)") Then
*
Sheets("Fees").Select
Range("C4").Select
ActiveCell.Offset(1, 0).Select
*
*** Selection.Copy
*** Range("F4").Select
*** ActiveSheet.Paste
*** Sheets("Fees").Range("H4:L46").Select
*** Application.CutCopyMode = False
*** Selection.Copy
*** Sheets("Data").Select
*** Range("B2").Select
*** Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
******* False, Transpose:=False
*** Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
******* False, Transpose:=False
*** Selection.PasteSpecial Paste:=8, Operation:=xlNone, _
******* SkipBlanks:=False, Transpose:=False
*** Range("A1").Select
*** Sheets("Data").Copy After:=Sheets(Sheets.Count)
*** ActiveSheet.Name = cell
*
End If
*
Next
*
End Sub
Bookmarks